Leading the way in Microsoft Office Development

Step 1 - How to display the Source Data ?

There are four ways you can display the source data for a chart.

1) Using the (Chart > Source Data) menu.

2) Using the Chart Shortcut Menu and selecting Source Data.

3) Using the Automatic Range Finder you can quickly see which range of cells an embedded chart is referring to.

4) Seleting an individual series and editing the Series Formula.

Step 2 - Using the (Chart > Source Data) menu

When you activate a chart the Data specific drop-down menu is automatically replaced with a Chart drop-down menu.

You can display the source data for the active chart by activating the chart and selecting (Chart > Source Data).

This will display the Source Data dialog box.

Step 3 - Using the Chart Shortcut Menu

The easiest way to adjust the source data of a chart is to right mouse click and select “Source Data” and click on the series tab.

You can select either the chart area or the plot area to display this shortcut menu.

Step 4 - Using the Automatic Range Finder

When you activate a chart the source data will be automatically highlighted.

This method can only be used with embedded charts when the chart is on th same worksheet as the data.

When a chart is selected its source data is also highlighted. In the same way an individual series’ data is highlighted when an individual series is selected.

If you select the chart, instead of a series all the series are outlined in one colour and the series labels in another.

If you select a series, the series is in one colour and the series category labels are in a different colour.

When you edit a series formula, the cells and ranges that the formula refers to is outlined in one colour and the series category labels in a different colour.

Selecting an individual data series will highlight only the source data for that specific series.

The range of cells consists of one or more coloured ranges.

Purple - Series labels (X-axis) or categories

Blue - Values (Y-axis)

Green - Bubble Sizes / Series name, Legend details

Each outline has a handle (i.e. a small square in the bottom right corner) and the source data can be increased or decreased by dragging this handle.

Each of the highlighted ranges contains a small handle in each corner which can be easily dragged to expand or reduce the range used for the source data.

The source data can even be moved by selecting the edge and dragging the outline.

It is possible to extend the range of cells within your source data and see the chart updated immediately including the new data.

Changing values are reflected immediately.

Note that the data for an individual data series must be continuous. The colour cannot be changed.

You can alternatively just drag the source range indicators and fill handles.

A way to update your charts (i.e. re-link / assign the source data) without to press F9 which updates the whole worksheet

You can extend the ranges by placing your mouse at the corner of the data range. You should see the pointer turn into a small plus sign, press down and drag the mouse until the coloured border extends to include the new cells. Release the mouse and the chart will update.

Step 5 - Using the Series Formula

This is discussed in detail on the next page.

When a series is selected the series formula is displayed in the formula bar at the top. The name of the series is displayed in the “Name box” to the left of the formula bar.

Step 6 - Things to Remember

The Automatic Range Finder can only be used with embedded charts when the chart is on th same worksheet as the data.

If you delete the source data for a chart and press F9 your source data will contain #REF’s and will have to be re-defined.

Source Data in hidden rows is not shown by default but you can change the setting (Tools > Options)(Chart tab, “Plot Visible cells only”).