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.
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.
|
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.
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.
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.
|
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.
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.
|
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.
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.
|
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. .
|