Charts and conditional formatting

In depth

Why bars beat pies

Cleveland and McGill (1984) measured how accurately people read quantities from different graphical encodings. They ranked the elementary tasks from most to least accurate: position along a common scale; position along non-aligned scales; length, direction and angle; area; volume and curvature; shading and colour saturation. In their experiment, judgements of position, as in a bar chart, were more accurate than judgements of angle, as in a pie chart.

That ranking explains most chart advice. Sorted bars on a common baseline use the most accurate task. In a stacked column only the bottom segment sits on the baseline; the others float, so comparing them across columns is a judgement of length on non-aligned scales. A pie asks for angles and areas. Microsoft suggests a pie chart only when there is one data series, no negative values, almost no zeros, and no more than seven categories that together make the whole.

Two parts of one whole are a fair use of a pie, or of a single 100% stacked bar. Five regions are already harder: their shares of spring order value run from 16.7% to 23.4%, and slices that close in size are hard to rank by eye. A sorted bar chart of the same five numbers ranks them at a glance.

Choosing between similar charts

Column or bar. Both compare categories. Microsoft suggests a bar chart (horizontal) when the labels are long, and bars also suit a ranking of many items, because the labels read left to right. Columns suit a handful of periods.

Line or scatter. In a line chart the horizontal axis is a category axis: text labels go at equal spacing whatever they say, a date axis puts dates in time order, and numbers are treated as labels. A scatter chart has two value axes and places every point at its numeric x. So if the x values are numbers that aren’t evenly spaced, such as the number of guests at each catering event, a line chart misplaces them; Microsoft’s rule of thumb is a line chart for non-numeric x values and a scatter chart for numeric ones.

Stacked and 100% stacked. A stacked column emphasises the total and shows the parts inside it; a 100% stacked column makes every column the same height and shows shares, which suits comparing a mix across months or regions. Use either with two or three parts at most, and put the part the reader must compare on the baseline.

Histogram and box and whisker. Both show a distribution, and Excel 2016 and later have both (Insert > Insert Statistic Chart). A histogram groups a numeric column into bins and draws a bar for the count in each: by default Excel sets the bin width by Scott’s normal reference rule, and you can set a bin width, a number of bins, and overflow and underflow bins instead. A box and whisker chart draws the quartiles as a box, the median inside it and whiskers beyond, with outlier points past the whiskers. The next lesson builds the frequency table behind a histogram.

Combo charts and secondary axes. A combo chart combines chart types, typically columns with a line, and Microsoft suggests a secondary axis when series vary widely or are of different kinds, such as price and volume. Use it with care: the two scales are independent, so where the line crosses the columns, and how steep it looks, depend on axis limits that someone chose. Label both axes with units, colour each axis like its series, and ask whether a ratio of the two (order value per order, say) or two charts one above the other would say it more plainly.

Sparklines. A sparkline is a tiny chart inside one worksheet cell (Insert > Sparklines: Line, Column or Win/Loss). Put one at the end of each row of a table, for example each product’s weekly sales, to show many trends side by side without a chart for each. Markers can pick out individual points, and an axis can be shown when values change sign.

Truncated axes and their exaggeration

When a bar chart’s value axis starts at a baseline $b$ above zero, two bars for values $v_1$ and $v_2$ have lengths $v_1 - b$ and $v_2 - b$. Their apparent ratio is $(v_1 - b) \div (v_2 - b)$, while the true ratio is $v_1 \div v_2$. The closer $b$ is to the smaller value, the greater the exaggeration. A truncated axis on a line chart doesn’t carry this error, because nobody reads a line’s height from zero, but it can still make a small wobble look like a crisis, so label it.

Conditional formatting rules in Excel

• Highlight Cells Rules: Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring and Duplicate Values. Each compares a cell with a value you type (or a cell you point to).
• Top/Bottom Rules: Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average and Below Average. The 10 is only the default: you can choose 1 to 1,000 items or 1% to 100% of the range. Average rules compare with the mean of the selected range.
• Data Bars: a bar inside each cell, longer for larger values.
• Color Scales: a two- or three-colour gradient between the minimum, a midpoint and the maximum.
• Icon Sets: three to five icons, each for a band of values set by thresholds. Microsoft says the default bands are equal in size; with three arrows that is roughly the top, middle and bottom third of the way from the lowest value to the highest (the sheets in this lesson split at 67% and 33%).
• New Rule > Use a formula to determine which cells to format: any formula that returns TRUE or FALSE.

By default (Automatic) a data bar’s length is proportional to its value, so the longest bar belongs to the largest value; a colour scale runs from the lowest value to the highest. You can change either by setting the minimum and maximum types (a number, a percent, a percentile or a formula). That matters when one value dwarfs the rest. Lantern’s office order of $1,512 would stretch the data bars so far that a $58 gift box got a sliver of a bar; setting the maximum to a number, such as 200, or to a percentile, restores the comparison among ordinary orders.

Formula rules and mixed references

A formula rule is written for the top-left cell of the “Applies to” range, and Excel adjusts it for every other cell as if the formula had been filled across and down: relative references move, absolute ones don’t. That is why whole-row highlighting needs a mixed reference such as $F2, with the column locked and the row free. The formula must return TRUE or FALSE (or 1 or 0), and it must start with an equals sign.

Useful patterns, each written for row 2 of a range that starts in column A: =$D2>=100 (orders of $100 or more); =AND($E2="app",$D2>=30) (app orders of at least $30); =COUNTIF($B$2:$B$232,$B2)>1 (customers with more than one order: the whole range locked, the row free); =$D2>=LARGE($D$2:$D$232,10) (the ten largest orders, with ties; LARGE(range, k) returns the kth largest value). One trap: when you click cells while typing a rule, Excel inserts absolute references such as $D$2, which would make every row test row 2. Delete the $ before the row number.

Test a rule before trusting it. Type the same formula in a spare cell of row 2, fill it across and down, and check that it says TRUE exactly where you expect: conditional formatting will move it in the same way. If the formula returns an error in some cells, those cells are not formatted, so a rule can fail silently.

Rule order and Stop If True

Home > Conditional Formatting > Manage Rules lists the rules for a range in order of precedence, top to bottom; a new rule goes to the top, and Move Up and Move Down change the order. When two true rules set different things (one bold, one a red fill), both apply. When they conflict (two fills), the rule higher in the list wins. A true rule also overrides any manual formatting of the cell.

Stop If True, a tick box beside each rule, stops Excel evaluating the rules below it once that rule is true. Microsoft describes it as a backward-compatibility setting, and it isn’t available for data bars, colour scales or icon sets. Because conflicting formats are already settled by precedence, Stop If True matters when a lower rule sets something that doesn’t conflict, such as bold, a data bar or an icon set, which a formula rule with Stop If True hides for the rows it matches.