Conditional Formatting in Excel

Publication Date :

20 Dec, 2021

Blog Author :

Edited by :

Reviewed by :

Download FREE Conditional Formatting Excel Template and Follow Along!

Conditional Formatting Excel Template.xlsx

Table Of Contents

What Is Conditional Formatting In Excel?

Conditional formatting in excel helps format a range of cells based on the fulfillment of one or more conditions. The formatting to be applied can either be selected from the various options given in Excel or created from scratch by the user. Conditional formatting works on the rule “if this is the case, then that should be done.”

For example, if the numerical values in the range B2:B10 are less than 100, color the cells blue. The cells (of range B2:B10), which meet the specified condition (less than 100), will be colored blue.

For creating a conditional formatting rule, one should specify the following clauses:

Range clause: This represents a cell or range of cells on which conditional formatting has to be applied. It can be a named range or a selection.
Condition clause: This represents a condition (or a criterion) that needs to be satisfied by the conditional formatting rule. The condition applied can be single or multiple. But, every condition should necessarily have an “if” and “then” clause.

If the condition (in pointer “b”) is met, formatting is applied to the cell range (in pointer “a”). If not, no formatting is applied.

The purpose of applying a conditional formatting rule in excel is to analyze data based on the visualizations created. Moreover, it helps highlight the variations in data values, spot significant issues, and discover trends and patterns.

Key Takeaways

Conditional formatting in excel is used to format a range of cells based on the fulfillment of one or more conditions.
The option, conditional formatting is available under the Styles group in the Home Tab.
This function helps us highlight important information in our worksheet.
Conditional formatting is similar to that of cell formatting but the only difference is that the formatted cells automatically show the conditional formatting.
We can apply conditional formatting to one or more cells in the worksheet.

What Does The Conditional Formatting Feature Do In Excel?

The conditional formatting excel feature changes the appearance of a cell by changing its fill color, border, font color, and so on. With such changes, certain data cells can be distinguished from the others. This feature is available in the “styles” group of the Home tab.

A conditional formatting rule in excel can fulfill a variety of conditions. Some of these are listed as follows:

Format values greater than, less than, between, equal to
Format values above or below the average
Highlight the top or bottom 10 items
Highlight the top or bottom 10%
Highlight the duplicate or unique items
Format a date falling within a certain period
Format text containing certain words or characters
Format cells using a conditional formatting formula

This list is not exhaustive. One can explore more conditions or create a new conditional formatting rule in Excel.

Note: Conditional formatting is different from simple formatting in Excel. This is because the application of the former requires a condition to be met, while the latter is not condition-based.

How To Apply Conditional Formatting In Excel?

We can use the conditional formatting function in excel using the below steps:

First, select the cells we want to format.
Next, go to Home Tab.
Then, select the Conditional Formatting option under the Styles group.
Finally, choose the required condition from the list of options found in the Conditional Formatting drop down menu.

How to apply conditional formatting in excel

Let us consider some examples to understand the working of conditional formatting in Excel.

You can download the conditional formatting template here – Conditional Formatting Excel Template.

Examples

Example #1 – Apply the “Equal To” Conditional Formatting Rule To A Single Numeric Cell

The following image shows a numerical value in cell A1. We want to color the given cell (range clause) red if the value it contains is equal to 1 (condition clause).

conditional formatting 1

The steps to apply conditional formatting in excel to the given cell (A1) are listed as follows:

To begin with, select cell A1. Click the “conditional formatting” drop-down from the “styles” group of the Home tab. Choose “new rule,” as shown in the following image.
The “new formatting rule” window opens, as shown in the following image. Under “select a rule type,” choose the option “use a formula to determine which cells to format.”
Next, click inside the box under “format values where this formula is true.” Select cell A1 on which conditional formatting should be applied. The cell reference “=$A$1” appears in this box, as shown in the following image.

Alternatively, one can type the absolute reference “=$A$1” in the box displayed within the red rectangle.
Complete the formula entered in the preceding step. The entire formula is “=$A$1=1” (exclude the beginning and ending double quotation marks).
Click “format,” shown in a red box in the following image.
The “format cells” dialog box opens, as shown in the following image. Click the “fill” tab.
Select the desired color. We have selected red. Click “Ok.”
Check the preview in the box to the right of “preview.” The formatting of cell A1 will match the preview displayed in this step.

If the preview is fine, click “Ok” in the “new formatting rule” dialog box.

Note: If the preview is not satisfactory, one can make changes by clicking the “format” button again.
Once “Ok” is clicked in the preceding step, the “new formatting rule” window closes. The output appears, as shown in the following image. Cell A1 has been colored red. This is because this cell meets the stated condition, which was, “if the value is 1, color the cell red.”

Hence, it has been proved that the value of cell A1 does equal to 1. Such validations are quite helpful when the dataset is large and one wants to know which cell contains what value.

Example #2 – Apply The “Greater Than” Conditional Formatting Rule To A Numeric Range

The succeeding image shows certain numerical values in the range A5:A19. We want to highlight the values that are greater than 30 (condition clause) in the given range (range clause).

The steps for applying the given conditional formatting rule in excel to the stated range are listed as follows:

Step 1: To start with, select the range A5:A19 on which conditional formatting is to be applied. Subsequently, from the “conditional formatting” drop-down (in the Home tab), choose “highlight cells rules.” Next, select “greater than.”

Step 2: Next, the “greater than” window opens, as shown in the following image. Under “format cells that are greater than,” enter 30. This is because the values greater than 30 are to be highlighted.

Select the desired color in the box to the right of “with.” We have selected “light red fill with dark red text.” Click “Ok” to proceed.

Step 3: The output is shown in the following image. Hence, the values in the range A5:A19 that are greater than 30 have been highlighted.

In total, 8 cells contain values greater than 30. Since all these cells are appearing in a different color, one can easily distinguish them from the other cells of the dataset.

Example #3 – Apply The “Duplicate Values” Conditional Formatting Rule To A Numeric Range

Working on the dataset of example #1, we want to highlight the duplicate values (condition clause) in the range A5:A19 (range clause). Use the conditional formatting technique of Excel.

The steps to highlight duplicates in the given range are listed as follows:

Step 1: First, from the “conditional formatting” drop-down in the Home tab, select “highlight cells rules.” Choose the option “duplicate values,” as shown in the following image.

Step 2: The “duplicate values” window appears. In the first box to the left, select “duplicate.” In the box to the right, select the required color. We have chosen “light red fill with dark red text.”

Next, click “Ok.”

Step 3: Next, the output is shown in the following image. The duplicate values in the range A5:A19 have been highlighted in the chosen color. Hence, the duplicates can be easily distinguished from the unique values.

As the next step, one can decide whether to retain or delete the single duplicate value of the dataset. Moreover, had the dataset been much larger than the present one, identifying duplicates by mere observation would have been difficult. So, in such cases, conditional formatting would have helped the user know which cells contain duplicate values.

Example #4 – Apply “A Date Occurring” Conditional Formatting Rule To A Date Range

The following image shows some random dates of the year 2018. We want to highlight those dates in the range E4:E19 (range clause), which pertain to the current week (condition clause). Use the conditional formatting tool of Excel.

Note that this article was created in the last week of October 2018. This covers the period from October 28 to November 3 of 2018.

The steps to apply the given conditional formatting to the date range in excel are listed as follows:

Step 1: To start with, select the range E4:E19 containing dates. From the Home tab, click the “conditional formatting” drop-down.
Next, choose “highlight cells rules” and click the option “a date occurring.” This is shown in the following image.

Step 2: Then, a new window titled “a date occurring” opens. Several options related to dates can be seen in the box to the left. These options are shown within a red box in the following image.

Step 3: Then, from the box on the left, select “this week.” In the box to the right of “with,” we have chosen the color “light red fill with dark red text.” Now, click “Ok.”

Step 4: The output is shown in the following image. The dates falling in the current week (10/28/2018 to 11/03/2018) are highlighted. A total of four dates relate to this period.

Remember that once “this week” is selected, Excel automatically takes into account the dates of the present week. So, after selecting “this week,” had we been in the last week of December 2021, Excel would have considered this period for applying the given conditional formatting rule.

Example #5 – Apply Multiple Conditional Formatting Rules To A Single Numeric Range

The following image shows some dates and numbers in columns G and H respectively. We want to apply multiple conditional formatting rules to column H (range clause), which are listed as follows (two conditional clauses):

Highlight the numbers lying between 31 and 43 (both inclusive).
Highlight the numbers less than 0.

Use the conditional formatting feature of Excel.

Step 1: To begin with, select the range H5:H20. From the “conditional formatting” drop-down, choose “highlight cells rules.” Next, click “between.”

Note: Ignore the alignment of the label “count,” which has changed from “left” to “center” in the following image. This is due to the different versions of Excel being used to create images.

Conditional Formating - Highlight Cell Rules - Between

Step 2: The “between” dialog box opens. Enter the numbers 31 and 43 in the first and second boxes respectively. These are shown in a red box in the following image.

Step 3: Select the desired color for highlighting the cell values. We have chosen “yellow fill with dark yellow text.” Next, click “Ok.”

Step 4: The output is shown in the following image. The numbers of column H, which are between 31 and 43, have been highlighted.

Had there been numbers 31 and 43 in column H, they too would have been highlighted. This is because both the upper and lower limits are included in the “between” condition.

Step 5: For applying the second condition, select the formatted range H5:H20. Since we want to apply multiple conditional formatting rules to the same range, ensure that the output (H5:H20 to which formatting has been applied) of the preceding step is selected.

Next, from the “conditional formatting” drop-down, choose “highlight cells rules.” Click “less than” and the succeeding window opens.

In the box to the left, enter 0. In the box to the right, select the desired color. We have chosen “light red fill with dark red text.” Click “Ok.”

Note: It is recommended to choose different colors while applying multiple conditional formatting rules to the same excel range. This helps distinguish between the different outcomes.

Step 6: Finally, the output is shown in the following image. The negative numbers in column H have been highlighted in light red color.

Now, column H shows outcomes based on two criteria. The result of the first conditional formatting rule (between 31 and 43) is in yellow, while the result of the second one (less than 0) is in light red. In this example, the two criteria (or conditions) were not overlapping. This is the reason it is easy to compare the two outputs.

In overlapping criteria, the first condition may be to highlight numbers lying between 31 and 43. The second condition may be to highlight numbers equal to 42. In such cases, comparison between the outcomes of the two conditions may become difficult.

Note: For changing priorities of multiple conditional formatting rules, refer to the second question under the heading “frequently asked questions.” This section is given at the end of this article.

Example #6 – Apply “Data Bars,” “Color Scales,” And “Icon Sets” Formatting Methods To Multiple Numeric Ranges

In the subsequent images, the values of the “count” column (of example #4) have been pasted in three different columns titled “count1,” “count2,” and “count3.” Format the ranges L6:L21, K6:K21, and M6:M21 by using data bars, color scales, and icon sets respectively.

Show only the usage of the stated formatting methods without creating a new conditional formatting rule. There are three range clauses (three given ranges) in this example but no conditional clause.

The steps for using the stated formatting methods are listed as follows:

Step 1: Select the range L6:L21. From the “conditional formatting” drop-down, choose “data bars.” Next, select the desired color theme. We have chosen “blue data bar” from “gradient fill.”

The selection is shown in the following image. Notice that while selecting the color theme, Excel displays a preview (on the selected range) in the background.

Note: Data bars, color scales, and icon sets are the different conditional formatting techniques in excel that visually enhance the appearance of a dataset. In these techniques, the bars, shapes, color patterns, arrows, etc., are used to represent data.

Step 2: Select the range K6:K21. From the “conditional formatting” drop-down, choose “color scales.” We have selected the “green-yellow-red” color scale.

One can observe the selected theme in the background of the following image.

Step 3: Select the range M6:M21. From the “conditional formatting” drop-down, choose “icon sets.” Select the desired theme. We have selected “3 arrows (colored)” from the “directional” icon sets.

The selection is shown in the following image.

Step 4: The final output is shown in the following image. Notice how formatting has changed the appearance of the given dataset.

In all three ranges, the negative numbers have been colored differently. This helps distinguish between negative and positive numbers. For instance, -12 has been colored red (in the range K6:K21) by the color scale formatting method. With icon sets formatting, this number has been allotted a red, downward arrow (in the range M6:M21).

With the data bars formatting technique, the negative numbers (of range L6:L21) have leftward bars, while positive numbers have rightward bars. Moreover, bigger numbers have longer bars, while smaller numbers have shorter bars.

Example #7 – Apply The “Equal To” Conditional Formatting Rule By Using A VBA Code

It is possible to apply a conditional formatting rule by using a VBA code . For instance, with the condition “A1=1,” a VBA code is written as follows:

Sub Example()
ThisWorkbook.Worksheets(1).Range("A1").Select
With ThisWorkbook.Worksheets(1).Range("B1")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=A1=1"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End Sub

Important Things To Note

Conditional formatting in excel formats a range of cells based on one or more conditions.
We can use this option under the Styles group in the Home Tab.
Using conditional formatting, we can apply multiple rules to a same cell.
We can also copy the conditional formatting using format painter.

Frequently Asked Questions (FAQs)

What is conditional formatting and when is it used in Excel?

In case of multiple conditional formatting rules, which rule assumes priority in Excel? State the steps to change the precedence (priority) of the conditional formatting rules.