18.6. Advanced Charting
At this point, you've seen a wide range of tips and tricks for improving almost any Excel chart. But it's worth learning a few advanced charting techniques. 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.
18.6.1. Exploding Slices in a Pie
Data labeling and formatting help make individual slices of a pie chart stand out. However, 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 18-28.
To explode only one slice of a pie chart, just click the slice, and then 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.
Tip: You can also help separate your slices by adding a thin white border around each slice. Right-click any slice, and then choose Format Data Series. Then, in the Border Color section, choose to create a solid white line. You can use the options in the Border Styles section to make the line thicker so the slices stand out more.
18.6.2. Grouping Slices in a Pie
An even more interesting pie chart feature breaks down a single slice into more detail. You may 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. Imagine you want to create a pie with these slices:
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:
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 selection doesn't include the same slices that you want. Fortunately, it's easy to change the separation just by dragging.
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 18-29 shows a perfected "Pie of Pie" chart.
18.6.3. Gaps, Widths, and Overlays in a Column Chart
Column and bar charts have their own options for fine-tuning. You can adjust how far each column group's spaced from the next and how much space appears 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 then select Format Data Series. Then, select Series Options, which is the first section in the list. You get two settings:
Note: In a 3-D column chart, you'll find an extra setting: Gap Depth. This is the space between the columns at the front and the columns that appear behind them.
One interesting trick is to use the overlap setting to compare two sets of data. People often use this technique to compare projections against actual results. Here are the steps you need to follow:
18.6.4. Creating Combination Charts
Sometimes, you want to use a chart to compare trends in different but related data. 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?
Combination charts are a well-kept Excel secret. 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. A chart that shows quarterly sales could 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.
Tip: Surf to the "Missing CD" page at www.missingmanuals.com to see a screencastan animated online tutorialthat demonstrates the process of creating a combination chart.
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 18-31 shows how this process works.
To create a combination chart, follow these steps: