Formatting, such as number type, alignment, and font style, determines how Excel displays the value within a cell. But did you know about ‘conditional formatting’, which is a more dynamic way of applying specified formatting only when certain conditions are met?

Conditional formatting provides a flexible range of rule-based options helping you to simplify many key tasks such as:

  • Locating specific values within a long list of data
  • Highlighting values which fall outside certain norms
  • Identifying duplicate entries

[themecolor]Here’s how it works[/themecolor]

In this first example, we used conditional formatting to highlight rows relating to “ABC Trading Company” with a green background and dark green text.

  1. Select the cells you want to apply conditional formatting to. Click the first cell in the range, and then drag to the last cell.
  2. On the Home tab, click Conditional Formatting > Highlight Cells Rules > Text that Contains.
  3. In the Text that Contains box, on the left, enter the text you want highlighted (“ABC Trading Company”).
  4. On the right, select the colour format for the text, and then click OK.
  5. The selected text is highlighted throughout the worksheet.

[themecolor]Advanced formatting[/themecolor]

Use these steps when you don’t necessarily have easily identifiable text to search for. In this example, we used conditional formatting to transaction values greater than £10,000 with bold red text.

  1. Select the range of cells you want to apply conditional formatting to.
  2. On the Home tab, click Conditional Formatting > Highlight Cells Rules > Greater Than.
  3. In the ‘Format cells that are GREATER THAN’ field, type £10,000.
  4. Specify the formatting you require in the field alongside.
  5. The cells with values greater than £10,000 are highlighted throughout the worksheet

[themecolor]Identify duplicates [/themecolor]

  1. Select the range of cells in which you wish to locate duplicate entries.
  2. On the Home tab, click Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. In the ‘Format cells that contain’ field, ensure ‘duplicate’ is selected.
  4. Specify the formatting you require in the field alongside.
  5. Cells containing duplicate values are highlighted throughout the worksheet.