Advanced Charts
You learned, 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 above figure. 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 this section, you'll get a quick introduction to all these possibilities, using just the simple column chart and line chart.
You'll learn how to customize your charts to the maximum so that they look exactly as you want. You also pick up some slick charting tricks that will make your charts even more impressive.
Chart Customization
Excel 2007 makes creating a basic chart easier than ever. Select your data, choose a chart type, and you’re finished. You may take a few extra seconds and select one of the prebuilt Chart Layouts, and maybe even select one of the Chart Styles. But if your goal is to create the most effective chart possible, you probably want to take advantage of the additional customization techniques available in Excel.
Customizing a chart involves changing its appearance, as well as possibly adding new elements to it. These changes can be purely cosmetic (such as changing colors modifying line widths, or adding a shadow) or quite substantial (such as changing the axis scales or adding a second Value Axis). Chart elements that you might add include such features as a data table, a trend line, or error bars.
Charts with Multiple Series of Numbers
A series is the sequence of numbers that you plot on a graph. In the simple chart example, 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. A line chart, on the other hand, shows a separate line for each series.
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 you've already added to your worksheet.
Controlling the Data Excel Plots on the X-Axis
Excel's charting tool has a 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.
This chart has three series of sales figures (one for each year) and five sets of columns (one for each region). Each of the five sets has 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.
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 below 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 below 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.
This worksheet shows the same data charted in two different ways. In the right table, the category axis lists the sales years, which are used to group the regions. In the left table, the category axis lists the regions, which are used to group the years.
The column chart example is fairly simple. 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 in figure below, 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, 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 below 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 below.
The chart on the right is pretty straightforward. The chart on the left 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.
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. If you try this action on the charts in figure above, you reverse the results. Thus, the chart on the right would group the data into yearly series, and the chart on the left would group the data into regional series. To return them to normal, you can select each chart, and then click Switch Row/Column again.
Data in Different Scales
When you add multiple series, each series should use the same scale. In other words, the points for each series should be plotted (placed on the chart) using the same measurement system.
The worksheet in above figures works perfectly well because the different series of sales figures all use the same unitdollars. But if one series recorded sales totals in dollars and another recorded them in Euros (or even worse, recorded 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 of noticing that anything's amiss. And if you don't notice either, you'll create a misleading chart.
Your chart may imply a comparison that isn't accurate or, if the scale is radically different, the chart can get so stretched that it starts to lose detail. If you have sales figures from $50,000 to $100,000 and units sold from 1 to 100, the scale stretches from 1 to 100,000, and the differences in sales totals or units sold are too small to show up at all.
What's the solution? Don't mix different scales. Ideally, convert values to the same scale (in this case, 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's a way. in Section 18.6.4, "Creating Combination Charts" shows you how to build combination charts that fuse together two incompatible sets of data in a logical way.
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's 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 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.
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 chart a bunch of years that are far from sequential (like Sales02, Sales04, and Sales08) 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 below.
The top chart uses category axis scaling to properly space out dates, even when there are missing values. The bottom chart doesn't.
What's happening in fFigure above is worth examining in a bit of detail. The pictured worksheet shows two charts that illustrate the exact same data: a series of monthly sales figures from two regions (covering the time period between January 2007 and December 2008). The diamonds 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 2007 and June 2008) is missing. To make sure Excel handles this omission correctly, you must 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, no values 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 2007 and June 2008 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 above was created. (Why you'd want to do it is another question, but someone, somewhere, is probably in desperate need of this feature.) To change how Excel scales the category axis, select the chart, and then choose Chart Tools : Layout > Axes > Axes > Primary Horizontal Axis More Primary Horizontal Axis Options to show the Format Axis dialog box. Next, change the Axis Type setting, as shown in figure below.
Under the Axis Type heading, pick one of the following: "Text axis" (treat the category values as labels), "Date axis" (treat the category values as date vales), or "Automatically select based on the data" (let Excel decide based on what it thinks is best).
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. 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.
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:
- First, use the mouse to select the data in column A.
Excel surrounds the data with a marquee. Don't click anywhere else yet. - 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. - 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:
- Create a chart normally, by selecting part of the data, and then, from the Insert > Chart section of the ribbon, choosing a chart type.
- Select the chart, and then choose Chart Tools : Design > Data > Select Data.
The Select Data Source dialog box appears. - 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.
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.
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.
Changing the Order of Data Series
If a 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 first the end result is still the same. But in some charts, it does make a difference. One example is a stacked chart, 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.
Changing the Way Excel Plots Blank Values
When 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:
Gaps
Excel leaves a gap where the information should be. In a line chart, this breaks the line (making it segmented). This option is the standard choice.
Zero
Excel treats all blank cells as though they contain the number 0.
Span with line
Excel treats all blank cells as missing information and tries to guess what should go in between. If a line chart goes from 10 to 20 with a blank cell in between, Excel interpolates the data point 15 and plots it.
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.