9.3. Practical ChartingFigure 9-1 showed you how to chart a list that contains two columns you want to graphone with text labels and one with 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 9-1. 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 NumbersA series is the sequence of numbers that you plot on a graph. In the simple chart example (Figure 9-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. 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. It doesn't take any extra expertise to create a chart that uses multiple seriesyou just select the right range of cells and pick a chart option from the ribbon, just as you would for a chart that has a single series. Different types of charts handle multiple series in different ways. The clustered column chart creates a separate bar for each value in a row, as shown in Figure 9-8. 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 in Section 9.4.
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-AxisExcel'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. Unfortunately, this decision may not be what you want. Fortunately, you can change it. But what causes the situation in the first place? Excel creates your charts according to the way the data's organized in your worksheet. A simple example shows you the effect. The worksheet in Figure 9-9 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 9-9 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.
The column chart example is fairly innocent. Although you may prefer one way of looking at the data over the other, they're relatively similar. However, most Excel charts aren't as forgiving . The line chart's a classic example. 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-10), 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-10 illustrates the problem.
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, as in Figure 9-10. Fortunately, you have the power to override Excel's choice if you need to. Just select your chart, and then choose Chart Tools Design Data Switch Row/Column.
9.3.3. Non-Contiguous Chart RangesSo 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:
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:
9.3.4. Changing the Order of Your Data SeriesIf your table has more than one series, Excel charts it in the order it appears on your worksheet (from left to right if your series are arranged in columns, or from top to bottom if they're arranged in rows). In a basic line chart, it doesn't matter which series Excel charts firstthe end result is still the same. But in some charts, it does make a difference. One example is a stacked chart (Figure 9-14), in which Excel plots each new series on top of the previous one. Another example is a 3-D chart, where Excel plots each data series behind the previous one. You can easily change your data series' order. Select your chart, and then choose Chart Tools Design Data Select Data. Now select one of the series in the Legend Entries (Series) list, and then click the up or down arrow buttons to move it. Excel plots the series from top to bottom. 9.3.5. Changing the Way Excel Plots Blank ValuesWhen Excel creates a chart, its standard operating procedure is to ignore all empty cells. The value of 0 doesn't count as an empty cell and neither does text (Excel plots any cells that contains text as a 0). So what's the difference between an ignored cell and a cell that contains the number 0? In some types of charts, there's no difference. In a bar or pie chart, the result is the sameyou don't see a bar or a pie slice for that data. However, in some charts, there is a difference. In a line chart a 0 value is plotted on the chart, but an empty cell causes a break in the line. In other words, the line stops just before the missing data, and then starts at the next point. This broken line indicates missing information. If you don't like this behavior (perhaps because your empty cells really do represent 0 values), you can change it. Select your chart, and then choose Chart Tools Design Data Select Data to get to the Select Data Source dialog box. Then, click the Hidden and Empty Cells button, which pops open a dialog box with three choices:
You can also switch on or off the "Show data in hidden rows and columns" setting to tell Excel whether it should include cells that are hidden when creating a chart. This setting determines how Excel deals with data when you use filtering in a table, or when you explicitly hide rows or columns using the Home Cells Format Hide & Unhide menu. Ordinarily, Excel treats these missing values just like blank values, and ignores them. |