9.3. Practical Charting
Figure 9-1 shows 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 probably need to deal with many different types of data that occupy many different configurations on your worksheet.
Think for a minute about all the possible variations on the simple sales chart shown in Figure 9-1. For example, you may 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.
9.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 9-1), there's just 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 may want to compare 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.
You don't need any special expertise to create a chart that uses multiple seriesjust 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 automatically 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 9-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 "Chart Types" section on Section 9.4.
Figure 9-6. This clustered column chart shows five sets of columns, one set for each location (Central, Eastern, Western, and so on). In each set, there are three bars, one for each data series. (In the worksheet, you see that each data series represents a sales year.) Excel labeled the regions on the category axis, but you 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).
9.3.2. Controlling the Data Excel Plots on the X-Axis
Excel's charting tool has a dirty little secret. You may 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 you organized the data in your worksheet. The best way to understand what kind of difference this scheme can lead to is to look at a simple example.
The worksheet in Figure 9-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 across-the-bottom category axis, while the sales figures form the up-and-down value axis. In other words, Excel creates a separate series for each year. But you could just as well organize the table in a different way, by making the year the category axis and creating a separate series for each region. Figure 9-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 9-7. This worksheet shows the same data charted in two different ways. In the table on the left, the category axis lists the sales years and the value axis (the up-and-down data bars) lists the regions. In the table on the right, the order is switched: the category axis lists the regions, and the value axis lists the sales years. Notice the difference in bar heights between the two charts.
The column chart example shown in Figure 9-7 is fairly innocent. Although you may prefer one way of looking at the data over the other, they're relatively similar. But 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 9-8), you 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 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 9-8 illustrates the problem.
Figure 9-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.
Clearly, when you create a line chart, you need to make sure the chart ends up displaying the data in a way you and other folks can understand. So how does Excel decide how to plot the data? Essentially, Excel makes its 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:
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.
9.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. But 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-axis 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 9-9.
Figure 9-9. Category-axis scaling in action.
What's happening in Figure 9-9 is worth examining in a bit of detail. The worksheet pictured shows two charts that illustrate the same data: a series of monthly sales figures from three 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, you need to enter real date values (rather than text labels) for the category axis. If you take that step, the chart Excel creates automatically uses a continuous timescale, as shown in the top chart. (As you can see by looking at the data points in the top chart, there are no data points in the middle of the series, which is as it should be.)
On the other hand, if you enter the labels as text (as was done when creating the bottom chart), you see an incorrect and misleading result: the data from August 2004 and June 2005 are placed close togethereven though they record months that are almost a year apart. A quick glance at this bottom chart, and you may not even notice that there's missing data (which could potentially lead to some pretty bad business decisions).
Note: Category-axis scaling works with more than just dates. You can scale any category-axis values, as long as they're numeric.
9.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 may seem uncommon, but it actually occurs quite often when you need to chart only part of the data in a table. For example, you may want to create a chart using two or three columns, and these columns may not necessarily be next to each other. If they aren't, you 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: