Excel Tip - Highlight Rows using Conditional Formatting

You can use Conditional Formatting to make data stand out in your Excel spreadsheets. Find out how cells can be formatted to change the text style, add a border or even fill the cell with colour...

In the following example, we are using Conditional Formatting to change the colour of rows based on a condition in column E:

  • In Excel, select the cells to be formatted:

  • On the Home Tab, select the Conditional Formatting tool:

  • Choose New Rule, then Use a formula to determine which cells to format:

  • In the Format values where this formula is true box, enter =$E3. Column E contains the criteria that will be used to identify which cells to format, and the number 3 is the starting row for the data.  The $ fixes the column but not the row, so that the subsequent rows E4:E6 can be formatted too.
  • In the example below, we want to highlight the rows where all the documents have been completed, we do this by searching for the word “Completed” in the E column.
  • Enter the full formulae as =$E3=”Completed”

  • Finally, click on the Format button and select the formatting options for the cell.  In this example, we used the Fill tab to add a background colour of blue to the rows:

  • Click OK to finish. Your data will now be formatted:


Pin it