Section 17.3. Advanced Charting | Excel 2010: The Missing Manual

17.3. Advanced Charting

By this point, you've seen a wide range of tips and tricks for improving almost any Excel chart. But there are still a few advanced charting techniques that are well worth the trouble of learning. Most of these work only with specific chart types, like exploding pie slices and overlaying bar charts . You'll also learn about one of the most interesting chart tricks known to Excel-kind: using a combination chart to fuse together two different chart types into one object.

17.3.1. Exploding Slices in a Pie

Data labeling and formatting help make individual slices of a pie chart stand out. However, in order to really accentuate important information in a pie or donut chart, you can separate a piece from the rest of the pie by exploding the slice (to use the rather dramatic technical term ). You've already seen this feature at work with the pie or donut chart subtypes that explode all the pieces. This technique shows you how to explode just a single slice, as shown in Figure 17-17.

Figure 17-17. This example shows a pie with one exploded piece, representing the sales in the Eastern division.

To explode only one slice of a pie chart, just click the slice, and drag it away from the pie. You can pull it as far away as you want, and you can repeat this process to explode several pieces (although the emphasis works best when you remove just a single slice). Be careful that you don't select the whole data series before you start dragging, or you'll end up exploding the whole pie.

17.3.2. Grouping Slices in a Pie

An even more interesting pie chart feature is the ability to break down a single slice into more detail. For example, you might want to create a pie chart that shows your personal budget by category (including food, living expenses, clothes, and so on). You could then further subdivide a single slice, like food, to show what portion goes to groceries, restaurants , the local hot dog vendor, and ice cream splurges. Adding all this information into a single pie would result in too many slices, making the chart less effective.

Creating a pie chart with a single slice breakdown is pretty easy, but it's not the most intuitive operation in the world. First, you need to set up your data correctly. Your table needs to include all the information you're putting into the chart in a single series. For example, imagine you want to create a pie with these slices:

Food	$13,911
House	$18,342
Clothes	$4,790
Fun	$7,980
Computer	$34,010

Now, assume that the slice you want to subdivide is the Computer slice. That means you need to remove the Computer entry, and replace this information in the chart with more detailed information. Here's what you might end up with:

Food	$13,911
House	$18,342
Clothes	$4,790
Fun	$7,980
Computer Software	$7,500
Computer Hardware	$6,500
Missing Manual Books	$20,010

Notice that the Computer Software, Computer Hardware, and Missing Manual Books entries add up to the whole computer slice ($34,010), which has been removed from the chart.

Note: The only limitation to breaking down an individual slice is that you can perform this trick on only one slice.

Now that you've organized your data in the right way, create a new pie chart, and choose either the Pie of Pie or Bar of Pie subtypes. Excel automatically chooses some slices from the bottom of your chart to group in a separate pie or bar chart. Unless Excel makes a lucky guess, this won't include the same slices that you want. Fortunately, it's easy to change the separation just by dragging.

For example, if you have a Bar of Pie chart, you can add a slice to the bar by dragging it from the main pie to the column. If you want to take a slice that's in the column and put it back in the pie, drag it from the column back to the main pie. Continue this process until you've grouped the Computer Software, Computer Hardware, and Missing Manual Books slices into the standalone bar.

Figure 17-18 shows a perfected Pie of Pie chart.

Figure 17-18. This example shows a pie chart of expenses, with the Computer slice broken down further into a second pie chart. Excel pie charts often need a fair bit of tweaking before they look respectable. Usually, you'll want to remove the legend and use category labels, as shown here. If you drag the label a short distance away from the midpoint of the slice, Excel adds a line pointing from the label to the slice. These lines often help to make a pie chart more readable.

17.3.3. Gaps, Widths, and Overlays in a Column Chart

Column and bar charts have their own options for fine-tuning. Using these, you can adjust how far each column group is spaced from the next and how much space there is between each column in a group. With a little imagination , you can use these settings to create an innovative overlay chart , which layers two different sets of data on top of each other.

To see these extended column chart settings, right-click a data series in the chart and select Format Data Series. Then, select Options, which is the last tab in the Format Data Series dialog box. There are two settings:

Overlap . This is the amount of overlap between columns in the same category. This setting only takes effect if you have more than one series. The standard option is 0, which means that clustered columns touch each other but don't overlap. With a value of 100, the columns in the same category overlap completely, while negative values put a space between the columns .
Gap width . The gap width is the space between each category. In a chart with one series, this setting is the space between each column; in a chart with multiple series, it's the space between each group of columns. The standard value is 150, which leaves a space that's roughly equal to the width of 1.5 columns.

Note: In a 3-D column chart, you'll find a different group of settings. These include Gap depth (the space between the front of the chart and the columns), Gap width (the space between columns), and Chart depth (the total depth of the chart). Columns grow deeper automatically as you deepen the chart. Often, a deeper chart creates a more dramatic 3-D effect.

One interesting trick is to use the overlap setting to compare two sets of data. This technique is commonly used when you need to compare projections against actual results. Here are the steps you need to follow:

Create a bar or column chart with two series.

Figure 17-19 shows one possible example of a bar chart that compares projected sales against actual sales.

Figure 17-19. This example shows an overlay bar chart. The projected sales values have dotted borders and a transparent fill. The actual sales numbers show a gradient fill. Using this model, you can clearly see that the Southern and Northern locations exceeded their expectations, while the Western region fell far short of its predictions , even though it had the second-best overall sales.

Decide which series should be on top of the other.

The series that's on top needs to be transparent, so the other series can show through. In Figure 17-19, the projected value should be placed on top.
Select this topmost series in the chart. Right-click it, and choose Format Data Series.

The Format Data Series dialog box launches.
Select the Options tab, and set the Overlap to 100.

This setting ensures that two series are layered one on top of the other.
Select the Series Order tab, and use the Move Down button to make sure that the series that's supposed to be on top is at the bottom of the list.

Excel draws the series from top to bottom in this list, so the one it draws last ends up in front. Select the "front" series name in the list and click Move Down.
Select the Patterns tab. Choose None for Area, and then click OK.

This tells Excel to make this data series transparent, with only the border visible.
Now select the other series, right-click it, and then choose Format Data Series. Select the Patterns tab, and select None for Border.

You can also choose any fill color or fill effect. You may need to try several combinations in order to have enough contrast for the overlay chart to really work.
Click OK.

17.3.4. Creating Combination Charts

Sometimes, you want to use a chart to compare trends in different, but related data. For example, imagine you create two charts, one to show how many hours you've worked in the last few months, and the other to show how much money you've spent. After you create these two charts, you start to wonder if there's a linkin other words, do you spend more money when you have a greater workload? Unfortunately, because these two measurements use different units (one records the number of hours, while the other counts the number of dollars), you can't put them on the same chartor can you?

One well-kept secret in Excel is combination charts . Using combination charts, you can compare trends across different sets of data, even if the units are wildly different. Combination charts are also useful when you need to compare more than one piece of information to tell the complete story. For example, a chart that shows quarterly sales might make it look like your company is meeting wild success. But using a combination chart, you can contrast the sales against another factor (like changing currency exchange rates, or increased business expenses brought about by an irrational 80 percent-off coupon campaign) to tell a different tale.

You make a combination chart by creating a new secondary value axis. This axis appears on the right side of the chart. In other words, you'll end up with two value axes, one that applies to the first series of data, and the other that applies to the second series. Figure 17-20 shows how this works.

To create a combination chart, follow these steps:

Create a chart that includes both sets of data.

Initially, the smaller set of data won't appear correctly because the scale is wrong. To solve this problem, you need to plot this data against another axis.
Right-click the data series that doesn't fit, and choose Format Data Series.

If the scale difference is really extreme, you might have trouble selecting the data series you want to change because it may be squashed against the bottom chart axis. In this case, select the series by name from the Chart toolbar.
Select the Axis tab, choose "Secondary axis," and then click OK.

Excel creates a new scale on the right side of your chart and uses this scale to plot the data series. Excel automatically chooses the best scale, although you're free to change it by formatting the axis.

Combination charts don't just let you compare different units of data. They also help you fuse together two different types of charts. For example, you could plot one series using a scatter chart, and add columns to represent the values in the second series. When done right, this combo produces an attractive chart with a series of columns and a line above them, which you'll see commonly in shareholder reports and marketing documents.

Figure 17-20. These charts both compare the number of customer inquiries with the amount of sales made in each month. Top : Both lines use the same scale, which actually doesn't make much sense since the units tracked in each data series are different.
Bottom : But once a secondary axis is added at the right side, the numbers are easy to compare.

Tip: Some chart types just don't mix. To mix properly, the arrangement of axes should be the same in both chart types. Thus, a combination of a column chart and line chart works great, but a combined line chart and pie chart doesn't make much sense.

To create a combination chart that uses more than one chart type, right-click the chart and choose Chart Type. You can now choose a new chart type and subtype, as usual. The difference is that this change is applied only to the series that you've selected. Figure 17-21 shows a good example. .

Figure 17-21. This example compares the inquiry and sales numbers using two different chart types. The inquiry numbers are depicted as columns, and the sales figures use a line chart. This technique is a great way to highlight the fact that you're comparing different types of data.