Charting Data From Different Sheets

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 at least a couple of ways to use data from different sheets in a chart's source data. One involves the Source Data dialog, the other uses the Series Formula.

Source Data Dialog
If you are creating a new chart, the Source Data dialog comprises Step 2 of the Chart Wizard. For an existing chart, you can access the dialog by selecting Source Data from the Chart menu or the chart's context menu (right click menu). When the Source Data dialog appears, click on the Series tab. Select a series from the list or add a new series, then click in the appropriate box, and highlight its contents. With the cursor in one of these boxes you can use the sheet tabs at the bottom to change sheets, and the Windows menu to change workbooks. Browse to the workbook and worksheet you need, and select the range of cells that contains the data.

Chart Series Formula
You can also select the series, and highlight the part of the series formula you want to change. You can edit the text of the formula in the formula bar, or use the Windows menu and the sheet tabs to browse to the workbook and worksheet containing 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.

Select Data from a Different Sheet using a Forms Toolbar Control

ChartAgainstStandard.zip contains a zipped Excel workbook showing how to use a listbox or combobox to select which sheet contains data for a charted series. The listbox or combobox are used to select the sheet, and the INDIRECT worksheet function uses it to build cell references to the selected sheet.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile