callierosalind - Tumblr blog

callierosalind · 7 years

Text

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.

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

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.

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

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.

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.

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.

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.

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.

Heat Map after values are Hidden

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.

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”

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

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

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.

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

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’

LET’S GO!

The post How to Use Conditional Formatting in Microsoft Excel 2016 appeared first on Earn and Excel.

How to Use Conditional Formatting in Microsoft Excel 2016 syndicated from http://www.earnandexcel.com/

0 notes