Exceljet

Quick, clean, and to the point

What is conditional formatting?

What is conditional formatting?

You can think of conditional formatting as automatic formatting that is triggered by conditions that you define. For example, you can use conditional formatting to automatically change the color of cells that contain values greater than or less certain values. Conditional formatting is a great way to visually highlight important information in a worksheet.

Let's take a look.

A common use of conditional formatting is to highlight values in a set of data. Here we have a table that shows monthly sales figures for a group of sales people. The monthly sales quota is $5000, so let's use conditional formatting to highlight monthly sales numbers that are below that value.

Conditional formatting is applied with rules. First, select the cells you'd like to format. In our sales table, that's all the monthly sales figures, excluding totals.

Then, open the Conditional Formatting menu, which appears in the Styles group on the home tab of the ribbon.

There are many conditional format presets in this menu. For this example, we'll use a preset in the Highlight Cells Rules section. We'd like to highlight values below a certain threshold, so we'll choose the "Less than" rule to start.

This type of rule requires two inputs: the criteria, or condition, used to trigger the rule, and, the format to apply when that condition is met.

Because our threshold is 5000, we'll use 5000 for the condition.

For the format, we can choose from several pre-built options, or we can define a custom format. For now, let's choose the "light red fill with dark red text" preset.

Once we click OK, the rule is created and see the formatting applied to cells that meet the condition we defined.

Conditional formatting is fully automatic. If we change a value in the table to be lower than our threshold of $5000, the rule that we created is triggered and the formatting is automatically applied.

Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.