Conditional formatting in Excel
Conditional formatting is an especial feature (formatting feature) of Excel used to find unique and duplicates values by formatting the cells. This feature is available in various spreadsheet applications, and Excel is one of them.
As the name implies, conditional formatting allows the users to format the cells and their data based on some conditions specified by the user. Using conditional formatting, you can highlight a cell with a certain color and its content with different font.
Conditional formatting enables various features to the users to make the data more informatic and readable. It also allows you to format the cells and their data, which meet the specified criteria.
In this chapter, you will learn the several uses of conditional formatting and how it applies to an Excel worksheet to make the data more useful.
Topics covered in this chapter
The following list of topics, we are going to cover in this chapter –
- Features of conditional formatting
- Where the conditional formatting option is available?
- Conditional formatting Basics
- Pre-set conditional formatting
- Highlight the cells
- Apply data bars on data
- Apply color scales on data
- Clear conditional formatting
- Define Custom Conditions (new rules)
Features of conditional formatting
Excel enables several features of conditional formatting, such as –
- You can highlight the cells with a background color to make the data more valuable.
- You can also format the cell content with a special font.
- It also allows you to format the cells and their data, which meet the specified criteria.
- Excel allows to put conditions on data and format the cells based on following conditions, like greater than, less than, duplicate values, unique values, equal, if text containing specified data.
- You can set the rules to format the data and also clear the rules set by you either from selected cells or the entire sheet earlier.
- Conditional formatting enables you to do a number of things with data, like you can set the icons with the data, such as indicators, shapes, directional, etc.
- Conditional formatting makes the data more readable and informatic to the reader for the analysis purpose.
- For the analysis purpose of the spreadsheet, conditional formatting is a good choice to format the data.
Where conditional formatting option available?
It is very easy to find the conditional formatting option in Excel as it presents in the first Excel tab. It presents inside the Home tab, i.e., Home > Style > Conditional Formatting.
Under these conditional formatting options, you will get several conditions to apply and format the spreadsheet data. Choose wisely and use them according to your needs.
Conditional formatting basics
Before moving to apply conditions on an Excel spreadsheet, let’s understand the basic concepts of conditional formatting.
If-then Logic
Conditional formatting logic is based upon the if-then logic. It works in such an if-then manner to format the cells.
For example, X is a certain condition and Y is formatting to be applied on data. In such a scenario, if the X condition is satisfied (if TRUE), Y formatting will be applied to the data.
Hence, it can be written as X →Y. Simply, it means that if X is True, then Y is applied. All conditional formatting follows the same logic.
Pre-set conditions
Conditional formatting offers several pre-set conditions that a user usually needs, such as greater than, less than, duplicate values, unique values, etc. Hence, to save the time of users in writing formulas, Excel offers them some pre-set conditions.
Following are the pre-set conditions. You can use any of them from here.
Excel has vast library of pre-set conditions that a user usually wants to apply using functions.
Custom conditions
Often, pre-set conditions do not meet with criteria that you want to apply. Excel allows you to manipulate the pre-set conditions and define your own custom conditions. It means that you can create your rules as well to format the data.
From here, you can define custom conditions.
Applying multiple conditions
Sometimes, data requires multiple conditions to be applied to get the exact result that you want. Excel allows to apply multiple conditions on a single cell. Note that – be aware with the hierarchy and precedence to use them.
Conditional Formatting
In this chapter, we are going to discuss each possible pre-set condition in brief to give you an overview. By learning the basics of them, you can easily use them accordingly. When you navigate to the Conditional Formatting option in the Home tab, it enables several pre-set conditions.
- Highlight Cells Rules
- Top/Bottom Rules
- Data Bars
- Color Scales
- Icon Sets
Highlight Cells Rules
This conditional formatting rule allows the users to highlight the cells by using pre-set conditions that meet the specified criteria. It offers such conditions to the Excel users, such as Greater than, Less than, Between, Equal to, Text that contains, Duplicate Values.
These conditions are usually comparison operator conditions.
Top/Bottom Rules
This conditional formatting contains top/bottom rules to format the cell data by highlighting the cells. It is usually used to highlight the cells from the top or the bottom of the column, like top 10 cells or bottom 5 cells.
Top/Bottom rules contain such pre-set conditions, Top 10 items, Bottom 10 items, Top 10%, Bottom 10%, Above Average, and Below Average.
Data Bars
Data bars are the colored bars apply on Excel data to represent the value of a cell. These data bars are different than the above two conditional formatting. They are used to show the higher/low status of the data contained in the cells clearly.
“Higher the value, longer the bar. Similarly, Lower the value, shorter the bar“.
- Data bars are the graphical representation of data. Or you can say that graphical way to show the data.
- Data Bars help the Excel users to show the clear status on the based on overall data by highlighting the cells with a color according to the high and low value in the cell.
- Remember one thing that the data bars only apply to the numeric type of data. On String data, these are worthless.
- Conditional formatting offers several colors to use in Data bars representation.
With the help of an example, we will discuss you later how data bars actually work.
Color Scale
In conditional formatting, a color scale is applied to a range of cells. The color indicates where each cell value falls within the cell range. In Excel, conditional formatting offers twelve color scales to represent the data.
This color scale conditional formatting applies only to numeric values in an Excel worksheet.
Icon Sets
In Excel, you can represent the cells data through the different icon sets. Conditional formatting contains several types of icon sets, such as Directional, Shapes, Indicators, Ratings.
For example, by using a directional icon set, data can be represented in three ways high, low, and average value in a column.
These icon sets only attach with the numeric values in an Excel worksheet, not with String data.
Highlight the cells
We will take an example to highlight the cells by putting certain conditions on a column. For example, highlight all the cells that are having data greater than a value (specified by you for conditional formatting).
Follow the given steps:
Step 1: Select a range of cells in your Excel sheet, e.g., B2 to B8.
Step 2: Go to the Conditional Formatting present inside the Home tab and click on it.
Step 3: Hover the mouse to Highlight Cells Rules in dropdown list and then click on the Greater than condition rule.
Similarly, you can choose any other conditions from here and perform the operation accordingly.
Step 4: Here, specify a value with which you want to check all the selected values. We have entered 65 here.
Step 5: Also specify the color to highlight the greater values and press the OK button.
Step 6: Look in the below screenshot, all the values that are greater than 65 are highlighted with yellow color.
Step 7: Change the B5 cell value to greater than 500 and press the Enter key.
Step 8: See that the cell color is changed Yellow automatically, and the cell is highlighted.
Clear conditional formatting
In Excel, the users can clear all formatting applied to the cells in an Excel spreadsheet at once. Excel allows to clear the conditional formatting either from the entire worksheet or the selected cells (certain cells). See both methods below –
1. Clear formatting from entire worksheet
In the Home tab, go to Conditional formatting > Clear Rules > Clear Rules from Entire Worksheet.
It will clear all the pre-set and custom condition rules from the entire worksheet.
For example,
Step 1: Select all cells to clear the formatting from an entire worksheet and follow the step to clear the formatting rule for the entire worksheet.
Step 2: By clicking on the Clear Rules from entire worksheet, all formatting will be removed from the entire worksheet. Look at the screenshot below:
Here, you can see that the formatting is removed from the entire worksheet (all cells).
2. Clear formatting from selected cells
In addition, you can also clear the conditional formatting from the selected range of cells. Steps are almost the same.
In the Home tab, go to Conditional formatting > Clear Rules > Clear Rules from Selected Cells.
For example,
Step 1: Select the range from B2 to B7 but leave the last one, i.e., B8 and use the clear formatting rule for the selected cells.
Step 2: By clicking on the Clear Rules from Selected Cells, formatting will be removed only from the selected cells. See the screenshot below:
You can see that the formatting has been only removed from the selected cell, not from all.
Apply top/bottom rule and highlight the cells
Conditional formatting enables six pre-set top/bottom rules to highlight the cells. It allows the users to highlight the cells from the top or from the bottom of the column, like top 10 cells or bottom 5 cells, etc.
Top/Bottom rules contain such pre-set conditions, Top 10 items, Bottom 10 items, Top 10%, Bottom 10%, Above Average, and Below Average.
See the steps how top/bottom rules apply on a column:
Step 1: Select a column (range of cells) and go to the Conditional Formatting inside the Home tab.
Step 2: Click on this Conditional Formatting option and navigate to the Top/Bottom Rules in the list, then choose one of the Rule from here.
We have chosen Above Average to highlight the values which are above average.
Step 3: Set a color to format the color of the cell and click the OK button.
Step 4: See that all the values which are above the average of the column are highlighted.
Apply data bars on data
Conditional formatting consists of data bars. You can use these data bars on numeric data in a column to represent the value of the cells graphically. See the steps how could it be done:
Step 1: Select a column (range of cells) and go to the Conditional Formatting inside the Home tab.
Step 2: Click on this Conditional Formatting option and navigate to the Data Bars in the list, then choose any of the Data Bar from here.
Step 3: Now, you can see each cell of the column is represented by the bar.
“Higher the value, longer the bar. Similarly, Lower the value, shorter the bar“.
Apply color scale on data
Conditional formatting consists of several color scales. You can use these color scales only on numeric data in a column to represent the value of the cells graphically. The color indicates where each cell value falls within the cell range.
See the steps how could it be done and how data is represented:
Step 1: Select two or more columns (range of cells) containing numeric data and go to the Conditional Formatting inside the Home tab.
Step 2: Click on this Conditional Formatting option and navigate to the Color Scales in the list, then choose any of the Color Scales from here.
Step 3: Now, you can see that the numeric data of the selected column is represented by the color scale.
You can note that the color scale has been applied only on numeric data, not on strings.
Apply icon sets on data
In Excel, conditional formatting offers four types of icon sets, i.e., directional, shapes, indicators, and ratings. You can use these sets of icons on numeric data in a column to represent the cell values in a column or range of cells. We will describe you the directional icon set.
Following are the steps to use the icon sets with the cell data and represent them:
Step 1: Select the column (range of cells) containing numeric data and go to the Conditional Formatting inside the Home tab.
Step 2: Here, from the conditional formatting option list, click on the Icon Sets. When you click on it, you will get different icon sets; choose any of them whichever you want.
Step 3: Now, you will see that the icon set is attached with cell values and representing them with an arrow.
Define custom conditions
In Excel, conditional formatting offers pre-set conditions and also allows the users to define their custom rules/conditions. In the Home tab, go to the Conditional Formatting > New Rule.
From here, you can set custom rules. Let’s take an example to understand better.
For example, Row to row comparison of two columns and highlighting the cell if matches found.
Step 1: We have this dataset for comparison. Select a row, which you want to check whether the values are the same or not.
Here, we can easily compare for simple data only by seeing the data. But for complex data, it is not easy to match the values.
Step 2: Now, under the Home tab, go to Conditional Formatting > New Rule.
Step 3: Here, click on the Use a formula to determine which cell to format from the rule type list.
Step 4: Here, inside the formula field, specify the cells you want to compare in the following format, e.g., =$A2=$B2 for the second row.
Step 5: In the end, specify the format for matched cell by clicking on the Format button and see the preview of it inside the Preview box.
Step 6: Here, navigate to the Fill tab and choose a background color to highlight the matches and click the OK button.
Step 7: See the preview inside the preview section and see how row will look like if the match is found. After finalizing all the things, click the OK button to save the changes.
Step 8: You will see that the selected row has not been highlighted because the A2 and B2 cells do not contain the same values.
Step 9: By following the same steps, compare the next row data. See that everything is set up successfully. Now, click the OK button to get the result.
Step 10: Look at the following screenshot, that the 3rd row has been highlighted because it gets the same data in both columns.
Step 11: Similarly, we will check for all rows one-by-by. See the Excel worksheet after comparing both columns that row having the same data in both columns has been highlighted and remains are as it is.
It will highlight all the matching data row in the format you have chosen previously. Like this, we can define the custom conditions in conditional formatting.
