Hints - Microsoft Excel - Hide Errors

You can use Excel conditional formatting to check for errors, and change the font colour to match the cell colour. In this example, if column A contains a zero, the #DIV/0! error is displayed in column C.

Hide Duplicate Values

In a table, each row should have all data entered, to enable sorting and filtering. However, you can use Excel conditional formatting to hide the duplicate values, and make the list easier to read. 

In this example, when the table is sorted by Region, the second (and subsequent) occurences of each region name will have white font colour. You can see the text if you select the cells.

Highlight Duplicates in Column

Use Excel conditional formatting to highlight duplicate entries in a specific column, or in a range of cells (multiple rows and columns):

In Excel 2007 or later:

For Excel 2003:

Highlight Duplicate Records in a List

Use Excel conditional formatting to highlight duplicate records in a list. Use a formula to combine all the fields into one column, then test that column for duplicates.

Create a formula to combine the data:

Add the conditional formatting:

Highlight Items in a List

Use Excel conditional formatting to highlight items that are in a list on the worksheet.

Show Temperatures With a Color Scale

To show hot temperatures in a red cell, and cold temperatures in a blue cell, you can use Excel's conditional formatting color scale. This feature is available in Excel 2007 and later versions.

Highlight Lottery Numbers

You can use Excel conditional formatting to highlight the ticket numbers that have been drawn in a lottery. In this example the ticket numbers are in cells B2:G4, and the drawn numbers are entered in cells B6:G6

Highlight Upcoming Expiry Dates

You can use Excel conditional formatting to highlight payments that are due in the next thirty days. In this example, Due dates are entered in cells A2:A4. 

Highlight Expired Dates

You can use Excel conditional formatting to highlight policies with dates that have expired. In this example, Due dates are entered in cells B2:B7.