Microsoft Excel

Non-Contiguous Chart Ranges

So far, all the chart examples have assumed the data you want to chart is placed in a single, tightly packed table. But what if your information is actually scattered across your worksheet? This scenario may seem unlikely, but it actually occurs quite often when you need to chart only part of the data in a table. Say you want to create a chart using two or three columns, and these columns aren't next to each other. In this case, you need to take a few extra steps when you create your chart.

Imagine you have a table that records the monthly sales of 10 different regional offices. However, you want to create a chart that compares only two of these offices. Your chart will use the category information in column A (which contains the month in which the sales were recorded), along with the values in column C and column D (which contain the total amount of sales for the two regions in which you're interested).

The easiest way to create this chart is to start by selecting the non-contiguous range that contains your data. Here's what you need to do:

  1. First, use the mouse to select the data in column A.
    Excel surrounds the data with a marquee. Don't click anywhere else yet.
  2. Then, hold down the Ctrl key while you click with the mouse again, and drag to select the data in columns C and D.
    Because you're holding down the Ctrl key, column A remains selected as in figure below.
  3. Now choose Insert Charts, and then pick the appropriate chart type.
    Excel creates the chart as usual, but uses only the data you selected in steps 1 and 2, leaving out all other columns.

This approach works most of the time. However, if you have trouble, or if the columns you want to select are spaced really far apart, then you can explicitly configure the range of cells for any chart. To do so, follow these steps:

  1. Create a chart normally, by selecting part of the data, and then, from the Insert > Chart section of the ribbon, choosing a chart type.
  2. Select the chart, and then choose Chart Tools : Design > Data > Select Data.
    The Select Data Source dialog box appears.
  3. Remove any data series you don't want and add any new data series you do want.
    To remove a series, select it in the Legend Entries (Series) list, and then click Remove.

Non-Contiguous Cell Selection
This worksheet shows a non-contiguous selection that ignores the numbers from region 1. When you create the chart, Excel includes only two series in the chart: one for region 2, and one for region 3.

Select Data Source Settings
This dialog box demonstrates a handy secret about Excel charting. Excel not only records the whole range of cells that contain the chart data (as shown in the "Chart data range" text box), it also lets you see how it breaks that data up into a category axis and one or more series (as shown in the Legend Entries (Series) list).

To add a new series, click Add, and then specify the appropriate cell references for the series name and the series values.

You can also click Switch Row/Column to change the data Excel uses as the category axis and you can adjust some more advanced settings, like the way Excel deals with blank values, and the order in which it plots series.