Skip to content
Tutorials Center
My learning

Topics

Lesson 54 of 93 ยท Charts and Visualization

Tornado, Funnel, Waffle, Pictograph and Actual-vs-Target Charts

Build business charts Excel does not offer as one click: tornado, funnel, waffle, pictograph, actual vs target, goal line, trend arrows and step charts, with the data layout for each.

Excel's chart gallery covers the basics (Choosing the Right Chart), but business reports regularly ask for shapes that are not in it: a tornado for sensitivity analysis, a funnel for a sales pipeline, a waffle for "62% complete", an actual-versus-target bar. All of them are built from ordinary chart types plus a smart data layout. Learn the layouts and you can make them in a minute.

Excel cannot run on this site. The chart steps are real Excel; the TryIts use JavaScript stand-ins that prepare each layout's numbers (sorted tornado bars, a 10 by 10 waffle, step-chart points) and print them as text so you can check the logic before building the chart. They do not draw charts.

Tornado (butterfly) chart

A tornado compares two sides of the same categories, or shows how much each input moves a result. The bars point left and right from a center line and are sorted by size, so the longest bar is on top and the shape narrows downward.

Layout: categories in column A, the "low case" change in B (negative numbers), the "high case" change in C (positive).

  1. Sort the table by the larger absolute value, descending.
  2. Select A:C and Insert > Bar > Clustered Bar.
  3. Click any bar, Format Data Series > Series Overlap 100% (so the two sides share one row), Gap width 30%.
  4. Right-click the vertical axis, Format Axis > Categories in reverse order (so the biggest sits on top), and set Labels > Label Position: Low so category names stay at the left edge instead of colliding with the bars.

For a "population pyramid" with two groups of positive values (men and women, last year and this year), store one side as a negative number, or format its axis number as 0;0 so negative numbers print without a minus sign.

JS Sorting a tornado and checking the swing

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Funnel chart

A funnel shows stages that shrink: visits, sign-ups, trials, customers. Recent Excel versions (Microsoft 365, 2019 and later) include Insert > Waterfall, Funnel, Stock > Funnel, which needs only the stage names and values in descending order. For any version, build one from a stacked bar with an invisible spacer:

  1. Add a helper column Spacer = (MAX(values) - value) / 2.
  2. Chart Spacer and Value as a stacked bar, then set the Spacer series to No fill. The visible bars are now centered, which creates the funnel silhouette.
  3. Reverse the category axis so "Visits" is at the top, and add data labels. Show the conversion rate between stages in a column with =B3/B2.

The funnel type only makes sense when each value is at most the previous one; if not, the data is not a funnel and a column chart is more honest.

Waffle chart

A waffle chart is a 10 by 10 grid of 100 squares in which a percentage of squares is colored, which makes "62%" instantly visible. It is a worksheet trick, not a chart:

  1. Fill a 10 by 10 block with the numbers 1 to 100 (=SEQUENCE(10,10) (SEQUENCE) gives it in one formula; reverse the order with =SEQUENCE(10,10,100,-1) if you want to fill from the bottom).
  2. Make the cells square (column width and row height equal) and the font tiny or the same color as the fill.
  3. Apply a conditional formatting rule: =A1<=$H$1 where H1 is the percentage times 100, with a green fill; a second rule =A1>$H$1 with grey.
  4. Hide the numbers with the custom number format ;;;.
JS A 10 by 10 waffle driven by one percentage

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Pictograph

A pictograph repeats a small picture, one icon per unit or per ten units. In a column chart, copy a picture or icon (Insert > Icons or Pictures), select a data series, paste it, and in Format Data Series > Fill > Picture or texture fill choose Stack and Scale with units per picture (for example 10). Each bar then shows a row of icons. In the grid, you can also repeat a symbol with =REPT("โ—", B2/10) and a font that has the symbol, which gives an in-cell pictograph that updates with the data.

Actual versus target

When one number is judged against a goal, you have several designs:

  • Bullet chart (one KPI): a stacked bar of three qualitative ranges in light greys (poor, ok, good) behind a thin dark bar for the actual value and a marker for the target. Build the ranges as stacked bar on the primary axis, put the actual on the secondary axis with a narrower bar, and add the target as a scatter point or error bar.
  • Column with target line: columns for actuals, a line series for the target, set through Change Series Chart Type > Combo (Choosing the Right Chart). A constant target needs the same number in every row of its helper column.
  • Overlapped bars: actual and target as two clustered series with Series Overlap 100%, the target wide and light, the actual narrow and dark, drawn in front.
  • Status in cells: =IF(B2>=C2,"โ–ฒ","โ–ผ") with a font color from conditional formatting, or the icon sets in Sparklines, Icons and In-Cell Visuals.

Goal line and trend arrows

For a goal line that stops at the data, add a helper column Goal with the same value in each row, plot it as a line, and hide the markers. To show the direction of change with arrows, use a custom number format on a "% change" column:

TEXT
[Color10]โ–ฒ 0.0%;[Red]โ–ผ 0.0%;0.0%

The first section applies to positive values, the second to negative and the third to zero; [Color10] is a green from Excel's 56-color palette. The cell holds a real number, so it can still be summed or charted.

Step chart

A step chart holds a value flat until the next change, like a price list or a stock level. Excel has no step type, so double the data points. For each change at date d to value v, add a point (d, previous value) before (d, v), then draw it as a scatter with straight lines (or a line chart with a date axis).

JS Step-chart points from a change log

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Which chart answers which question?

Question Chart
Which input matters most? Tornado
Where do we lose people? Funnel
How far along is it (one number)? Waffle or bullet
How does each item compare with its target? Column with a target line
What is the value between changes? Step

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a tornado chart, what should you do with Series Overlap?

    Overlap 100% makes the negative and positive bars occupy the same row, forming the center-line butterfly shape.
  2. 2. How do you build a funnel from a stacked bar when the Funnel type is not available?

    Half the missing width on each side centers every bar, which draws the funnel silhouette once the spacer has no fill.
  3. 3. What does the number format [Color10]โ–ฒ 0.0%;[Red]โ–ผ 0.0%;0.0% do?

    Number formats have positive, negative and zero sections. The arrows are part of the display only; the cell still holds the number.
  4. 4. How do you make a step chart in Excel?

    Excel lacks a step type, so each change is plotted twice: first at the old value, then at the new one, creating a vertical riser.

Summary

  • Tornado: clustered bar, overlap 100%, sorted by swing, categories reversed, labels set to Low.
  • Funnel: the built-in Funnel type, or a stacked bar with an invisible spacer of half the missing width.
  • Waffle: a 10 by 10 grid of 1 to 100 painted by conditional formatting; pictograph: picture fill with stack and scale, or REPT.
  • Actual versus target: bullet chart, column with a target line series, or overlapped bars.
  • Trend arrows come from a custom number format; step charts come from doubled data points.

Next: Dynamic and Interactive Charts.