Excel 2019 (Part 23): Conditional Formatting

Imagine you have a spreadsheet with thousands of rows of data. It would be difficult to spot patterns and trends just by examining the raw information. Similar to charts , conditional formatting provides a way to visualize the data and make the spreadsheet easier to understand.

 

Learn about conditional formatting.

Conditional formatting allows you to automatically apply formatting—such as color, icons, and data bars—to one or more cells based on the cell value. To do this, you need to create a conditional formatting rule. For example, a conditional formatting rule might be: If the value is less than $2000, color that cell red. By applying this rule, you can quickly see which cells contain values ​​less than $2000.

How to create conditional formatting rules

For example, imagine you have a spreadsheet containing sales data and you want to see which salespeople are meeting their monthly sales targets. The sales target is $4000/month, so we would create a conditional formatting rule for any cell containing a value higher than 4000.

1. Select the desired cells for the conditional formatting rule.

 

2. From the Home tab , click the Conditional Formatting command. A drop-down menu will appear.

3. Hover over the desired conditional formatting style, then select the desired rule from the menu that appears. For example, to highlight cells greater than $4000.

4. A dialog box will appear. Enter the desired value(s) in the empty field. For example, you would enter 4000 as the value.

5. Choose a formatting style from the drop-down menu. For example, select Green Fill with Dark Green Text , then click OK.

6. Conditional formatting will be applied to the selected cells. In the example, it's easy to see which salespeople achieved the $4,000 sales target for each month.

 

You can apply multiple conditional formatting rules to a range of cells or worksheets, allowing you to visualize different trends and patterns in your data.

Install conditional formatting presets.

Excel has several predefined styles—also known as presets—that you can use to quickly apply conditional formatting to your data. These are grouped into three categories:

1. Data bars are horizontal bars added to each cell, similar to a bar chart.

1. Color scales change the color of each cell based on its value. Each color scale uses a two- or three-color gradient. For example, in the Green-Yellow-Red scale, the highest values ​​are green, the middle values ​​are yellow, and the lowest values ​​correspond to red.

1. Icon Sets add a specific icon to each cell based on its value.

How to use conditional formatting presets

1. Select the desired cells for the conditional formatting rule.

 

2. Click on the Conditional Formatting command. A drop-down menu will appear.

3. Hover your mouse over the desired preset value, then select the preset style from the menu that appears.

4. Conditional formatting will be applied to the selected cells.

Remove conditional formatting.

To remove conditional formatting:

1. Click on the Conditional Formatting command. A drop-down menu will appear.

2. Hover over Clear Rules , then select the rule you want to remove. For example, select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet.

3. Conditional formatting will be removed.

Click on Manage Rules to edit or delete individual rules. This is especially useful if you have applied multiple rules to a worksheet.