how can I create an excel chart using data from more than one worksheet?

Question

I am trying to create a chart using data from 3 work sheets, do I need to combine the data into one sheet or is there a way I can create my chart using chart wizard?!

Answer 1

The easiest way I have found to assign data from multiple worksheets is to create the chart using the chart wizard, initially assigning arbitrary (and wrong) ranges to the various chart series'. Once the chart is created you can assign the series' to the correct ranges on the various worksheets by right-clicking the chart's plot area, select Source Data, select the Series tab. You can then select the series you want to correct in the Series listbox and in the Name, X Values, and Y Values RefEdit boxes you can type or go to the desired sheet and select the correct range reference for each. If you type in a range that is on another sheet rather than selecting it you must enter it with the sheet name qualifier, such as:

='Amortization Data'!$C$1:$C$14

The apostrophes are only required if the sheet name contains a space character.

Keep Excelling ;-)

Answer 2

As long as the worksheets are in the same workbook you can create a chart from it. When you go into chart wizard you select the cells you want to create the chart from. simply go to each sheet and select the cells you need. (use shift to slect multiple cells in different locations

Answer 3

First, open up the three worksheets. Second, create the chart as you normally would. When asked for the source range, select the other source worksheet and so forth and so on.

Answer 4

MS, in its infinite wisdom of what users REALLY need and want, decided to eliminate the wizard. I think you can use a "collector" worksheet to pull the data from the various sheets into one so the Pivot table will work.

Answer 5

Pete was almost right but you use "control" to select non-contiguous cells.