Categories
Excel

Use Non-Contiguous Ranges to Create Charts

A common assumption is that charting data requires a single, contiguous table. However, sometimes you may want to chart only a subset of the data in a table, and the columns you need may not be adjacent. This situation is not uncommon, but it requires some extra steps when creating your chart.

Suppose you have a table that shows the monthly sales of 10 different regional offices. However, you only want to compare the sales of two of these offices in a chart. Your chart will use the data in column A (which has the month of the sales) and the data in column C and column D (which have the total sales for the two regions you want to compare).

1) Select Non-Contiguous Range To Create a Chart

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:

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.
  1. First, use the mouse to select the data in column A.
  2. 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 the figure above.
  3. Now choose Insert tab from ribbon, and pick the appropriate chart type from the Charts group.

Excel creates the chart as usual, but uses only the data you selected in steps 1 and 2, leaving out all other columns.

2) Change/Remove Data Range Included in the Chart

The previoius 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 Charts group in the Insert tab of the ribbon, choosing a chart type.
  2. Right-click on the chart, and choose Select Data.The “Select Data Source” dialog box appears.
  3. Use the Legend Entries (Series) section to remove any data series you don’t want and add any new data series you do want.
    • Uncheck a sereies and click OK to remove the series from the chart.
    • Select a series and click Remove button to remove it.
    • To add a new series, click Add, and then specify the appropriate cell references for the series name and the series values.
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 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).

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.

Charts and Graphs