Section 18.6. Advanced Charting

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.

Figure 18-28. 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 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:

Table 18-1.











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:

Table 18-2.









Computer Saftware


Computer Hardware


Missing Manual Books


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.

Figure 18-29. This example shows a pie chart of expenses, with the Other 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.

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:

  • Series Overlap is the amount of overlap between columns in the same category. This setting takes effect only 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 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 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:

  1. Create a bar or column chart with two series .

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

    Figure 18-30. 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.

  2. 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 18-30, the projected value should be placed on top.

  3. Find this topmost series in the chart. Right-click it, and then choose Format Data Series .

    The Format Data Series dialog box launches.

  4. Select the Series Options section, and then set the Series Overlap to 100 .

    This setting ensures that two series are layered one on top of the other.

  5. Select the Fill section, and then choose "No fill" .

    This tells Excel to make this data series transparent, with only the border visible.

  6. Click Close .

    Next, you want to make sure the right series is on top.

  7. Right-click the chart, and then choose Select Data. Make sure the series that's supposed to be on top is at the bottom of the list .

    To move a series, select it, and then click the down arrow button. Excel draws the series from top to bottom in this list, so the one it draws last ends up in front.

  8. Now select the other series, right-click it, and then choose Format Data Series. Select the Border Color section, and then choose "No line" .

    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.

  9. Click Close .

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

Figure 18-31. 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 because the units tracked in each data series are different.
Bottom: But once a secondary axis is added at the right side, the numbers are a bit easier to compare. For an even better combo chart solution, see Figure 18-32.

To create a combination chart, follow these steps:

  1. Create a chart that includes both sets of data .

    Initially, the smaller set of data doesn't appear correctly because the scale is wrong. To solve this problem, you need to plot this data against another axis.

  2. Right-click the data series that doesn't fit, and then choose Format Data Series .

    If the scale difference is really extreme, you may 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 Tools Format Current Selection list.

  3. 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 series , and then choose Change Series 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 18-32 shows a good example.

    Figure 18-32. 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.

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: