Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Charts in Excel (Office 2000 )

    I want to combine charts from several worksheets into one chart. I have tried to fool with the Pivot chart but wasn't able to get what I wanted. I have created some sample data to illustrate in the attached file. I want to have another sheet with a chart of all the other sheets over many years. Of course each sheet has much more data than in these samples. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charts in Excel (Office 2000 )

    If you want to combine the data series in one chart, you can't use a time scale for the x axis, since the data come from different years.
    If you use the text January, February etc. you can use Chart | Add Data... to add series to a chart. You'll have to set the series name manually.
    See attached version.

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Charts in Excel (Office 2000 )

    Thanks Hans, I guess I didn't explain myself clearly. In my actual file I have data on a weekly basis for the entire year from 2000 until now. While what you have shown might be helpful to see if there are some months that tend to have a better performance than other months, what I was trying to do was create one long line graph that shows the performance over the entire period. Ideally, it would be a steady rise from the left end toward the right. It seems that Excel wouldn't care that the dates are from different years since it is a continuous series, but just happens to be separated onto different sheets. In other words, if I was to look on the web for the Dow Jones Industrial performance over the last several years, I'm pretty sure I could find a chart like what I'm trying to create. I hope I've explained myself more clearly here. Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charts in Excel (Office 2000 )

    In that case, you should organize the data differently. Instead of a sheet for each year, put all data in one sheet, each week below the previous one. You can then create a chart based on a single range. See attached version.

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Charts in Excel (Office 2000 )

    Wow, that was fast. I created a sample by doing just what you suggested since I posted the last post just to give you an idea of what I'm trying to create. However, since I have 52 weeks of data in each sheet, I didn't want to have to copy and paste everything from all those years into a new sheet like I did in this sample. There really ought to be a way to continue a series in a chart from more than one page. As I have it now, I have a chart on each page which shows me how my funds did each year. I suppose if I combined it all onto one page, I could create several charts using the different rows as applicable and then one larger chart using all the rows. I haven't looked at your sample yet but I'm guessing that is what you'd suggest.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Charts in Excel (Office 2000 )

    With data organized horizontally, you wouldn't be able to combine more than 4 years in one sheet. That's why you need to organize them vertically.

    You could use AutoFilter to switch between charting one year or all years. See attached version. Select a year or (All Categories) in the Year column in the Combined worksheet, then look at the chart.

  7. #7
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Charts in Excel (Office 2000 )

    Thanks Hans, That is pretty cool. I don't remember seeing those combo boxes in cells before. How did you populate them? I'll play around with that some. Did you have to have named areas in order for the chart to change when the data changes depending on the selection of year vs. all dates? Thanks again, I'll work on it. I guess I will have to reorganize. I still think there should be a way for Excel to accept ranges from multiple worksheets into one series.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Charts in Excel (Office 2000 )

    Here is another example and an extension of what Hans' suggests with more data and an additional column.

    With the 2 columns you can filter on date and then on month or just the Month/year and view just the time frame you want.

    Steve

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Charts in Excel (Office 2000 )

    Those "comboboxes" are created automatically when you select Data - filter - autofilter

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Charts in Excel (Office 2000 )

    <hr>I still think there should be a way for Excel to accept ranges from multiple worksheets into one series. <hr>

    It will accept multiple non-contiguous ranges that are on the same sheet. It is more limited with multiple sheets (even using named ranges, which typically gets around this type of limitation)

    I don't see the "problem" with having it all on one sheet. To me, that is the preferable placement. I would never suggest having essentially multiple sheets that are formatted the same way. It is always better to put them all together and use the built in features

    Filtering (Auto and advanced) can be used with the data and SUBTOTALS function can get stats of the filtered data set
    Pivot tables can be used to create summaries of the data
    Charting is easier
    Charts on the whole set, become "subcharts" of the data that is viewable which means you instantly can have charts from years or month/yr combos or anything else you care to filter on.

    Steve

  11. #11
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Charts in Excel (Office 2000 )

    Thanks Steve and Hans. I consider myself fairly knowledgeable about Excel but I'm not in your league yet. I'll keep working and get there someday. You've given me lots of good ideas and stuff to learn.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •