Excel Conditional Formatting Videos

Excel videos show how to use conditional formatting features. Highlight a row, hide duplicates, and more

Conditional Formatting Basics

With conditional formatting you can select cells, and set rules for formatting them. With the rules, you can set the cell font, the fill colour and the border settings, based on the value in the selected cell, or the contents of another cell.

There's a limit of 3 conditional formatting rules per cell, in Excel 2003 and earlier versions. If your rules (conditions) are met, then the conditional formatting is applied.

You can add formatting to highlight cells. For example, you can set conditional formatting so a cell is coloured blue if it has a value that's higher than 75. As the second condition, you can make the cell turn green if it contains a value lower than 50.

Watch this video to see the conditional formatting applied to a group of cells. For written instructions for this Excel tutorial, see: Conditional Formatting

Highlight a Row

You can add conditional formatting to an entire row if one cell is over a set amount. The conditional formatting checks the value in a specific cell in each row, and applies formatting to other cells in the same row, based on that value.

For example, you could colour the entire row in a table, if the values in column B are over a set value. In this video, we'll colour cells blue, if the number of units, in column B, is greater than 75. After the conditional formatting is applied, we'll be able to see at a glance, which dates have a high number of units.

For written instructions for this Excel tutorial, see: Conditional Formatting

Hide Duplicates

In a table of Excel data, each row should have data entered in every column, to enable sorting and filtering. That might make the the table harder to read, because it's not clear where each group of data starts.

However, you can use Excel conditional formatting to hide the duplicate values, and make the list easier to read. In this video, when the table is sorted by Region, the second (and subsequent) occurrences of each region name will have white font colour.

For written instructions for this Excel tutorial, see: Hide Duplicate Values

Refer to Cells on Another Sheet

Just like data validation, conditional formatting displays an error message if you try to refer to cells on another worksheet. However, you can name the range on the other sheet, and refer to the named range in the conditional formatting. For some reason, Excel is okay with references to named ranges on another sheet.

For written instructions for this Excel tutorial, see: Conditional Formatting Other Sheet

Hide Questions for Data Entry

In some Excel files, you could have a series of questions, and some of those questions could have two or more subsequent questions. For example, a user could select Yes or No, in answer to the question, “Do you have any dependents?”

If Yes is selected, the next heading and data entry box appear, "Please list dependent names and ages."

To make it easier for users to work with the file, you can use conditional formatting to hide the subsequent questions, and show them only if the applicable answer is selected in a drop down list.

For written instructions for this Excel tutorial, see: Create Secret Hidden Questions in Excel

Conditional Formatting Data Bars

Use Excel conditional formatting data bars to create a mini-chart in the worksheet cells. This gives a quick picture of the data, to make it easier to see the overall trend.

For written instructions for this Excel tutorial, see: Excel Conditional Formatting - Data Bars

More Excel Tips

Conditional Formatting Basics

Excel Videos - Index

Data Entry Tips

Names, Macros, Numbers

Mouse Shortcuts