4. Creating Charts That Show Differences

Comparing Entities

Whereas Chapter 3, “Creating Charts That Show Trends,” was concerned with the progression of a trend over time, this chapter focuses on demonstrating the differences between entities. For example, you can use Excel charts to compare each sales region versus the others or your company versus the competitors. In addition, when you want to compare the differences among a handful of entities, a bar chart is the perfect choice because the reader can clearly see the differences between the entities.

The only time you should use a pie chart is when you want to show how components add up to a whole entity. Pie charts are vastly overused, and many guidelines contraindicate their use. You should often instead consider 100 percent stacked column charts, bar of pie charts, or pie of pie charts. These last charts are a bit tricky to master but have some amazing flexibility.

Finally, in this chapter you will learn how to use some more Excel trickery to build a waterfall chart. This type of chart is perfect for telling the story of how a whole entity breaks down into components.

Using Bar Charts to Illustrate Item Comparisons

Bar charts are perfect for comparing items. Bar charts offer a few advantages over column charts for comparing sales of various items:

• People tend to associate column charts—or any other chart in which the data progresses from left to right—as having a time-based component. When you turn the columns on their sides and make them horizontal bars, people tend not to read time into the equation.

• With a bar chart, the category names can appear in a horizontal orientation, giving plenty of room for longer names. For example, in the chart shown in Figure 4.1, the category names take almost half the chart. However, there is room to get the point across that the “Excel for” series is not selling as well as the general-purpose Excel books.

Figure 4.1 A bar chart allows for lengthy category names and a comparison of different product lines.

Bar charts are oriented with the first item in the list closest to the bottom of the chart. If you expect people to read a chart from top to bottom, you should sort the categories into descending alphabetical sequence by clicking the ZA button in the Data tab. The bar chart in Figure 4.1 compares sales of six different product lines. It is easy to spot the winners in the chart because the original data set is sorted to have the VBA title at the top of the spreadsheet.

Figure 4.1 uses a clustered bar chart type, although it has only one series to report. Other alternatives are the clustered bar in 3-D, clustered horizontal cylinder, clustered horizontal cone, or clustered horizontal pyramid. As mentioned in Chapter 3, cone and pyramid charts are bad because they misrepresent data; the same advice applies here.

→ For proof, see the “Lying with Shrinking Charts” section in Chapter 14, “Knowing When Someone Is Lying to You with a Chart.”

For a more powerful arrangement of data, you can sort these categories by sales in ascending sequence. Excel then plots the largest category at the top of the chart. Even more than the chart in Figure 4.1, you can see that the chart in Figure 4.2 depicts a clearer delineation between the winners and losers than the chart in Figure 4.1.

If you think it is silly to sort your data into the reverse order from which you want to present it in the chart, there is a setting in the Format Axis dialog that you can use to correct this logic. Select Layout, Axes, Primary Vertical Axis, Show Right to Left Axis to show the first row in your data set at the top of the chart. Alternatively, you can right-click the category labels, select Format Axis, and select the Categories in Reverse Order check box (see Figure 4.3).

Figure 4.2 Sort the data by ascending sales in order to show the largest bars at the top of the chart.

Figure 4.3 Instead of sorting your data in reverse order, you can sort the data in normal order and then specify reverse order for the categories.

Adding a Second Series to Show a Time Comparison

In Figure 4.4, two series are plotted on the bar chart. The original series presents sales data from this year. This data was moved to Series 2 to continue to be plotted as the top bar. The new Series 1 contains sales data from last year, for reference.

Figure 4.4 In this chart, the sales for this year are moved to Series 2. A new Series 1 shows sales from last year; a bit of additional data to show whether sales are increasing or decreasing.

Because the main message is current-year sales, those bars are formatted with a black fill to draw attention to them. The bars for last year are shown with white outlines.

The addition of data from the previous year adds a bit of context to the chart. The reader can tell that Products 2, 3, and 5 are new this year and have no sales history from the previous year. Product 4 was the previous market leader, but its sales have fallen off sharply. Product 1 has experienced good year-to-year growth; it will be interesting to see if the newly emerging products show similar growth in Year 2.

To create the chart in Figure 4.4, follow these steps:

1. Enter product names in Column A.
2. Enter last-year sales in Column B and this-year sales in Column C.
3. Sort the data so that the largest sales from this year are at the bottom of the chart.
4. From the Insert tab, select Bar, 2-D Bar, Clustered Bar.
5. Select Layout, Gridlines, Primary Vertical Gridlines, None.
6. Select Layout, Axes, Primary Horizontal Axis, None.
7. Select Layout, Legend, Show Legend at Top.
8. Select Layout, Chart Title, Above Chart.
9. Click in the title area. Type the first line of the title, press Enter, and type the second line of the title.
10. Click the outline of the title to exit Text Edit mode. On the Home tab, click the Left Align button and the Decrease Font Size button. Drag the title to the left.
11. Click a bar from this year. On the Format tab, select Shape Fill and then select black.
12. Right-click the black bar and select Format Data Series. Change the Series Overlap setting to 25 percent.
13. Click one of the bars for last year. On the Format tab, select Shape Fill, White. On the Format tab, select Shape Outline and then select black.
14. Grab one of the resizing handles in the corner of the chart and drag outward to resize the chart.

Subdividing a Bar to Emphasize One Component

Excel offers a stacked bar chart type that allows you to break a bar such as total sales to show components. In the top chart in Figure 4.5, only one component is called out. The subject of the chart is this one component of product cost, showing how Company A has a significant cost advantage.

You should use this chart type sparingly. The reader of the chart is able to make a judgment about the size of the black bar for the major subcomponent. The reader is also able to make a judgment about the total size of the bar. However, it is difficult to make a judgment about the size of the white component of the bars. Can you tell which company has the lowest other cost? (The answer is Company B.)

The problem becomes worse if you try to show more than two components in the stacked bar chart. For example, the bottom chart in Figure 4.5 is trying to compare the costs of four major components. However, the reader will not be able to learn anything about the additional components.

Figure 4.5 A stacked bar chart allows the reader to judge the size of the total bar and the first component bar. Beyond that, it is difficult to make a comparison from one bar to the next.

Showing Component Comparisons

A component comparison chart is useful when you want to show several parts that add up to a whole. You can use this type of chart to show these concepts:

• The market share of several competitors in a market

• The cost breakdown of a product by subcomponent

• The breakdown of time spent in a day

• The relative size of five major customers and all other customers as a group

These are also all great concepts to show using a pie chart or a 100 percent stacked bar/column chart. Unfortunately, pie charts are overused in business today. People try to use pie charts to compare items that do not add up to a whole. For example, in Figure 4.6, someone tried to use a pie chart to compare product prices. Without reading the individual prices labeling each pie slice, it is difficult to figure out whether the Hummer H3 or the Ford Escape hybrid costs more. As discussed in the previous section, using a bar chart is best when you are trying to compare items. The bottom chart in Figure 4.6 shows the same data as the pie chart. However, in this case, it is plotted on a bar chart, which makes it easier to understand.

Figure 4.6 An attempt to use a pie chart to compare prices is misguided. The bar chart in the lower half of the figure is more effective.

Some of the frustrations with pie charts are humorously summarized by Dick DeBartolo, Mad’s Maddest Writer, in the chart in Figure 4.7 that he prepared for this book.

When you are performing component comparisons, the following chart types are effective:

• A pie chart is appropriate when you are comparing 2 to 5 components.

Figure 4.7 Pay close attention to this chart. Most people’s eyes will glaze over by the second slice.

• A pie of pie chart is appropriate for comparing 6 to 10 components.

• A bar of pie chart can handle 6 to 15 components.

• You should use a 100 percent stacked column chart when you want to have two or more pies such as to compare market share from this year to last year. The 100 percent stacked bar, 100 percent stacked line, and 100 percent stacked area are all variations of the 100 percent stacked column chart.

• A doughnut chart is a strange chart that can occasionally be used to compare two pie charts. However, a 100 percent stacked column chart is usually better for this type of comparison.

Figure 4.8 illustrates when to use which type of chart.

Figure 4.8 Choose the proper type of pie chart based on the number of categories in your data.

The following sections describe the ins and outs of using pie, 100 percent stacked column, and the other previously mentioned charts.

Using Pie Charts

Pie charts are great for comparing two to five different components. You typically select a range that contains category labels in Column A and values in Column B. Often, the categories are sorted so that the largest value is at the top and the remaining categories are sorted in descending order.

To create a pie chart, from the Insert tab, select the Pie drop-down, as shown in Figure 4.9. The following six icons are located in this drop-down:

Figure 4.9 You commonly use the first icon to create a 2-D pie chart.

• 2-D Pie—The pie chart type used most frequently.

• 2-D Exploded Pie—There is no need to choose this type of pie chart since you can explode a pie or a slice of pie later. This technique is discussed later in this chapter in the “Highlighting One Slice of a Pie by Exploding” section.

• Pie of Pie or Bar of Pie—Both of these chart types are effective for dealing with data sets that have too many slices and when you care about the small pie slices.

• 3-D Pie—This is a regular pie chart tipped on its side so you can see the “edge” of the pie. This is a cool effect when you are trying to decorate a PowerPoint chart. However, it is not as effective if you want someone to read and understand the data.

• Exploded Pie in 3-D—This is another option that you probably do not want to choose since you can explode a pie using techniques discussed later in this chapter.

The default pie chart has no labels and includes a legend on the right side to help identify the pie slices. Initially, the first data item appears starting at the 12 o’clock position, and additional wedges appear in a clockwise direction, as shown in Figure 4.10.

In black and white, it is particularly difficult to match the tiny color swatches used in the legend with the pie slices. For this reason, you nearly always want to delete the legend and add data labels, as discussed in the next section.

Excel offers seven built-in layouts for a pie chart. All the layouts that include labels add them inside the pie slices. If you would add a blank font to the pie wedges in Figure 4.10, the font will not be legible on the dark pie slices.

Figure 4.10 The default pie chart uses a legend that is too small and too far from the chart to be effective.

Labeling a Pie Chart

Because a pie chart does not have a lengthy axis running along one edge of the chart, choosing the data labels is an important consideration. Instead of using the built-in Data Label choices on the Layout tab, you can select More Data Label Options from the Data Labels drop-down on the Layout tab. Excel displays the Label Options page of the Format Data Labels dialog, as shown in Figure 4.11.

Figure 4.11 Use the More Data Labels option in the Layout tab to build effective pie chart labels.

You have options for what the label should contain including the following options:

• Series Name—This option does not make sense in a pie chart since every slice has a series name, such as Sales.

• Category Name—Choose this option to show the names of items represented by the individual pie slices. When you turn this item on, you can set Legend to None.

• Value—Choose this option to show the actual numeric value for this slice from the cells in the spreadsheet. Either the Value or Percentage numeric values are used most often.

• Percentage—Choose this option to have Excel calculate the percentage of the pie allocated to each pie slice. This is a number that is not typically in your spreadsheet.

• Show Leader Lines—It is recommended that you leave this setting on. If you later reposition a label, Excel draws a line from the label to the pie slice associated with that label.

• Label Position—The usual choices for the label position on a pie chart are to either show the label outside the pie slice, indicated by Outside End, or inside the pie slice, indicated by Center.

The final choice in the Label Options page of the Format Data Labels dialog is the Separator drop-down. This option becomes important when you have chosen two items in the Label Contains section. For example, if you have selected Category Name and Percentage, Excel’s default choice for a comma as the separator shows the label East, 33 percent. To remove the comma from the pie chart labels, choose either (space) or (New Line) from the Separator drop-down.

Figure 4.12 shows a pie chart with category names and percentages at the outside end of each slice. The data label is separated by a new line character.

Figure 4.12 Displaying the labels at the outside end ensures that the labels can be read.

Rotating a Pie Chart

There is an ongoing argument about the rotation of a pie chart. Many people say that the first pie slice should start at the 12 o’clock position, and subsequent pie slices should appear in a clockwise direction. I disagree with this philosophy, from a purely practical point of view. I think that the smallest pie slices should be rotated around so that they appear in the lower-right corner of the pie. This location provides the most room for the labels of the small pie slices to appear without overlapping.

You have control over the rotation of a pie chart. You can right-click a pie chart and select Format Data Series. On the Options dialog that appears, you can change the setting for Angle of First Slice. For example, in Figure 4.13, the bottom pie chart has been rotated 195 degrees, which prevents labels from appearing on top of each other.

Figure 4.13 You can change the angle of first slice to rotate the pie until the data labels have the most room to appear without overlapping each other.

Moving an Individual Pie Slice Label

In a pie chart that has long category names and many slices, you may not be able to find an angle of rotation that enables all the labels to have sufficient room. In this case, you need to move an individual pie slice label.

The first time you click a data label, all the data labels are selected. At this point, you can use icons on the Home tab to change the font or font size.

Clicking a second time on a data label selects only that particular label. You can click the border of the label and drag it to a new position. If the Leader Lines option remains selected (refer to Figure 4.11), Excel automatically connects the label and the pie slice with a leader line. In Figure 4.14, the label for the keyboard has been moved so that it does not crash into the label for the trigger assembly.

Highlighting One Slice of a Pie by Exploding

The first time you click a pie selects the data series, which means it selects all the pie slices. If you then drag outward from the center of the pie, you explode all the slices of the pie.

Figure 4.14 After a second single-click, only one label is selected. You can drag an individual data label into a new position.

The charts in Figure 4.15 show various levels of explosion. The top-left chart uses a 15 percent explosion factor. Excel allows you to specify up to a 400 percent explosion factor, which really looks pretty silly.

Figure 4.15 Drag outward while a data series is selected to select the entire pie.

A better technique is to explode just the one slice that is the subject of the pie. For example, in Figure 4.16, a large order cannot ship because of shortages of a tiny assembly. The plant managers had previously decided to cut safety stock on this component. To illustrate that this might have been a poor decision, you can explode just that slice of the pie.

Figure 4.16 You can explode one piece of the pie to call attention to that piece.

To explode one slice of the pie, click once on the pie to select the entire series. Then click the slice in question one more time to select only that slice. Drag the individual slice outward to explode only that slice, as shown in Figure 4.16.

Highlighting One Slice of a Pie with Color

Instead of exploding a slice of a pie, you can highlight that slice using a contrasting color. A single black pie slice in an otherwise white pie instantly draws the reader’s eye, as shown in Figure 4.17.

Figure 4.17 You can plot one slice of the pie in a contrasting color to draw attention to that slice.

To format the chart as shown in Figure 4.17, follow these steps:

1. Click anywhere on the pie to select the entire pie.
2. On the Format tab, select Shape Fill, White. The entire pie disappears.
3. On the Format tab, select Shape Outline and then select black. The pie is now a white pie outlined in black.
4. Click the one pie slice that you want to highlight.
5. On the Format tab, select Shape Fill and then select black. The single pie slice now stands out from the other slices.

Switching to a 100 Percent Stacked Column Chart

It is difficult for a reader to track the trends from one pie to the next when you are trying to show a trend by using multiple pie charts, as shown in Figure 4.18.

Figure 4.18 It is hard to track trends by looking at multiple pies.

Instead of using pie charts, you can switch to one of Excel’s 100 percent charts. For example, in the 100 percent stacked column chart, Excel stacks the values from Series 1, Series 2, Series 3, and so on but scales the column so that each column is exactly the same height. This gives the effect of dividing a column into components, just as in a pie chart.

Excel offers 100 percent versions of column charts, bar charts, area charts, and line charts. To find them, in the Insert Chart dialog, look for charts where both the left and right elements are the same height (see Figure 4.19).

Figure 4.19 These icons all create 100 percent stacked charts.

Figure 4.20 shows examples of 100 percent stacked column, area, bar, and line charts. The 100 percent stacked column chart is probably the easiest to interpret. In a 100 percent stacked chart, the reader is able to judge the growth or decline of both the first series and the last series.

Figure 4.20 For year-over-year comparisons, a 100 percent stacked column chart is easier to read than multiple pie charts.

Using a Doughnut Chart to Compare Two Pies

Excel offers another chart type—the doughnut chart—that attempts to compare multiple pies. In a doughnut chart, one pie chart encircles another pie chart.

A reader of the chart in Figure 4.21 can see that the market share for OurCo increased between 2012 and 2013 and that the market share for Comp B decreased between 2012 and 2013. However, it is unlikely that the reader can draw any conclusions about Comp A based on this chart.

In creating the chart in Figure 4.21, you need to go through a number of maddening steps, including the steps listed here:

• Select Layout, Data Labels, More Data Label Options, and then choose to show only the series name as the label. You probably need to leave the legend turned on with this chart because there is not space to legibly fit OurCo 2012 in the thin ring of the doughnut.

• Changing the colors using the Format tab is a tedious process. You cannot format both OurCo sections simultaneously. Instead, click the 2006 ring, and then click 2012 OurCo. Select Format, Shape Fill, and then select black. Repeat these steps for the other five pieces of the doughnut chart.

• To make some data labels white, select the labels for Series 1, then select just one label, and then use the Font Color drop-down on the Home tab. Repeat this step for OurCo for Series 2.

Figure 4.21 Doughnut charts are generally difficult to read, although this one does effectively communicate some information.

One interesting setting for the doughnut chart is the doughnut hole size. When you right-click the inner series of the doughnut chart and select Format Data Series, the Format dialog box appears. You can use this dialog to change the doughnut hole size—valid values range from 10 percent to 90 percent. The doughnut chart in Figure 4.22 uses a hole size of 10 percent. Reducing the hole size makes the chart more readable in this case.

Figure 4.22 Reduce the doughnut hole size to allow more room for labels on the chart.

Dealing with Data Representation Problems in a Pie Chart

The 80/20 rule comes into play with pie charts. In many component comparisons, 20 percent of the categories make up 80 percent of the pie. In this case, the tiny pie slices at the end of the pie contain too much detail and are not useful. If you attempt to leave these slices in the pie, the labels needlessly complicate the chart, as shown in Figure 4.23.

As noted in the “Using Pie Charts” section earlier in this chapter, there are several methods that enable you to create the chart shown in Figure 4.23. For example, you can rotate a pie chart and move individual pie labels. However, the process is tedious with this amount of data.

Figure 4.23 The 20 pie slices make this chart difficult to read.

In addition, the data used in Figure 4.23 may not be entirely realistic. Usually, a company has a few major accounts and dozens of minor accounts. If you need a list of all your accounts, you should really show it in a table that lists the customers, sorted in descending sequence.

Usually a pie chart is focused on the top four to five accounts. The message for the reader of the chart in Figure 4.23 is that two major accounts make up 75 percent of the revenue stream. If anything happens to either of those accounts, this company will be in for tough times. To communicate this message, you can replace the last 16 pie slices with a single slice labeled 16 Other Accounts.

Other times you may need to show the detail of the small accounts such as to grow some new mega-customers. In this case, you can switch to a bar of pie or pie of pie chart. These options are discussed in the next sections.

Replacing Smaller Slices with an Other Customer Summary

Replacing smaller slices with an Other Customer summary does not require magic. Instead, you look through your customer list and identify a logical breakpoint between major customers and customers that should be listed as “other.”

The example shown in Figure 4.24 includes two customers that are not exactly major to help communicate how quickly sales fall off.

Figure 4.24 The low-tech solution is to add a formula to the worksheet to total all the smaller accounts.

To create this chart, insert a few blank rows to separate the major accounts from the other accounts. In the worksheet, add the label 16 Smaller Accounts. Then enter a SUM function to total all the smaller customers. Finally, change the chart range to include only the major customers and the Other line.

In Figure 4.24, the chart is less busy than in Figure 4.23. The title has been improved, and each data label now includes both the category name and percentage.

Using a Pie of Pie Chart

A pie of pie chart shows the smallest pie slices in a new, secondary pie chart. The true volume of the small slices is shown in a slice marked Other in the original pie. Series lines extend from the Other slice to the secondary pie.

This chart type can be used when the focus of your chart is the small slices such as when the focus is on the emerging markets (see Figure 4.25). Together, the five markets in this chart account for a 19 percent share. However, the markets are not doing equally well. The pie of pie chart shows which of the new markets are growing the fastest.

Figure 4.25 A secondary pie shows the detail of the emerging markets.

→ Creating the chart in Figure 4.25 requires some tricky steps that are described later in this chapter, in the “Customizing the Split in the ’Of Pie’ Charts” section.

Several interesting settings are available in the “of pie” charts. In both the pie of pie and bar of pie charts, you can control the size of the secondary plot compared to the primary pie. You can control the gap between the plots and choose whether the series lines extend from one chart to the next. To access these settings, click inside the chart but outside the pie to deselect the pie. Then right-click the pie and select Format Data Series, and then the Format Data Series dialog appears, as shown in Figure 4.26.

To see a demo of creating pie-of-pie charts, search for MrExcel Charts 4 at YouTube.

In the Format Data Series dialog, you can set the gap between the pies from 0 percent to 500 percent. The gap size is expressed as a percentage of the radius of the main pie. In other words, when you select 100 percent, the gap between the pies is equal to the radius of the main pie, which is 50 percent of the width of the main pie.

You can also set the secondary plot size to be anywhere from 5 percent to 200 percent of the main pie chart. The default setting is 75 percent.

Adjusting either the gap setting or the secondary plot size changes the size of both pies. If you make either the gap or the secondary plot size larger, Excel has to make the main pie smaller in order to fit all three elements into the same plot area. This requires a little bit of high school algebra. For example, if the plot area is 500 pixels wide and the width of the main pie is a variable n, you might encounter these settings:

Figure 4.26 The pie of pie and bar of pie charts offer new settings in the Series Options tab of the Format Data Series dialog.

• With a gap of 100 percent and a secondary plot size of 75 percent, the original pie is n pixels wide, the gap is 0.5n pixels wide, and the secondary pie is 0.75n pixels wide. This means the total width is 2.25n=500, so n is 222. This results in a main pie width of 222 pixels, a gap of 111 pixels, and a secondary plot size of 166.50.

• If you increase the secondary plot size to 100 percent and increase the gap to 150 percent, the original pie is n pixels wide, the gap is 0.75n pixels wide, and the secondary pie is n pixels wide. This means the total width is 2.75n=500. The main pie is 181 wide, the gap is 136 wide, and the secondary pie is 181 pixels wide.

Basically, as you increase the size of either setting, the main pie gets smaller. Table 4.1 shows the horizontal size of the main pie, the gap, and the secondary pie for various combinations of gap width and secondary plot size. For example, the main pie can occupy as much as 83 percent of the plot area width if you select a 0 percent gap and a secondary plot size of 20 percent. As you increase the gap and secondary plot size, you might end up with a main pie that occupies as little as 18 percent of the width of the plot area.

Here is an example of how to read the table. The first cell in the table indicates 83%/0%/17%. This means that if you choose a secondary plot size of 20% and a gap size of 0%, then the main pie occupies 83% of the width, the gap is 0%, and the secondary plot occupies 17%. This happens because 17% is 20% of 83%.

For another example, read across row 2. In all of these examples, the secondary plot is to be 50% of the primary pie. In the first column, with a gap size of 0%, the main pie takes 67%, the gap is 0%, and the secondary pie is 33%. Move right to the 100% Gap Size column. Now, the gap is set to be 100% of the second pie. The table shows 50%/25%/25% meaning that the main pie takes up 50% of the width, the gap takes up 25% of the width, and the second pie takes up 25% of the width.

Table 4.1 Width of Main Pie/Gap/Second Pie

Figure 4.27 shows the extremes for the gap size and secondary plot size settings.

Figure 4.27 Changing the gap width and secondary plot size can create vastly different looks for the chart.

Excel also offers a robust system for choosing which slices should be in the secondary plot. The following section applies to both pie of pie and bar of pie charts.

Customizing the Split in the “Of Pie” Charts

With an “of pie” chart, you have absolute control over which slices appear in the main pie and which slices are sent to the secondary plot. Excel offers a surprising array of ways to control this setting.

When you right-click the pie and select Format Data Series, the Format Data Series dialog appears. The top setting in the dialog is the drop-down Split Series By, which offers the following options:

• Split Series by Position—You can change the spin button to indicate that the last n values should be shown in the secondary pie. Excel then uses the last n values from the original data set in the secondary pie.

• Split Series by Value—When you enter a value in the text box, Excel moves any slices with a value less than the entered value to the secondary pie.

• Split Series by Percentage Value—You can use the spin button to enter a value from 1 percent to 99 percent. Any slices smaller than the entered percentage move to the secondary plot. Note that if there are no slices smaller than that value, the secondary plot appears as an empty black circle.

• Split Series by Custom—This is a flexible setting where you can choose which slices to send to the secondary pie. After choosing Custom from the drop-down, you click an individual slice in the chart. Then, in the Format Data Point dialog, you indicate that the point belongs to the second plot or the first plot. You continue selecting additional slices in the chart and indicating the location for those points.

Using a Bar of Pie Chart

The bar of pie chart is similar in concept to the pie of pie chart. In the case of bar of pie, the large slices are plotted on a main pie. The smaller slices are moved over to a column chart on the right side of the chart area. All the settings for gap width, series lines, secondary plot size, and which slices move to the second plot area are valid for the bar of pie chart.

Figure 4.28 shows a bar of pie chart. Technically, this should be called a column of pie chart.

Figure 4.28 In a bar of pie chart, the smaller slices are grouped into Other, and the detail is shown in a column chart.

Using a Waterfall Chart to Tell the Story of Component Decomposition

As mentioned in Chapter 3, I spent a few months as a team member of a McKinsey & Company consulting gig when they were brought in to turn around the company where I used to work. McKinsey consultants were experts at creating cool charts including the waterfall chart that is closely associated with that firm.

The waterfall chart is a useful chart because it tells a story. For example, if you are an NPR fan, you might have listened to Ira Glass’ “This American Life” weekly radio show. Glass describes the show as a series of stories in which, “this happens, and then this happens, and then this happens, and then this happens….” Similarly, a waterfall chart makes a simple table into a story.

For example, waterfall charts in Figure 4.29 is used to analyze the profitability of a proposal. The chart starts with a tall column on the left side to show the total list price of the products you are selling. The next column appears to float in midair, dropping down from the total list price column to show the total discount that the sales team is proposing. The next column shows net revenue. The rest of the chart is a series of floating columns that show where all the revenue went. A tiny column on the right side shows the profit from the deal.

Figure 4.29 A waterfall chart breaks a single component chart out over several columns.

A waterfall chart can be used in many situations to turn a single component column chart into a whole-page chart. These charts present a dramatic picture of all the components in a process.

Creating a Stacked and Clustered Chart

In my seminars, people ask about creating both a stacked and clustered column for each time period, as shown in Figure 4.31. This type of chart can be used to show components of a cost figure compared to a total revenue figure.

Figure 4.31 Cost components stack up against revenue for each quarter.

Creating this chart is fairly difficult in Excel because it combines both a stacked column chart and a clustered column chart. Even if you can figure out how to create this chart, Excel has roadblocks to prevent the chart from looking good.

Secret 1: Combining Stacked Columns and Clustered Columns

Say you started out with three series of data and create a stacked revenue chart as shown in Figure 4.32. You need to move the Revenue portion of the column next to stacked cost components.

Figure 4.32 Start with all series stacked.

1. Double-click the Revenue portion of one of the columns. Excel displays the Format Data Series dialog.
2. Select Secondary Axis from the Plot Series On and click Close. By moving this series to the secondary axis, you can change the chart type for that one series.
3. With the Revenue series still selected, select Design, Change Chart Type. Select the first column chart, known as a clustered column chart.

The intermediate result shown in Figure 4.33 is horrible. Notice that the Revenue columns are plotted directly on top of the cost columns, making it impossible to see costs in Q2 and Q4.

Figure 4.33 Change Revenue to be a clustered column.

Secret 2: Moving a Series Over by Using Blank Series

To solve the problem in Figure 4.33, you can use another bit of chart magic—inserting some zero series to move the location of the revenue series.

1. Add two new series to your data table. Call them Blank 1 and Blank 2. Fill in zeros for these series.

2. Create a stacked column chart from the five series. Because you have five series and four quarters, Excel assumes that the Quarters should be category labels instead of series. Click the Switch Row/Column data in the Design tab to correct this.

3. Double-click the Revenue portion of the column and select Secondary Axis.

4. You need to format the blank series, but since they are zero, you cannot format them by double-clicking. Open the first drop-down in the Layout tab and select Series Blank 1. Click Format Selection and select Secondary Axis.

5. Repeat step 4 for the Blank 2 series.

6. Series Blank 2 should still be selected. Go to the Design tab and select Change Chart Type. Change the chart type from a stacked column to a clustered column.

You can now see the stacked cost column and the revenue column side-by-side, as shown in Figure 4.34. This is a useful trick.

Figure 4.34 Unlike Figure 4.33, the Revenue column is now shifted to the right.

Although you have overcome the major hurdle in completing the chart, you still have some problems with this chart. The revenue column is narrower than the cost columns. The blank series are showing up in the legend. In addition, the biggest problem is that the revenue column at 450 is showing up shorter than the cost columns that only total 359.

Figure 4.35 The location of the blank series is why the revenue series is shifted to the right.

Secret 3: Increasing Gap Width Makes Columns Narrower

Double-click one of the Cost series in the chart. There is a setting in the Format Data Series dialog called Gap Width. As you increase the gap between the series, the net effect is that the column becomes narrower.

There is not a good rule to figure out the correct gap width. You can repeatedly nudge the gap width in Figure 4.36 higher and higher until the size of the cost columns appears to be about the same width as the size of the revenue column. In this case, the setting of 314 percent seems about right.

Figure 4.36 Increase the gap width of the cost series to narrow the width of the cost columns.

Secret 4: Removing Items from the Legend Box

Choices in the Layout tab enable you to hide, show, and reposition the chart legend. You actually have much more control by clicking the legend, but you have to be careful.

Click the legend once to select the entire legend. Next, click one entry to select that single legend entry. With the single legend entry selected, you can change the font size, font color, and font style of that legend entry.

You might initially try something clever like changing the font color to white, but this leaves the marker and also leaves a gap where the legend should be. Instead, press the Delete key to remove that entry from the legend.

Figure 4.37 shows the progression of selecting the entire legend, selecting one blank series, typing delete, selecting the next blank series, and pressing Delete.

Figure 4.37 Select an individual legend entry and press the Delete key.

Secret 5: Adjusting Y-Axis Scale When There Are Two Axes

Sometimes you move a series to a secondary axis to show two series that have different orders of magnitude. In this case, you want the revenue and cost series to have the same scale.

You can solve this problem, but there is a cost. Normally, Excel chooses the scale automatically based on the data. If you later plug in new data for this chart and the revenue increased above 700, the upper limit for the chart will grow automatically.

You have to turn off override this behavior for both the left and right axis. This means that if the data later grows larger than the chart, you will have to re-visit the Format Axis dialog to adjust.

1. Double-click the numbers along the left axis. Excel displays the Format Axis dialog. Initially, settings for Minimum, Maximum, Major Unit, and Minor Unit are all set to Auto, as shown in Figure 4.38.

Figure 4.38 Initially the scale is set to automatic.

2. Change the Minimum setting from Auto to Fixed. You can leave the setting at zero.

3. Change the Maximum setting from Auto to Fixed. Type a new upper limit such as 750.

4. Change the Major Unit setting from Auto to Fixed. This setting controls where the gridlines are drawn.

At this state, the chart is not yet fixed. You have formatted the left axis to run from 0 to 750. However, the right axis is still running from 0 to 700 (see Figure 4.39).

Figure 4.39 Override settings for left axis.

With the dialog box still open, click the Right axis in the chart. Repeat the settings in steps 2 through 4 above. You will briefly see that the right axis is now at the same scale as the left axis, as shown in Figure 4.40.

Once you are sure the axes are set to the same scale, it seems redundant to have the numbers on the right side. Two settings in the current dialog box solve this problem: Change the Major Tick Mark Type to None and Change the Axis Labels to None.

Figure 4.40 Adjust the right axis to be the same as the left.

The final chart is shown in Figure 4.41.

Figure 4.41 The final chart makes it look easy.

Next Steps

In Chapter 5, “Creating Charts That Show Relationships,” you will learn how to create charts that highlight relationships. Scientists often use of scatter charts. However, you will learn how to use scatter charts in a variety of ways in business to demonstrate a correlation, or the lack of a correlation. In addition, you will learn that radar charts can be used to conduct annual performance reviews. Chapter 5 also takes a look at surface charts and frequency distributions.