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.

Conditional formatting In Excel

 

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.

 

Shows conditional formatting being applied in order to highlight certain data

 

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.

 

Showing the source data

 

  • Select cells B5:B20.

 

Screenshow of the cell range 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).

 

Screenshot showing the Conditional Formatting Option in the Styles group on the Home Tab

 

  • Select Highlight Cells Rules and then Greater Than…

 

Shows the Highlight Cells Rules and Greather Than.. options highlighted

 

  • 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.

 

Screenshot showing the Greater Than Window with the Format cells that are GREATER THAN: section highlighted as well as the format option

 

  • 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.

 

Screenshot showing all the values greater than 7 highlighted through conditional formatting being applied

 

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.

 

Screenshot showing the source data for Data Bars conditional formatting example

 

    • 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.

 

Screenshot of the Data Bars option highlighted and the chosen Gradient Fill option highlighted

 

    • You should see the following:

 

Screenshot of the Data Bars applied to the source data range

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.

 

Screenshot of the source data for the Colour Scales conditional formatting example

 

    • 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.

 

Screenshot showing the Colour Scales and the Green Yellow Red Colour Scale highlighted

 

    • The data will now be formatted as shown below.

 

Shows the Colour scales applied across the data range

 

    • 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.

 

Screenshot showing the source data for the Icon Sets conditional formatting example.

 

    • 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.

 

Screenshot showing Icon Sets and the three triangles under Directional highlighted.

 

The result is the following.

 

Screenshot showing the source data with Icon Sets applied.

 

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.

 

Screenshot showing the source data for the custom condition conditional formatting example.

 

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…

 

Screenshot showing the Conditional Formatting and New Rule... option highlighted.

 

  • The New Formatting Rule Dialog Box should appear.

 

Screenshot showing the New Formatting Rule Dialog Box.

 

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

 

=NOT(OR(B5=”Stephnie Rollins”,B5=”Candie Swimton”))

 

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.

 

Screenshot showing the Use a formula to determine which cells to format option highlighted and the formula itself highlighted.

 

  • 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.

 

Screenshot showing the Fill Tab with light green chosen.

 

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

 

Screenshot showing the preview of what the conditional formatting will look like highlighted.

 

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

 

Screenshot showing all the staff that are not Stephnie Rollins or Candie Swimton 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.

 

Screenshot showing the data range that already has conditional formatting applied.

 

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

 

Screenshot showing range C6:C17 selected.

 

  • 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…

 

Screenshot showing the Paste Special...option highlighted.

 

  • The Paste Special Dialog Box should appear.

 

Screenshot showing the Paste Special Dialog Box.

 

  • 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.

 

Screenshot showing the conditional formatting that has been copied and pasted to the data in range C6:C17.

 

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.

 

Screenshot showing the source data for the applying multiple conditional formatting example.

 

  • 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...

 

Screenshot showing the Highlight Cells Rules and the Less Than...Option selected.

 

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

 

Screenshot showing the Less Than Dialog Box.

 

  • The result is shown below.

 

Screenshot showing all the values less than 600 highlighted.

 

  • 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.

 

Screenshot showing the Light Blue Gradient Fill Data Bars option highlighted.

 

  • The result is shown below.

 

Screenshot showing the Less Than conditional formatting and Data Bars applied to the same cell range.

 

  • 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…

 

Screenshot showing the Manage Rules...option highlighted.

 

  • The Conditional Formatting Rules Manager Dialog Box should appear.

 

Screenshot showing the Conditional Formatting Rules Manager Dialog Box.

 

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

 

Screenshot showing the Show formatting rules for: option and This Worksheet highlighted.

 

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

 

Screenshot showing the Conditional Formatting Rules 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.

 

Screenshot showing the Data Bar Rule and the Delete Rule button highlighted.

 

  • The Data Bar rule should now be deleted.

 

Screenshot showing the Data Bar rule deleted.

 

  • Click Ok.
  • You should see the following:

 

Screenshot showing the cell range B6:B12 with only the Less Than Conditional Formatting applied.

 

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.

 

Screenshot showing the source data and the drop down list.

 

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.

 

Screenshot showing cell range A7:C13 selected.

 

  • 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:

 

=$C7=$C$4

 

Screenshot showing the formula for the conditional formatting that has mixed and absolute cells references, highlighted.

 

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.

 

Screenshot showing the whole row highlighted for each dog owner.

 

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

 

Screenshot showing the rows of all the hamster owners 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.

 

Screenshot showing the source data for the Clear Conditional Formatting example.

 

  • 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.

 

Screenshot showing the Clear Rules and the Clear Rules from Selected Cells option highlighted.

 

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

 

Screenshot showing the conditional formatting 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.

Screenshot showing how to use the Conditional Formatting Rules Manager to verify ranges.

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.

Screenshot showing the Edit Rule... button highlighted.

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

Screenshot showing the Edit Formatting Rule Dialog Box.

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.

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!

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.