How to Use Conditional Formatting in Microsoft Excel 2016

Introduction

Conditional Formatting is a powerful tool and can change how a cell appears, based on the cell’s value. It enables you to identify critical data at a glance. Colors, icons, data bars and color scales can be added to the cell by creating a conditional formatting rule.

Examples of Conditional Formatting

  • check
    Highlight Duplicate Values in Red color
  • check
    Using Data Bars to create progress bars
  • check
    Using Top N Rule to highlight Top 3 products
  • check
    Using Color Scales to generate a Heat Map
  • check
    Using Icon Sets to Identify Missing Data
  • check
    Clearing Rules

Highlight Duplicate Values in Red Color

​Conditional Formatting can be used to highlight duplicate values so that it is easier to identify and correct errors in the data.

highligh-duplicate

​Steps

​1.    Select the cells to format. In this example, it is A2:A9
2.    Go to Home → Conditional Formatting
3.    Click on Highlight Cells Rules → Duplicate Values
4.    Click ok. You can also choose other cell colors from the dropdown list besides the                  default one

Conditional Formate
duplicate-value
1.    Select the cells to format. In this example, it is A2:A9
2.    Go to Home → Conditional Formatting
3.    Click on Highlight Cells Rules → Duplicate Values
4.    Click ok. You can also choose other cell colors from the dropdown list besides the default one.

Using a data bar to create a progress bar

Data Bars can be used to represent data graphically inside a cell. The longest bar represents the highest value and shorter bars represent lower values. Data bars can help you spot large and small numbers easily in your spreadsheets.

For this example, if you want to see how the projects are progressing, you can create data bars to see the progress of all the projects on hand at a glance.

progress data

Steps

1. Select the cells to format. In this example, it is B2:B6
2. Go to Home → Conditional Formatting
3. Click on Data Bars → Blue Data Bar
4. You can also choose other color bars in the selection

blue data bar

Using Top/Bottom Rules to highlight Top 3 products

Another great way to use conditional formatting is to select the Top/Bottom Rules option. You can easily highlight the top 3 items in a list. To identify the top three products that makes the most sales in the store, you can create a Top three rule in Conditional Formatting.

Highlight top 3
Top Bottom Rules

Steps

1. Select the cells to format. In this example, it is B2:B8
2. Go to Home → Conditional Formatting
3. Click on Top/Bottom Rules → Top 10 Items…
4. 4.The default is 10 items. You can either type in the number 3 or use the add/reduce button.
5. 5.You can also choose other cell colors from the drop-down list besides the default one.

Top 10 items

Using Color Scales to generate a Heat Map

What is a Heat Map?

A Heat map is a table where the data in the spreadsheet are visualized using color. Sometimes values in the cells are shown and sometimes it isn’t shown. The values can be hidden using cell formats.

June Sales

Steps

1. Select the cells to format. In this example, it is B2:D10
2. Go to Home → Conditional Formatting
3. Click on Color Scales → More Rules…
4. The default is 2-color Scale. You can select 3-color scale from the drop-down list.
5. For this example, light blue, medium blue and dark blue is selected. When selecting colors for the heatmap, it is good practice to select the light/Medium/Dark tone of the same color. It is because selecting different colors may confuse the viewer and it is difficult to identify which cells actually has lower/higher values. However, by selecting the same color tone, the viewer is able to quickly recognize the highest amount and lowest amount immediately.

Color Scales
3 color scales

Additionally, if you want to hide the values in the table and only show the colors in the heat map, you can right-click on the spreadsheet → Click on Format cells → Click on Custom → Enter “;;;” in the type box and click on ok. The values are still in the table but is now hidden from view.

Custom Function

Heat Map after values are Hidden

June Sales

Using Icon Sets to Identify Missing Data in Timesheet

It is a common opinion that conditional formatting can only be used to format cells based on their own values. However, there is a way to get around this. You can use a formula to get the values based on another cell or other cells in the same row and then use conditional formatting to get the desired result.

We will demonstrate this in this example. For this example, we have added a formula in E4 to count the number of non-blank cells from A5 to D5. As all the necessary data has been filled up in row 5, the formula shows 4 non-blank cells as the result. However, in row 7, there is only one cell with data and three more blank cells to fill in. Thus, this row needs to be highlighted. We will use icon sets to identify the row that needs correcting.

Linda

Steps

1. Select the cells to format. In this example, it is E5:E9
2. Go to Home → Conditional Formatting
3. Click on Icon Sets → More Rules…
4. The default is 3 Traffic lights (Unrimmed). Go ahead and select 3 symbols (Circled) from the drop-down list.
5. Tick the “Show Icon only” checkbox
6. Click on the last icon and change “Red Cross Symbol” to “No Cell Icon”
7. Click on the 2nd Icon, change “Yellow Exclamation Symbol” to “Red Cross Symbol”, change the value to 1 and change the Type from “Percent” to “Number”
8. For the 1st icon which is the “Green Check Symbol”, change the value to 4 and change the Type from “Percent” to “Number”

icon sets
Edit Formatting Tools
linda operation

After completion of the above steps, if there is uncompleted data in any cells in column A-D of the same row, there will be a cross in column E. This will allow the user to quickly identify the incomplete data and rectify it. When all four cells from column A-D are entered in the same row, the green check will appear. This will allow the user to know that all the data has been entered and there are no more incomplete data.

Clearing Rules

Identifying cells that contain conditional formatting

Before you clear any conditional formats, you may want to identify the cells that contain conditional formats first. To do this, select all the cells on the spreadsheet using CTRL + A. Click on Special → Conditional formats → click on ok

Go To
Go To Special dialog box
linda operation

This will show you the highlighted cells from E5 to E9 which contains conditional formatting.

Clearing cells that contain conditional formatting

Steps

1. Go to Home → Conditional Formatting
2. Click on Clear Rules → Clear Rules from Selected Cells

Clear rules

Clearing multiple rules in the same worksheet

Alternatively, there is also another way to clear the conditional formatting. This method will display all cells with conditional formatting on the same worksheet and allow you to delete multiple rules easily.

Steps

1. Go to Home → Conditional Formatting
2. Click on Manage Rules
3. Change Show formatting rules for: “Current Selection” to “This Worksheet”. This will display all the formatting rules for this worksheet.
4. Click on the rule that you want to delete → click on delete rule → OK
5. Repeat Step 4 for rules that you want to delete.

Manage Rules
Delete Rules


Data Bars can be used to represent data graphically inside a cell. The longest bar represents the highest value and shorter bars represent lower values. Data bars can help you spot large and small numbers easily in your spreadsheets.
For this example, if you want to see how the projects are progressing, you can create data bars to see the progress of all the projects on hand at a glance.

Using a data bar to create a progress bar
Conditional Formatting can be used to highlight duplicate values so that it is easier to identify and correct errors in the data.
Examples of Conditional Formatting
Examples of Conditional Formatting

If you enjoyed this article then sign up for the report and newsletter below. Alternatively, if you want to master Excel then sign up to one of our Excel courses for professionals

Before you go, get my report '10 things I did with Excel that increased my salary from £27K to £100K'

    Sohail Anwar

    Click Here to Leave a Comment Below
    How to Use Conditional Formatting in Microsoft Excel 2016 – Earn and Excel - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    How to Use Conditional Formatting in Microsoft Excel 2016 – chambers Dixon - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    How to Use Conditional Formatting in Microsoft Excel 2016 | Jeannine Clarice - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    How to Use Conditional Formatting in Microsoft Excel 2016 | Barbra Lily - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    How to Use Conditional Formatting in Microsoft Excel 2016 – Callie Rosalind - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    How to Use Conditional Formatting in Microsoft Excel 2016 – Ratliff Diaz - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    How to Use Conditional Formatting in Microsoft Excel 2016 | Cathy valerie - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    How to Use Conditional Formatting in Microsoft Excel 2016 – bouchard Graccer - September 5, 2017 Reply

    […] post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and […]

    Leave a Comment: