Excel Chart Source Data

Of course, charting is all about displaying data. Excel provides a multitude of ways to get the data you want to plot into a chart. You can select it prior to starting the Chart Wizard, or in Step 2 of the Wizard. Or you can get to the source data through the Source Data menu command.

Manipulate the Source Data

You can open the Source Data dialog by selecting the chart or a chart series, and using the Source Data menu command from the Chart menu:

or by right clicking on the chart or a series and choosing Source Data from the context menu that pops up:

Data Range tab

The Data Range tab gives you a preview of the chart style you selected in Step 1, indicating where the chart's source data range is, and allowing you to plot by row or by column. The first dialog box below shows a column chart, plotted by rows. The second shows an area chart plotted by columns.

If you click in the Data range box and then click on the sheet, or click on the Range selection button , the dialog box shrinks to the size of just the data range box, obscuring less of the worksheet, so you are able to adjust this range with your mouse.

When the Source Data dialog box is activated, the source data range is highlighted by a "marching ants" border, shown in unanimated form below.

You can select a discontiguous range here. Select one area, then hold Ctrl while selecting additional areas. The range selection box reflects this multiple area range as two range addresses, separated by a comma.

The marching ants border highlights the multiple ranges you've selected. Notice the areas line up and have the same number of rows. If you had accidentally selected =Sheet1!$C$9:$C$20,Sheet1!$E$8:$G$20, Excel would accept this range, but it would mess up your chart.

Series tab

Clicking on the Series tab gives you the option to add or remove some of the data series in the selected source data range. You can change the Name, Values (Y axis), and Category labels (X axis) of each series, by editing the contents of the respective box, or by clicking on the Range selection button , then dragging with the mouse.

When you click in one of the range selection boxes (Name, X Values, or Y Values), the range is highlighted by the marching ants border.

To enter values directly without selecting a worksheet range, type them directly into the range selection boxes. In the illustration below, the ranges for our sample series have been replaced by numerical or text equivalents.

You are not required to enter any quotes or equals signs. Excel fills these in as needed.

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