Highlighted Chart Source Data

Excel highlights the data range of a chart under many conditions. The source data range must be relatively simple, based on a rectangular data range. The chart must be embedded in the worksheet that also contains the data. If the data meets these conditions (elaborated upon later), selecting the chart area or the plot area of the chart will cause Excel to highlight the source data.

Highlighting is used in Excel versions 97 (or earlier) through 2007; Excel 2003 was used for the illustrations in this post.

The Y value data is highlighted by the blue outline, the X value data by the purple outline, and the series names by the green outline. If the data range is defined in such a way that no series names are derived from the worksheet, there is no green highlighted range. If the X values are not drawn from the worksheet, there is no purple highlighted range. This data range corresponds to the Source Data – Data Range dialog below. Note that the rectangular range in the dialog includes four rectangular ranges in the worksheet: Y values in C3:G9, X values in B3:B9, series names in C2:G2, and the blank cell B2.

If a single series is selected, the corresponding data ranges for that series are highlighted (even if the source data for the entire chart was not highlighted).

This corresponds to the Source Data – Series dialog below.

In the following chart, the source data has been switched from series in columns to series in rows. The blue highlighted region below is the same as in the first figure above, but the purple and green regions have been switched.

The small squares (“handles”) on the corners of the highlighted ranges allow the user to adjust the sizes of the ranges by clicking on a corner and dragging. The ranges can be moved without resizing by clicking on the edge of a highlight rectangle and dragging. If the highlights show the source data range for the entire chart, moving and resizing one range will automatically move and resize the other ranges to maintain the “integrity” of the overall source data range.

In the following illustration, the data range has been adjusted so that columns C and G and rows 3 and 9 are no longer included. (If the source data has been defined such that rows or columns subdivide the Y data range, the data range is considered “too complex”, and the highlights vanish.)

The corresponding dialog is shown below.

The entire data range address isn’t visible in the screen shot, but I’ve reproduced it here:

=Sheet1!$B$2,Sheet1!$B$4:$B$8,Sheet1!$D$2:$F$2,Sheet1!$D$4:$F$8

Note that the range includes the top left blank cell (cell B2) as a separate range, along with the X and Y ranges and the series names.

Experimenting with the highlighting behavior leads us to develop requirements that must be met for highlighting to occur.

Requirements for highlighting of whole chart source data

• Data range must be based on a rectangular range.
• Chart must be embedded in same worksheet that contains all data.
• X values and series names are optional.
• If X values are included, all series must use the same X value range.
• If series names are included, all series names must come from the appropriate cell in the range.
• If series names are not included, no series name may come from a worksheet cell.
• Related ranges must have identical numbers of rows or columns (as applicable).
• Related ranges must be aligned (start and end in same rows or columns).
• If X values are included, they must be parallel to Y values (i.e., same row or column orientation).
• Overall range may be divided by rows or columns between X and Y values or between Y values and series names.
• Y values range may not be subdivided by rows or columns.
• Series must be plotted in same order as data in data range.
• If any range does not meet requirements, all highlighting is lost.

Requirements for highlighting of chart series source data

• Data ranges must each be a contiguous rectangular range.
• Only data in same worksheet as embedded chart will be highlighted.
• Related ranges need not have identical numbers of rows or columns (as applicable).
• Related ranges need not be aligned (start and end in same rows or columns).
• If X values are included, they need not be parallel to Y values.
• If one range does not meet requirements, others may still be highlighted.
• If the data range for one series does not meet requirements, data ranges for another series may still be highlighted.
• If the chart’s entire data range does not meet requirements, data ranges for an individual series may still be highlighted.

The following screen shot illustrates the greater flexibility allowed for series data range highlighting, compared to whole chart highlighting.