Applying Conditional Formatting In Excel

Conditional Formatting allows you to format a cell or group of cells based on a certain condition or conditions being met.

Excel provides a wide range of conditional formatting options, and they are simple to implement in your worksheets.

If you would like to learn more about conditional formatting, then see our Excel Course Offerings.

 

What is Conditional Formatting in Excel?

In order to better understand the concept of conditional formatting, let’s look at an instance where conditional formatting comes in handy.

In the example below, a hypothetical plant nursery is reviewing the sales data for the month and wants to highlight the bellflower data only.

They could highlight this data by using conditional formatting as shown below.

 

 

Conditional Formatting – A Simple Example

Let’s look at a simple scenario that will show you how to implement conditional formatting. We have student marks for a test and we want to see all the marks above 7 highlighted.

 

 

• Select cells B5:B20.

 

 

• With cell range B5:B20 still selected go to the Styles group on the Home Tab (Step 1 in the image) and select Conditional Formatting (Step 2 in the image).

 

 

• Select Highlight Cells Rules and then Greater Than…

 

 

• The Greater Than Window should appear. Enter 7 in the textbox underneath the Format cells that are GREATER THAN: section and leave the format as Light Red Fill with Dark Red Text.

 

 

• Click Ok.
• All the cells that contain a value greater than 7 should now be highlighted with the Light Red Fill with Dark Red Text.

 

If you would like to see another example of conditional formatting, check out our article about Excel heat maps.

Conditional Formatting – Using Preset Conditions

You can use predefined presets in Excel to format your data. Excel has three useful presets – Data Bars, Colour Scales and Icon Sets.

We will see how each one works.

Data Bars

Data bars are horizontal bars added to each cell in a selected range, in order to reflect the magnitude of the values, comparatively.

In our source example, a business owner owns seven franchises and has recorded the sales data for the month of January.

 

 

• • So, for this example, select cell range B5:B11.

• • With this range selected go to the Styles group on the Home Tab and select Conditional Formatting.

• • Select Data Bars and then choose a fill option under either Gradient Fill or Solid Fill depending on your preference. In this case, we will select the Blue gradient fill.

 

 

• • You should see the following:

 

Colour Scales

Colour Scales use shades of colour in order to represent each value in the cell range comparatively. In the example below we have some sales data, which shows how much each salesperson sold over three months.

 

 

• • Select cell range B6:D11 and go to the Styles group on the Home Tab and select Conditional Formatting.

• • Choose Colour Scales and select the Green-Yellow-Red colour scale.

 

 

• • The data will now be formatted as shown below.

 

 

• • The higher values are filled with shades of green, with the highest value being a dark green. The middle values are shaded with yellow and the lowest values are filled with shades of red with the lowest value shaded with a dark red fill.

 

Icon Sets

Icon Sets are useful for quickly seeing key indicators or performance values. In our example, a supplier has recorded the number of orders received from different cities.

 

 

• • Select cell range B5:D9.

• • With this range selected go to the Styles group on the Home Tab and select Conditional Formatting.

• • Select Icon Sets and choose an Icon Set in this case we will select the three triangles under Directional.

 

 

The result is the following.

 

 

Conditional Formatting – Using Formulas for Custom Conditions

You can create your own formulas for custom conditions. In our example, we have a list of projects and the staff member responsible for the project recorded.

 

 

We want to see which staff members other than Stephnie Rollins and Candie Swimton are responsible for projects. So, we will need a custom condition.

 

• Select the cell range B5:B15.
• With this range selected, go to the Styles group on the Home Tab and select Conditional Formatting and choose New Rule…

 

 

• The New Formatting Rule Dialog Box should appear.

 

 

• Select the Use a formula to determine which cells to format option and enter the following formula:

 

 

Excel will start by looking at the value in cell B5 and then going down the rows in the selected range and only format those names that are not Stephnie Rollins or Candie Swimton.

 

 

• Click on the Format… button and you can choose any format that you’d like but in this case, we are going to use a light green fill.

 

 

• Click Ok. You should now see a preview of what the formatting will look like.

 

 

• Click Ok again. The staff other than Stephnie Rollins and Candie Swimton are highlighted.

 

 

Copy and Paste Conditional Formatting

You can copy and paste conditional formatting. Let’s see how this works in the following example.

We have a list of participants that were asked to evaluate two different dips produced by the same company.

Conditional formatting has already been applied to the onion flavoured dip ratings and highlights every rating that is greater than 5.

 

 

• Select cell B6 and press Ctrl-C on your keyboard.
• Select cell range C6:C17.

 

 

• Right-click any cell in the range that is selected, in this case we will right-click on cell C9 and then choose Paste Special…

 

 

• The Paste Special Dialog Box should appear.

 

 

• Instead of All select Formats and then click Ok.

 

6) Press the Esc key to get rid of the marquee around cell B6.

7) The conditional formatting has been copied and applied to the data range for the cheese flavoured dip.

We can now see all the ratings that were greater than five for this data set as well.

 

 

Applying more than one type of Conditional Formatting to a Range

You can apply more than one type of conditional formatting to a selected cell range. In our source example we have a speciality tea supplier recording the monthly tea sales data.

 

 

• We are going to apply conditional formatting to cell range B6:B12 that highlights all the cells that are less than 600.

 

• In order to do this, select the cell range B6:B12 and then go to the Styles group on the Home Tab and select Conditional Formatting.

 

• Select Highlight Cells Rules and then Less Than...

 

 

• Under the Format cells that are LESS THAN: section type 600 and choose Yellow Fill with Dark Yellow Text.

 

 

• The result is shown below.

 

 

• Now we are going to apply Data Bars so with cell range B6:B12 selected, go to the Styles group on the Home Tab and select Conditional Formatting.

 

• Select Data Bars and choose one of the fills.

 

 

• The result is shown below.

 

 

• You can manage the conditional formatting rules applied to this range.
• Select any cell in the worksheet and go to the Styles group on the Home Tab and select Conditional Formatting.
• Select Manage Rules…

 

 

• The Conditional Formatting Rules Manager Dialog Box should appear.

 

 

• Change the Show formatting rules for: option to This Worksheet as shown.

 

 

• As a result, you should see all the conditional formatting rules that have currently been applied in the worksheet.

 

 

• You can either create a new rule, edit an existing rule, or delete a rule.

 

In this case we are going to delete the Data Bar rule that we applied. So, select the Data Bar rule and click on the Delete Rule button.

 

 

• The Data Bar rule should now be deleted.

 

 

• Click Ok.
• You should see the following:

 

 

Conditional Formatting – Highlight a Whole Row Based on a Criterion

Let’s say you have a situation where you would like to highlight a whole row based on a certain criterion. In our example we have a list of pet owners and the type of pet that they own.

So based on what the user selects in the drop down list, in cell C4 we would like to see the entire row highlighted. The user has a choice of Dog, Hamster or Cat.

 

 

If you would like to learn more about how to create drop down lists in Excel then click here

• So, select cell range A7:C13 as shown.

 

 

• With this range selected go to the Styles group on the Home Tab and select Conditional Formatting and choose New Rule…
• Select the Use a formula to determine which cells to format option and enter the following formula:

 

 

 

We are using a combination of mixed and absolute cell references in this formula, we are looking at the value in cell C7 first and then want Excel to see if it is equal to the value in C4.

As we move down the rows, we want the formula to compare the value in C8 to C4 and so on.

That is why we have a mixed cell reference in terms of $C7 and an absolute reference in terms of cell C4.

To learn more about mixed and absolute cell references read this article.

 

• Click the Format button and in this case, we chose a pale yellow fill.
• Click Ok.
• Click Ok again.
• Now if the user selects Dog from the drop down list in cell C4, the whole row of each dog owner will be highlighted.

 

 

• Likewise, if the user selects Hamster from the drop down list in cell C4 then the row of each hamster owner will be highlighted.

 

 

How to Clear Conditional Formatting from Cells

If you would like to clear the conditional formatting from your cells or worksheet. You can do so in the following way.

 

• Select the cell range containing the conditional formatting which in this case is range B5:B16.

 

 

• With this range selected go to the Styles group on the Home Tab and select Conditional Formatting. Choose Clear Rules and then Clear Rules from Selected Cells.

 

 

• The conditional formatting should now be cleared from the selected cells.

 

 

Troubleshooting Your Conditional Formatting

You may encounter situations where your conditional formatting is not working for some reason. You can fix some of the typical problems, by following these steps.

 

Use the Conditional Formatting Rules Manager

You may be encountering an error because you have not formatted the correct range of interest. You can use the Conditional Formatting Rules Manager to check that the conditional formatting applies to the correct range.

Select any cell in the workbook and go to the Styles group on the Home Tab and select Conditional Formatting.

Select Manage Rules…

Choose Show Formatting rules for: This Worksheet and then check the applied range for the rule.

If it is not correct you can re-select it.

Make sure you used references correctly in your formulas

You may have created a custom formula for your conditional formatting that used either mixed or absolute references or both.

You can use the Conditional Formatting Rules Manager to check your formula by selecting the rule of interest and clicking on the Edit Rule: button.

Check your formula for referencing errors and correct it from here.

Click Ok and then Apply and then Ok again.

Look at each component of complex formulas individually

If you have used a complex custom formula, then try breaking it up into simple parts or use a helper column in your worksheet in order to check the formula.

In order to learn more about auditing formulas in your actual worksheet please read this post.

 

Learning Objectives

Let’s review the skills you have learned.

• You know what conditional formatting is.
• You know how to apply simple conditional formatting in your workbooks.
• You know how to use the preset conditions.
• You know how to use a formula in your conditional formatting.
• You know how to Copy and Paste conditional formatting.
• You can apply more than one type of conditional formatting to a range.
• You can highlight a whole row based on a specific criterion.
• You know the basic steps for troubleshooting your conditional formatting.

 

Conclusion

Excel’s conditional formatting options are powerful and allow you to format data according to simple criteria and to use your own formulas for more advanced criteria.

You will often see conditional formatting applied in advanced dashboards and reports.

Casual Excel users and professional Excel users can make use of the conditional formatting options that Excel provides, and it’s available on Excel for Web as well for easy collaboration with colleagues.

One other case where conditional formatting can be used is when looking for outliers in a dataset. If you would like to know more about outliers, have a look at our article about finding outliers in Excel.

Special thank you to Taryn Nefdt for collaborating on this article!