A more detailed description of a chart and its series formula is presented in the The Chart Series Formula page elsewhere on this site, but a brief description here is in order.

Every chart series has a formula which describes the data in the series. For example, a simple series formula looks like this:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

This formula can be broken up into four elements as follows:

=SERIES([Series Name],[X Values],[Y Values],[Plot Order])

The Series Name can be blank, a text string in double quotation marks, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The X Values can be blank, a literal array of numeric values or text labels enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Y Values can be a literal array of numeric values enclosed in curly braces, a reference to a worksheet range (one or more cells), or a reference to a named range (named formula). The Plot Order can only be a whole number between 1 and the number of series in the chart.

Each of the three elements that can be linked to worksheet values (Series Name, X Values, and Y Values) can only come from a single sheet. However, these items are not constrained to come from the same sheet. Each can come from a different sheet, even a different workbook.

Selecting Allowed Data from Different Sheets

There are a couple of ways to use data from different sheets in a chart's source data. The easiest way is to right click on the chart, select Source Data from the popup menu, and click on the Series tab. Select a series from the list or add a new series, then click in the appropriate box, and select the range of the sheet that contains the data. From this dialog you can use the sheet tabs at the bottom to change sheets, and the Windows menu to change workbooks.

You can also select the series, and highlight the part of the series formula you want to change. Using the Windows menu and the sheet tabs, browse to the workbook and sheet with the data you want, and select the range with the mouse.

Summarizing Data from Different Sheets

Suppose you still need to plot data that has Y Values on three different sheets, for example monthly statistics for three different years, where each year is on a different sheet. You have two options:

1. Use a different series for each sheet's data, or
2. Create a summary sheet, with links to the three data sheets, and chart the data on the summary sheet.

The first of these is straightforward. Add a series as described above, then select its data range from the appropriate sheet. Or copy the data, select the chart, and use Paste Special from the Edit menu to add the copied data as a new series. Format the different series to have the same formats (same markers, colors, etc.) so they appear to be from the same data source.

To generate a summary sheet, copy each of the ranges from the individual sources, and use Paste Special from the Edit menu, with the Paste Links option, to add the data to a summary sheet. Using the Paste Links option links the pasted data, so it updates with the original cells.

If the data is arranged the same on all the different sheets, you can use formulas on the summary sheet. For example, to summarize five worksheets, put the worksheet names in cells A2:A6, and put the cell addresses for the X and Y values into cells B1 and C1. In cell B2, put the following formula, and fill it right to C2 and down to B6:C6.

=INDIRECT($A2&"!"&B$1)

Note: if the sheet names have spaces, you need to enclose them in single quotes, so the formula should be

=INDIRECT("'"&$A2&"'!"&B$1) 

The table will look like this when filled in:

Construct your chart using B2:B6 of the summary sheet for your X values and C2:C6 for your Y values.