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.

In Excel, they are Conditional Formatting → Data Bars and Color Scales.

Steps

  1. Select the units sold, B2:B9, click Conditional formatting in the toolbar, choose Data bars, and click Apply rule.

  2. 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.

  3. 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.

  4. Rugs have had a good week. Change B9 to 1200 and watch every bar in the column.

Remember

Previous: Top, bottom and above average · Next: Highlight a whole row with a formula