How to Use Microsoft Excel Conditional Formatting to Highlight Data

When you build a large spreadsheet containing hundreds of rows of data, it is easy for critical information to get lost in the sea of numbers. If you are tracking monthly sales figures and a particular region drops below a target threshold, or an employee’s performance rating suddenly spikes, you will not notice unless you manually scan every single cell.

Conditional Formatting in Microsoft Excel automates this visual inspection. It allows you to create rules that automatically change the background colour, font colour, or icon of a cell based on its value. Cells containing values above a certain threshold can be highlighted in green, while cells below target can turn red, transforming your spreadsheet into a live, colour-coded dashboard.

How to Apply a Basic Conditional Formatting Rule

1. Open your Excel spreadsheet and select the range of cells you want to format (e.g., highlight Column C, which contains sales figures).
2. Click the Home tab in the top ribbon menu.
3. In the “Styles” group, click Conditional Formatting.
4. Hover over Highlight Cells Rules. A submenu will appear with common options:
   • Greater Than…
   • Less Than…
   • Between…
   • Equal To…
   • Text That Contains…
   • Duplicate Values…
5. Select Greater Than… from the submenu.
6. A dialogue box will appear. Type a value (e.g., 10000).
7. Use the dropdown on the right to choose a formatting style (e.g., “Green Fill with Dark Green Text”).
8. Click OK.

Every cell in your selected range that contains a value greater than 10,000 will instantly turn green. If someone later edits a cell to exceed 10,000, it will automatically change colour without any manual intervention.

How to Use Colour Scales for Gradients

Instead of a simple on/off colour, you can apply a Colour Scale that creates a smooth gradient across your data.

1. Select your data range.
2. Click Conditional Formatting > Color Scales.
3. Choose a preset gradient (e.g., Green-Yellow-Red).

Excel will analyse the minimum and maximum values in your range and apply a smooth colour gradient. The lowest values will be deep red, the mid-range values will be yellow, and the highest values will be bright green. This provides an instant visual heat map of your data.

How to Use Data Bars for In-Cell Charts

For an even more visual approach, Data Bars insert a small horizontal bar chart directly inside each cell, proportional to the cell’s value.

1. Select your data range.
2. Click Conditional Formatting > Data Bars.
3. Choose a colour style (solid or gradient fill).

Each cell will now display a coloured bar behind the number. The highest value in the range will have a bar that fills the entire cell, while smaller values will have proportionally shorter bars. This allows you to instantly compare relative values without needing a separate chart.

How to Create a Custom Rule

If the preset options are too simple for your needs, you can write a completely custom rule using a formula.

1. Select your data range.
2. Click Conditional Formatting > New Rule.
3. Select Use a formula to determine which cells to format.
4. Type a formula that returns TRUE or FALSE. For example, to highlight an entire row where the “Status” column equals “Overdue”:

   =$E1="Overdue"

5. Click the Format button, select a background fill colour (e.g., bright red), and click OK.

Every row where Column E says “Overdue” will now be highlighted in red across the entire row.