Chapter 13: Advanced Charts

Lesson 6 of Introduction to Excel and chapter 6 of this book covered the everyday chart types: column, bar, line, pie and their relatives. MO-211 adds charts for special jobs: combining two kinds of data on one chart, showing how values are spread out, showing how a total builds up or narrows down, and showing a hierarchy. Most of these chart types build themselves from the data, so the skill to learn is laying out the data correctly and changing each chart's special options.

13.1 Dual-axis and combo charts

13.1.1 When you need two axes

Suppose you chart monthly revenue (in the hundreds of thousands) and the number of orders (in the hundreds) on one column chart. The order columns are too small to see, because both series share one vertical axis. A dual-axis chart fixes this by plotting one series against a secondary vertical axis on the right side, with its own scale.

A combo chart mixes chart types, such as columns for revenue and a line for orders. The two ideas usually go together: a combo chart with the line on a secondary axis is the classic dual-axis chart.

13.1.2 Creating a combo chart

1. Select the data, including the labels.
2. Choose Insert › Insert Combo Chart (in the Charts group) and pick one:
   • Clustered Column - Line: the last series as a line, on the same axis.
   • Clustered Column - Line on Secondary Axis: the last series as a line, on a secondary axis.
   • Stacked Area - Clustered Column.
   • Create Custom Combo Chart, to choose each series' type and axis yourself.

The custom option opens the Combo page of the Insert Chart dialog box. It lists each series with a Chart Type drop-down and a Secondary Axis check box. The preview updates as you choose.

13.1.3 Changing an existing chart

To turn an existing chart into a combo or dual-axis chart, select it and choose Chart Design › Change Chart Type, then choose Combo at the bottom of the list. Set each series' type and check Secondary Axis for the series that needs its own scale.

To move just one series to the secondary axis without changing types, double-click that series (or select it in Format › Current Selection). In the Format Data Series pane, under Series Options, choose Plot Series On › Secondary Axis.

13.1.4 Labeling the secondary axis

A second axis needs a title so readers know which scale is which. Choose Chart Design › Add Chart Element › Axis Titles › Secondary Vertical, then type the title. You can also format the secondary axis like any other axis: double-click it to set its minimum, maximum and number format.

13.2 Histograms and box and whisker charts

These two statistic charts show how values are distributed: where most of them fall, how spread out they are, and whether any are unusual. Both are under Insert › Insert Statistic Chart.

13.2.1 Histograms

A histogram groups numbers into ranges, called bins, and draws a column for each bin showing how many values fall in it. Use it for questions like "How are test scores spread out?" or "How many orders fall in each price range?"

To create one, select a single column of numbers (with or without its heading) and choose Insert › Insert Statistic Chart › Histogram. Excel chooses the bins automatically. Don't create a separate table of counts. The histogram counts the raw data itself.

To control the bins, double-click the horizontal axis to open Format Axis. Under Axis Options › Bins:

Option	Effect
By Category	Counts each text category instead of number ranges.
Automatic	Lets Excel choose the bins.
Bin width	Sets the size of each range, such as 10 for 60–70, 70–80 and so on.
Number of bins	Sets how many bins, and Excel works out the width.
Overflow bin	Puts every value above a cutoff in one last bin, such as >100.
Underflow bin	Puts every value at or below a cutoff in one first bin, such as ≤50.

The axis labels read like [60, 70] and (70, 80]. A square bracket means the number is included, and a parenthesis means it isn't.

The Pareto chart, on the same menu, is a histogram sorted from the largest column to the smallest, with a line showing the cumulative percentage.

13.2.2 Box and whisker charts

A box and whisker chart summarizes a set of numbers in one shape, and it makes it easy to compare several sets side by side:

• The box spans the middle half of the values, from the first quartile (25th percentile) to the third quartile (75th percentile).
• The line across the box is the median.
• The X marks the mean.
• The whiskers reach to the lowest and highest values that aren't outliers.
• Dots beyond the whiskers are outliers: values more than 1.5 times the box's height away from the box.

To create one, select the data and choose Insert › Insert Statistic Chart › Box and Whisker. For one box per group, such as test scores for each class, arrange the data in two columns: one for the group's name and one for the values. Each value is a row, and Excel draws a box for each group. Several numeric columns, one per series, give several boxes in each category.

Double-click a box to open Format Data Series. Its Series Options include:

• Show inner points: shows every value as a dot.
• Show outlier points: shows or hides the outlier dots.
• Show mean markers and Show mean line: show the X, and a line connecting the means of the boxes.
• Quartile Calculation: Exclusive median or Inclusive median, which changes how the quartiles are calculated when there's an odd number of values.

13.3 Waterfall charts

A waterfall chart shows how a starting value is changed by a series of increases and decreases to reach a final value. Each change is a floating column that starts where the previous one ended. Use it for questions like "How did we get from revenue to net income?" or "What changed our cash balance this month?"

13.3.1 Creating a waterfall chart

1. Lay out the data in two columns: labels and amounts. Increases are positive and decreases are negative. Include the starting value, and the subtotals and ending value if you want them shown as full columns.

   Item	Amount
   Starting cash	42,000
   Sales	68,500
   Payroll	-31,200
   Rent	-8,000
   Supplies	-6,750
   Ending cash	64,550

2. Select the data and choose Insert › Insert Waterfall, Funnel, Stock, Surface, or Radar Chart › Waterfall.

13.3.2 Setting totals

At first, Excel draws every row as a floating change, including the starting and ending balances. To make a column start from zero as a full total:

1. Click the column once to select the series, then click it again to select only that data point.
2. Right-click it and choose Set as Total, or check Set as total in the Format Data Point pane.

Do this for the starting value, the ending value and any subtotals. The legend shows Increase, Decrease and Total, each with its own color. To change a color, click the legend entry for it once, then again to select just that entry, and change its fill.

In the Format Data Series pane, Show connector lines draws the thin lines between columns.

13.4 Funnel and sunburst charts

13.4.1 Funnel charts

A funnel chart shows values that shrink from stage to stage of a process, drawn as centered bars from the largest at the top to the smallest at the bottom. Use it for a sales pipeline (leads → qualified → proposals → deals) or a sign-up process (visitors → sign-ups → active users).

Lay out the data as stages and values, in the order the process runs, usually from largest to smallest. Select it and choose Insert › Insert Waterfall, Funnel, Stock, Surface, or Radar Chart › Funnel.

Each bar is labeled with its value. To show percentages instead, add a column that calculates each stage as a percentage of the first, and chart that.

13.4.2 Sunburst charts

A sunburst chart shows hierarchical data as rings. The inner ring is the top level of the hierarchy, and each ring outward breaks the one inside it into parts. Use it for data with categories inside categories, such as sales by region, then state, then city, or a budget by department, then team.

Lay out the data with one column for each level, from the top level on the left, and the values in the last column. Repeat higher-level labels on each row:

Region	State	City	Sales
West	California	Fresno	180,000
West	California	San Diego	240,000
West	Oregon	Salem	95,000
East	New York	Albany	130,000
East	Maine	Portland	70,000

Select the data and choose Insert › Insert Hierarchy Chart › Sunburst.

The Treemap chart, on the same menu, shows the same kind of data as nested rectangles.

13.4.3 Choosing the right chart

The task describes	Use
Two series with very different scales on one chart	A combo chart with a secondary axis
How many values fall in each range	Histogram
Median, quartiles and outliers, or comparing spreads	Box and whisker
A starting value, changes and an ending value	Waterfall
Stages of a process that narrow	Funnel
Categories nested inside categories	Sunburst (or treemap)

13.5 Practice