Course / Conditional formatting
Data bars and colour scales
Draw a bar in every cell, shade a grid of numbers into a heat map, and mark falls and rises in two colours. Beginner · 4 min · Data bars, Colour scales, Heat map
The rules so far answer yes or no: a cell is highlighted or it isn't. Two more show how much.
- A data bar draws a bar inside each cell, as long as its number is large. A column of numbers becomes a small bar chart, without leaving the table.
- A colour scale shades each cell by where its number falls between the lowest and the highest. A grid of numbers becomes a heat map, and the hot spots show themselves.
In Excel, they are Conditional Formatting → Data Bars and Color Scales.
Steps
Select the units sold,
B2:B9, click Conditional formatting in the toolbar, choose Data bars, and click Apply rule.The grid on the right counts shop visits by time of day. Select all its numbers,
F2:K5, choose Colour scale, keep White to green, and apply.The Change column has falls as well as rises, so one colour is not enough. Select
C2:C9, choose Colour scale, and pick Red, white, green.Rugs have had a good week. Change
B9to 1200 and watch every bar in the column.
Remember
- Data bars turn a column into a bar chart inside the table.
- A colour scale turns a grid into a heat map.
- One colour for less-to-more; two colours when there is a good end and a bad end.
- Both are drawn relative to the selection: a new largest value rescales everything.
Previous: Top, bottom and above average · Next: Highlight a whole row with a formula