Section 16.3. Practical Charting | Excel 2010: The Missing Manual

16.3. Practical Charting

Figure 16-1 showed you how to chart a list that contains two columns you want to graphone with text labels, and one with the numeric data. But in real life, you'll probably need to deal with many different types of data that occupy many different configurations on your worksheet.

Consider all the possible variations on the simple sales chart shown in Figure 16-1. For example, you might need to compare the sales figures but, rather than showing region-to-region comparisons, you want to show how well (or poorly) each of your firm's products sold. Or perhaps you want to chart the quarterly performance of different stores over a five-year period, or determine the relationship between sales and profitability. All these charts require a slightly different arrangement of data. In the next section, you'll get a quick introduction to all these possibilities, using just the simple column chart and line chart.

16.3.1. Charts with Multiple Series of Numbers

A series is the sequence of numbers that you plot on a graph. In the simple chart example (Figure 16-1), there's one series of numbers, which represents the sales figures for a company's different regions . Of course, a real chart usually adds extra layers of detail. For example, you might be interested in comparing the sales figures from several different years . In this case, you'd add a separate column to your worksheet data for each year. Then you'd add each column to your chart as a separate series.

It doesn't take any extra expertise to create a chart that uses multiple seriesyou just select your table of data, fire up the Chart Wizard, and follow the same steps as you would for a chart that has a single series. Different types of charts handle multiple series in different ways. For example, the clustered column chart creates a separate bar for each value in a row, as shown in Figure 16-6. A line chart, on the other hand, shows a separate line for each series (as demonstrated in the next section). For more possibilities, take a look at the Section 16.4 later in this chapter.

Figure 16-6. This chart has three series of sales figures, one for each year. The column chart has five sets of columns, one set for each region. In each set, there are three bars, one for each data series. The regions are labeled on the category axis, but you'll need to consult the legend to determine which year each column represents.

Tip: You can add multiple series to an existing chart without starting over from scratch. First, select the chart so that the linked data becomes highlighted. Then, click the rightmost edge, and drag it to the right to expand the range so that it includes the new columns (which, of course, you've already added to your worksheet).

16.3.2. Controlling the Data Excel Plots on the X-Axis

Excel's charting tool has a dirty little secret. You might not realize it right away, but sooner or later, whether it's your first chart or your fortieth, you'll stumble onto the fact that Excel makes a fairly important decision for you about what data shows up in your chart's X-axis. This decision may not be what you wantbut you can change it.

The issue stems from the fact that Excel creates your charts according to how the data is organized in your worksheet. The best way to understand what kind of difference this scheme can lead to is to look at a simple example.

Up To Speed Data in Different Scales

When you add multiple series, you run the risk of one potential problem. Each series must use the same scale . For example, the worksheet in Figure 16-6 works perfectly well because the different series of sales figures all use the same unitdollars. But if one series records sales totals in dollars and another records them in Euros (or even worse , records totally different data like the number of units sold), the chart would be inconsistent.

Excel doesn't complain if your series use different scalesin fact, it has no way to notice that anything is amiss. The problem is that the chart you end up creating will be misleading. For example, the chart may imply a comparison that isn't accurate or, if the scale is radically different, the chart gets so stretched that it starts to lose detail. For example, if you have sales figures from $50,000 to $100,000 and units sold from 1 to 100, the scale will stretch from 1 to 100,000, and the differences in sales totals or units sold will be too small to show up at all.

What's the solution? Don't mix different scales. Ideally, convert values to the same scale (for example, use the currency exchange rate to turn Euros into U.S. dollars before you create the chart). Or, just create two charts, one for each data series. But if you really want to compare the changes in different types of data across the same categories, there is a way. On Section 17.3.4, you'll see how you can build combination charts that fuse together two incompatible sets of data in a logical way.

The worksheet in Figure 16-6 looks at sales based on two factors: the year when the sales were recorded, and the region where the sales were made. In technical charting terms, the regions form the category axis , while the sales figures form the value axis . In other words, Excel creates a separate series for each year. But it makes just as much sense to organize the table in a different way, by making the year the category axis and creating a separate series for each region! Figure 16-7 contrasts these two different ways of looking at the same data, and shows how they affect the way Excel groups your data in a column chart.

Figure 16-7. This worksheet shows the same data charted in two different ways. In the first table (left), the category axis lists the sales years, which are used to group the regions. In the second table (right), the category axis lists the regions, which are used to group the years.

The column chart example is fairly innocent. Although you might prefer one way of looking at the data over the other, they're relatively similar. However, most Excel charts aren't as forgiving . A classic example is the line chart.

In a line chart, each line represents a different series. If you list the sales years on the category axis (as shown on the left side of Figure 16-8), you'll end up with a separate line for each region that shows how the region has performed over time. But if you invert the table (shown on the right side), you'll end up with a chart that doesn't make much sense at all: a series of lines that connect different regions in each year. Figure 16-8 illustrates the problem.

Figure 16-8. The chart on the left is pretty straightforward. The chart on the right shows a line for each year, which makes sense if you concentrate on what's being depicted, but mainly illustrates the way people can use computers to complicate things.

Up To Speed The Difference Between a Column and a Line

With simple column charts, life is easy. It doesn't matter too much what data you choose to use for your category axis because your choice simply changes the way data is grouped. Other chart types that follow the same principle include pie charts (which only allow one series), bar charts (like column charts, but oriented horizontally instead of vertically), and donut charts (where each series is a separate ring).

The same isn't true for line charts, and most other types of Excel charts. The category axis you use for a line chart is important because the values in each series are connected (in this case, with a line). This line suggests some sort of "movement" or transition as values move from one category to another. That means it makes sense to use a line to connect different dates in a region (showing how sales have changed over time), but it probably doesn't make sense to use a line to connect different regions for each date. Technically, this latter scenario (shown on the right side of Figure 16-8) should show how yearly sales vary as you move from region to region, but it's just too counterintuitive for anyone to interpret it properly.

As a general rule of thumb, use time or date values for the category axis. You should do this especially for chart types like line and area, which usually show how things change over time.

Clearly, when you create a line chart, you need to make sure the chart ends up using the data in a way that makes the most sense. So how does Excel decide how to plot the data? Essentially, Excel makes a best guess about your data. If you have more rows than columns, Excel assumes that the first column represents the category axis. If you have more columns than rows (or if you have the same number of rows and columns), Excel assumes that the first row represents the category axis.

Fortunately, you have the power to override Excel's choice if you need to. Just follow these steps:

Right-click your chart and choose Source Data.

The Source Data dialog box appears, which has the same information as step 2 of the Chart Wizard.
In the Data Range tab, change the setting in the "Series in" section. For example, if you're currently using Rows, change it to Columns, or vice versa.

If you choose Rows, Excel assumes each row is a separate series, and the top row (with the column headings) is the category axis. For example, consider the charts in Figure 16-8. In both cases, Excel assumes that each row is a separate series. In the chart on the left, each region is a separate series. In the chart on the right, each year period is a separate series.

If you choose Columns, Excel assumes each column is a separate series, and the leftmost column (with the row headings) is the category axis. For example, if you were to use this approach with both tables in Figure 16-8, you'd reverse the results. Thus, the chart on the left would group the data into yearly series, and the chart on the right would group the data into regional series.

Tip: You don't have to change the orientation of a chart after you've created it. If you're sharp enough to anticipate the problem before it occurs, you can make the adjustment in step 2 of the Chart Wizard. Excel shows a preview of the chart to help you assess whether you have the data oriented the right way.

16.3.3. Data that Uses a Date or Time Scale

As the previous example demonstrates , using time or date values for the category axis makes a lot of sense for charting progress or spotting long- term trends. However, the example does cheat a little. Even though any sentient human knows that the labels Sales-03, Sales-04, and Sales-05 represent consecutive years, Excel is oblivious to what these labels actually mean. You could, for example, chart a bunch of years that were far from sequential (Sales-92, Sales99, and Sales02, for example) and Excel would obediently (and misleadingly) place each value on the category axis, spaced out evenly.

This snafu doesn't present a problem in the previous example, but it's an issue if you need to chart years that aren't spread out evenly. Fortunately, Excel offers an easy solution. Instead of entering text labels, you can enter actual dates or times. Because Excel stores dates and times as numbers, it can scale the chart accordingly (this process is sometimes called category access scaling ). Best of all, Excel automatically notices when you're using real dates, and kicks into action, making the appropriate adjustments, as shown in Figure 16-9.

Figure 16-9. Category access scaling in action.

What's happening in Figure 16-9 is worth examining in a bit of detail. The worksheet that is pictured shows two charts that illustrate the exact same data: a series of monthly sales figures from two regions (covering the time period between January 2004 and December 2005). The squares and triangles on the line charts indicate the data points for which sales data is available. The twist is that a big chunk of data (the months between August 2004 and June 2005) is missing. To make sure this omission is handled correctly, enter real date values (rather than text labels) for the category axis. If you take that step, the chart Excel creates will automatically use a continuous timescale , as shown in the top chart. (As you can see by looking at the data points, there are no values that fall in the middle of the series.)

On the other hand, if you enter the labels as text (as was done when creating the bottom chart), you'll see an incorrect result: the data from August 2004 and June 2005 are placed close togethereven though they record months that are almost a year apart.

Optionally, you have the ability to tell Excel to disregard any values you've used in your column or row labels, thereby spacing the dates out evenly, as though they're ordinary text labels. That's how the incorrect chart in Figure 16-9 was created. (Why you'd want to do it is another question, but someone, somewhere, is probably in desperate need of this feature.) Figure 16-10 shows you how to make this change.

Figure 16-10. To change how Excel scales the category axis, right-click the chart and select Chart Options. Select the Axes tab, and under the "Category (X) axis" checkbox, pick from one of the following choices: Category (treat the category values as labels), Time-scale (treat the category values as numbers), or Automatic (let Excel decide based on what it thinks is best). Make sure you don't turn off the "Category (X) Axis" checkbox or the "Value (Y) axis" checkboxif you do, the axis labels disappear from the chart, making it difficult to interpret your data.

Category axis scaling works with more than just dates. You can scale any category axis values, as long as they're numeric, which is particularly useful if you're trying to determine the relationship between two different values. For example, if you wanted to determine the relationship between students' IQs and their test scores, you could use the numeric IQ for the category axis, and use the test scores for the value axis.

If you want to create a chart like this that compares two sets of numbers, you must use a scatter chart instead of a line chart. Scatter charts look similar to line charts and are covered in more detail later in this chapter.

16.3.4. 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 might seem uncommon, but it actually occurs quite often when you need to chart only part of the data in a table. For example, you might want to create a chart using two or three columns, and these columns might not necessarily be next to each other. If they aren't, you'll need to take a few extra steps when you create your chart.

For example, 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. This technique is described in detail in Chapter 3 (Section 3.1.2). Here's what you need to do:

First, select the data in column A with the mouse.

Excel surrounds the data with a marquee. Don't click anywhere else yet.
Then, hold down the Ctrl key while you move over and select the data in columns C and D.

Because you're holding down the Ctrl key, column A remains selected (see Figure 16-11).
Now choose Insert Chart and follow the steps in the Chart Wizard.

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

Figure 16-11. This worksheet shows a non-contiguous selection that ignores the numbers from region 1. When you choose Insert Chart, the Chart Wizard includes only two series in the chart: one for region 2, and one for region 3.

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, you can explicitly configure the range while you're creating the chart. To try this out, just follow these steps:

Select the entire chart, and choose Insert Chart Wizard.

In step 1 of the Chart Wizard, choose your chart type, and click Next. The next step is the important part.
In step 2 of the wizard, make sure that the "Data range" includes all the data you want, and verify that the "Series in" option is set correctly so Excel knows which axis is the category axis.

For more information on setting the "Series in" option correctly, see the earlier Section 16.3.2 on Section 16.3.2.

Now click the Series tab, which lists all the separate series that you're adding to your chart (Figure 16-12).

You can click each series in the list to see the corresponding name (the label of the value from the category axis), and the values (the rest of the column or row data). More important, you can select a range and click Remove so that it isn't included in the chart.

Figure 16-12. This dialog box demonstrates a little-known secret of Excel charting. Excel not only records the whole range that contains the chart data, it also lets you see how it breaks that data up into a category axis and one or more series. You can use this dialog box to remove a series you don't want to plot (click its name in the Series list and click Remove), or add a new series (click Add and specify the appropriate cell references for the Name and Values).

Once you've removed the ranges that you don't want, click Next to go on to the next Chart Wizard step, or click Finish to insert the chart immediately.

Tip: You can also use this technique to add or remove a series from an existing chart. Just right-click the chart, select Source Data, and continue with step 2.