Charts: Eight Months of Growth, One Column Chart, and the 11,704.67 the Picture Could Not Show

The chart in the board pack was a column chart of monthly revenue, eight bars, January to August. It went up. Not in a straight line — February dipped, July dipped — but the last bar was the tallest bar, 574,900 against January's 412,800, and everyone in the room read it correctly: revenue is up 39.3% in eight months.

The column beside it on the same sheet was advertising spend, and it was not charted. It went up 59.4%.

Nobody was hiding anything. The spend column was three inches from the revenue column, in the same file, in front of the same people. But a number you have to compute in your head from two columns is a number nobody computes, and the one worth computing here is what an order cost to win:

• January: 38,400 of advertising bought 1,284 orders — 29.91 an order.
• August: 61,200 bought 1,655 orders — 36.98 an order.

That is 23.6% more spent to win each order, over the same eight months in which the revenue chart went up and to the right. In August alone, buying 1,655 orders at January's rate would have cost 49,495.33. It cost 61,200. The difference is 11,704.67, in one month, and there was no chart it could have appeared on.

This is what charts are for and what they do wrong, in the same story. A chart is an argument about which numbers matter, made before anyone reads it, by whoever chose the range. The rest of this article is about making that choice deliberately: which shape, which axis, which series, and what happens to the picture when a month is missing.

What this covers. Everything here works in every version of Excel this century, with Excel for the web differing mainly in that it cannot create sparklines (it displays them fine). Value From Cells data labels in section 10 need Excel 2013 or later. Nothing here needs VBA, though section 11 names the one thing that still does.

1) The Four Questions That Choose the Chart

Most chart choices are made by scrolling the Insert gallery until something looks nice. Four questions get there faster, and they are about the data rather than the picture.

What is on the category axis — time, or things? Time goes left to right in chronological order, always, and never gets sorted by size. Things — products, regions, reps — are unordered until you order them, and sorting them by value is usually the whole point.

How many categories? Up to about eight, columns are readable. Beyond twelve or so, the labels start rotating to 45 degrees, and a horizontal bar chart with the names down the left reads better than a column chart with the names on their side.

One unit or two? Revenue in currency and on-time delivery in per cent cannot share an axis without one of them becoming a flat line on the floor. That is section 4.

Is it a part of a whole, right now? That, and only that, is what a pie chart is for — one moment, up to about five slices, adding to 100%. Eight months of revenue in a pie is a time series drawn in a circle: it has no order, no direction, and it makes the biggest month look like the most important one.

🎯 Scenario: The pack needs one chart for the eight months of revenue in B2:B9. Eight points is few, the categories are months, and the shape of each month matters as much as the trend, so it is a column chart — A1:B9 selected, Insert → Column, done. If it were thirty-six months it would be a line chart, because thirty-six columns is a fence.

2) The Axis Is an Argument

Take the on-time delivery column, E2:E9: 96.8%, 96.1%, 97.2%, 95.8%, 94.9%, 94.1%, 93.8%, 94.5%. Plot it as a line chart three times, changing nothing but two numbers in Format Axis → Bounds.

All three charts contain the same eight numbers. The reader's conclusion is set by the Bounds box, not by the data, which is why the axis deserves as much thought as the series.

Two rules make the choice for you most of the time:

A column or bar chart must start at zero. The length of the bar is the quantity; halve the axis and you halve the apparent value. This is not a style preference, it is what the shape means. Excel gets this right by default and people override it.

A line chart need not start at zero, because the message of a line is the slope, not the area. Forcing an interest rate, a percentage or a temperature to a zero baseline usually flattens the thing you drew the chart to show.

So what should the on-time chart use? Neither extreme — the number that matters is the service target. Pick bounds that comfortably contain the data and the threshold, and put the threshold on the chart:

Lower bound:  =FLOOR(MIN(E2:E9),0.02)     → FLOOR(0.938,0.02)   = 0.92
Upper bound:  =CEILING(MAX(E2:E9),0.02)   → CEILING(0.972,0.02) = 0.98

That gives 92% to 98% — the fall is visible, the exaggeration is bounded, and the 95% line drawn across it says the thing worth saying: May, June, July and August were all under target, four months in a row, while the revenue chart next to it was going up.

How to draw the target line: add a helper column of the constant, F2:F9 = 0.95, include it in the chart range, and format that series as a line with no markers. It is more robust than a drawn shape, because a drawn shape does not move when the axis changes.

One caveat worth knowing before you build a template around this. Axis bounds cannot be linked to a cell. You can compute 0.92 and 0.98 with MIN and MAX, and you still have to type them into the Bounds box by hand — the only way to link them is VBA. Auto re-derives them on every recalculation, which is fine for a working chart and jumpy for a published one.

3) The Chart Nobody Drew

Back to the 11,704.67. It never appeared because nobody plotted it, and nobody plotted it because it does not exist as a column — it is a ratio of two columns that both had to be divided before anything could be drawn.

That is the general case: the interesting series is usually one you have to build. Two helper columns turn this data set into the chart that answers the question the revenue chart raised.

F2:  =D2/C2                    → 29.906542…, the advertising cost of one order
G2:  =B2/C2                    → 321.4953…,  the revenue one order brings in

Fill both down to row 9 and the picture changes completely. Revenue per order rose from 321.50 to 347.37 — up 8.0%, genuinely good news. Advertising per order rose from 29.91 to 36.98 — up 23.6%. The gap between those two rates is the entire story of the eight months, and neither of the two numbers is in the source data.

🎯 Scenario: You want one chart that shows whether growth is being bought. Plot F2:F9 as a line against the months. It rises from 29.91 to 39.17 in July before easing to 36.98 — and the July peak, the same month revenue dipped, is a question worth asking that no revenue chart can raise.

And the trap in the summary row. Asked for "the cost per order over the period", the reflex is to average the eight monthly figures:

=AVERAGE(F2:F9)                → 34.504873…, the average of eight rates
=SUM(D2:D9)/SUM(C2:C9)         → 34.650083…, the rate for the whole period

The second one is the answer. The first is the average of eight ratios with different denominators, and it comes out 0.4% low here precisely because the expensive months were also the busy ones — each month gets one eighth of the weight regardless of how many orders it carried. It is the same arithmetic that makes an unweighted average of percentages wrong: the simple average of the on-time column is 95.4%, while weighting each month by its orders, =SUMPRODUCT(C2:C9,E2:E9)/SUM(C2:C9), gives 95.31%. The gap is small and it is about ten more late deliveries than the flat average implies, because the worst months were the busiest.

4) Two Units, One Picture: The Combo Chart

Revenue runs from 386,500 to 574,900. Cost per order runs from 29.91 to 39.17. Put both on one axis and the cost line is a flat trace along the bottom of the chart, indistinguishable from zero, because on a scale that reaches 574,900 the difference between 30 and 39 is a third of one pixel.

The fix is a secondary axis, and there are two ways in:

1. Insert → Combo → Custom Combination. Set Revenue to Clustered Column, Cost per Order to Line, and tick Secondary Axis on the second one.
2. Or, on an existing chart, click the small series, Format Data Series → Secondary Axis.

Two rules that stop a combo chart from misleading:

Give the two series different shapes. Columns for the one on the left axis, a line for the one on the right. Two lines on two axes look like two lines on one axis, and readers compare their heights, which is meaningless.

Label both axes. Because — and this is the honest disclosure about secondary axes — you choose where the two series cross. Set the right-hand axis to 0–40 and the cost line sits above the revenue columns; set it to 0–100 and it sits below. Neither is false and neither is inevitable. A reader who cannot see the two scales cannot know that the crossing point was a choice.

🎯 Scenario: Show revenue against on-time delivery for the eight months. Revenue as columns on the left axis, on-time as a line on the right axis bounded 90%–100%. The picture is the whole problem in one frame: the columns climb, the line falls away from the 95% target from May onward, and the question of whether the growth is what broke delivery is now a question somebody will ask out loud.

5) Sorting: When to Rank and When to Leave It Alone

Time series are never sorted. A revenue chart sorted descending is not a chart of a business, it is a chart of a league table of months, and the trend — the only thing anyone wanted — is gone.

Ranking charts are always sorted. If the categories are products or reps or regions, an unsorted bar chart forces the reader to do the comparison the chart was supposed to do for them.

Sorting a chart means sorting the data behind it, and if the source is a Table you can sort it in place. When you cannot — the source is shared, or the order matters elsewhere — build the ranked view with formulas and plot that instead:

I2:  =LARGE($B$2:$B$9,ROW()-1)                                    → 574900, then 531400, 502650…
H2:  =INDEX($A$2:$A$9,MATCH(I2,$B$2:$B$9,0))                      → Aug, then Jun, May…

Fill down as far as you want the ranking to run — three rows for a top three, all eight for the full order. The chart then reads H2:I4 and re-ranks itself whenever the numbers change.

The duplicate trap: MATCH returns the first row with that value, so two months with identical revenue would both be labelled with the earlier month's name. When ties are plausible, rank on a value that cannot tie: =LARGE($B$2:$B$9+ROW($B$2:$B$9)/100000,ROW()-1) nudges each figure by a fraction of a row number, which is invisible at chart scale and unique by construction.

And the direction. In a horizontal bar chart Excel plots the first category at the bottom, so a range sorted largest-first draws upside down. Fix it on the axis, not in the data: Format Axis → Categories in reverse order.

6) Gaps, Zeros, Empty Strings and #N/A

September has not closed yet. What goes in the cell, and what does the line chart do about it? Four answers, four different pictures, and only one of them is honest:

That fifth row catches almost everybody. A perfectly reasonable formula — =IF(C11="","",D11/C11) — written to keep the sheet tidy will drop the chart line to zero for every future month, and the tidier the sheet looks, the worse the chart gets. The fix is to return NA() instead of "" in anything a chart will read:

F11: =IF(C11="",NA(),D11/C11)

The cost is #N/A showing on the sheet, which is ugly but honest. Hide it with a conditional format that paints the font white where ISNA is true, and leave the value in place for the chart. Do not replace it with IFERROR(…,0) — that is the same lie in a different function.

7) A Chart That Grows When September Is Typed

Build a chart on B2:B9 and it will plot exactly those eight cells for the rest of its life. Type September into row 10 and the chart does not change; nothing warns you; the pack goes out with a month missing.

The easy fix is a Table. Select the range, Ctrl+T, then build the chart from the Table. A row typed under the last one is absorbed into the Table, the Table is part of the series formula, and the chart extends itself. This is the reason to reach for a Table even when you want none of its other behaviour.

The fix when a Table is not an option — someone else's layout, or a sheet where a Table would break existing formulas — is a name that resolves to the used part of the column:

Formulas → Define Name
  Name:     ChartRevenue
  Refers to: =Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNT(Sheet1!$B:$B)+1)

COUNT counts the numbers in column B, INDEX returns the cell at that offset as a reference rather than a value, and the range ends wherever the data currently ends. Then select the series, and in the formula bar replace the range with the name, workbook-qualified:

=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$9,'Sales.xlsx'!ChartRevenue,1)

Two things to know or you will lose an afternoon: the name must be workbook-scoped, not sheet-scoped, and it must be typed with the workbook name in front of it in the series formula. Excel accepts nothing else there, and the error message does not explain why.

Category axis type, while you are in here. With months typed as text — "Jan", "Feb" — Excel builds a text axis: every category gets equal width, and if June is missing entirely the chart simply closes the gap and puts May next to July. With real dates it builds a date axis, which spaces points by time and leaves a visible hole where June should be. If your months are dates, keep them dates: the axis that shows a missing month is worth more than a tidier label.

8) Sparklines: One Chart Per Row, Inside a Cell

A sparkline is a chart drawn in the background of a single cell. There are no axes, no titles and no legend — which is the point, because it can sit beside a number in a table and give it a shape.

Insert → Sparklines → Line, data range B2:B9, location G1. A vertical range is fine; the sparkline is drawn left to right regardless. Repeat with D2:D9 in G2 and the two sit one above the other: revenue rising with two dips, ad spend rising without a single one. That contrast is the article's whole story in two cells.

Three things worth knowing:

• Each sparkline scales itself by default, so a row varying by 2% and a row varying by 200% look equally dramatic. Select the group, then Sparkline → Axis → Same for All Sparklines, whenever the rows are meant to be compared.
• Turn on the High Point and Low Point markers. In a shape that small, the two extremes are most of the information.
• A sparkline is not cell contents. You can type in a cell that holds one. Deleting the cell's value does not remove it — that needs Sparkline → Clear.

9) Labels, Titles and the Number Format Nobody Set

Data labels carry their own number format. By default they inherit the cell's, which is why a revenue chart labelled straight from the sheet shows 412800 rather than 412,800 when the cell was never formatted. Fix it at the source — format the cells, and the labels follow — or in Format Data Labels → Number, where unticking Linked to source lets the chart show #,##0,"k" while the sheet keeps its full figures.

Direct labels beat a legend whenever there are two or three series. A legend makes the reader look away from the line, match a colour, and look back. A label at the end of the line does not.

Value From Cells (2013 and later) is the one label feature worth going out of your way for: Format Data Labels → Label Options → Value From Cells points at any range, so a column of =IF(E2<0.95,"below target","") becomes a set of labels appearing only on the months that matter.

Link the title to a cell. Click the chart title, type = in the formula bar, click the cell, press Enter. Then a title cell holding ="On-Time Delivery — "&TEXT(MIN(E2:E9),"0.0%")&" low in "&INDEX(A2:A9,MATCH(MIN(E2:E9),E2:E9,0)) writes its own headline: "On-Time Delivery — 93.8% low in Jul". A chart that titles itself does not go stale when the data moves.

10) Twelve Traps

1. A deleted column leaves #REF! in the series formula, and the chart keeps the other series and quietly loses one. Deleting rows is safe; deleting the column a series points at is not.
2. Charts skip hidden rows. Filter the source and the chart follows the filter — often what you want, occasionally a surprise. Select Data → Hidden and Empty Cells → Show data in hidden rows and columns decides it.
3. Copying a chart to another workbook keeps pointing at the original. The series formula carries the source file's name with it; the chart looks right until the source file changes.
4. A pie chart of a time series is a category error however good it looks. So is a pie with nine slices, and so is a pie of anything that does not sum to a meaningful whole.
5. Stacked columns hide every series but the first. Only the bottom band starts at a common baseline, so the ones above it can only be compared to themselves.
6. A secondary axis without visible scales is unreadable in the only way that matters — the reader cannot tell where you put the crossing point.
7. 3-D charts trade accuracy for depth. The perspective makes the front bars larger than the back ones at the same value.
8. A truncated bar chart is the classic distortion, and the classic accident: a single click on Auto after typing a bound is all that separates the two.
9. Number formats live in two places. The axis and the data labels each have their own, and neither follows the cell once you have overridden it.
10. The chart title is not the axis title. A chart titled "Revenue" with an unlabelled axis reading 0 to 600000 leaves the reader guessing at currency and thousands.
11. Axis bounds cannot be linked to cells — only VBA can do it, so a template using computed bounds needs someone to retype them.
12. A chart is a snapshot of a range, not of a question. Charts age badly: the range still resolves, the numbers still update, and the reason anyone drew it is not stored anywhere. Put the question in the title.

11) Mini Exercises

Copy the grid into a blank sheet starting at A1. Each answer is one formula, or one menu route.

1. Build the missing series. In F2:F9, write the advertising cost per order. What are the highest and lowest months, and in which months do they fall?
2. Two ways to summarise it. Compute the period cost per order twice — as the average of the eight monthly rates, and as total spend over total orders. State which belongs in the pack and why the other is lower.
3. Bound the axis. Write the two formulas that give sensible bounds for an on-time delivery chart rounded outwards to the nearest 2 points, and say what they return for this data.
4. Rank without sorting. Produce the three highest revenue months, name and value, in two columns of formulas that re-rank themselves when the data changes.
5. Make a month missing. Delete the value in B6 and describe what the line chart does. Then type =NA() there, then ="", and describe each. Which two look identical to a reader, and which of those two is lying?
6. Count the breaches. Write one formula that returns how many months finished below a 95% on-time target, and one that returns the name of the first month that did.

Summary

A chart is a sentence about the data, and like a sentence it can be true and still be the wrong thing to say. The revenue chart was true. It went up 39.3%, exactly as drawn. What it could not say — because the series was chosen before anyone asked the question — was that each of those orders cost 23.6% more to win than it had in January, or that August alone carried 11,704.67 of that increase.

Three habits are most of the difference. Build the series before you build the chart, because the number that matters is usually a ratio of two columns and not a column. Choose the axis on purpose, since the same eight figures make a cliff or a flat line and only one of those answers a question. And when the data is missing, say so with NA() rather than a zero or a tidy empty string, because a line that dives to the floor is a claim about September that nobody meant to make.

The last one is the cheapest and the most often skipped: put the question in the title. A chart called "Revenue by Month" is a range with a picture on it. A chart called "Growth is up 39%, and each order costs 24% more to win" is an argument — and an argument, unlike a range, can be checked.