How do you explain conditional formatting?

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

Nội dung chính Show

• What does the Conditional Formatting Feature do in Excel?
• How to Apply Conditional Formatting in Excel?
• Frequently Asked Questions
• Recommended Articles
• How do you use conditional formatting?
• Which statement best describes conditional formatting?

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:

1. 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.
2. 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.

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 formulaConditional formatting using Formula is just changing the format of cells based on the user's condition or criteria. The outcome will be either TRUE or FALSE if all of the formulas are logical. read more

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 ExcelFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more. 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?

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

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

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

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

   
   

2. 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.”
   

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

   
   

4. Complete the formula entered in the preceding step. The entire formula is “=$A$1=1” (exclude the beginning and ending double quotation marks).
   

5. Click “format,” shown in a red box in the following image.

   
   

6. The “format cells” dialog box opens, as shown in the following image. Click the “fill” tab.

   
   

7. Select the desired color. We have selected red. Click “Ok.”

   
   

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

   
   

9. 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: 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: 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 valuesHighlight Cells Rule, which is available under Conditional Formatting under the Home menu tab, can be used to highlight duplicate values in the selected dataset, whether it is a column or row of a table.read more (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: 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: 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: Select the range E4:E19 containing dates. From the Home tab, click the “conditional formatting” drop-down. Choose “highlight cells rules” and click the option “a date occurring.” This is shown in the following image.

Step 2: 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: 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.” 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: 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.

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: 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 barsData bars are a type of conditional formatting option in Excel that are used to highlight cells or data ranges in a worksheet based on certain criteria.read more, color scales, and icon setsExcel icon sets are part of the conditional formatting graphics for numerical data sets. They help design the numbers more beautifully Directional, Shapes, Indicator, and Ratings are the many types of icon sets. read more 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 Conditional formatting is also possible in Excel VBA programming by using the ‘Format Conditions Collection' macro/procedure. A conditional format is one that is applied only to cells that meet certain criteria, such as values above a certain threshold, positive or negative values, or values with a specific formula, and so on.read more. For instance, with the condition “A1=1,” a VBA code is written as follows:

Sub Example()
ThisWorkbook.Worksheets(1).Range(“A1”).Select
With ThisWorkbookVBA ThisWorkbook refers to the workbook on which the users currently write the code to execute all of the tasks in the current workbook. In this, it doesn't matter which workbook is active and only requires the reference to the workbook, where the users write the code.read more.Worksheets(1).Range(“B1”)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:=”=A1=1″
.FormatConditions(1).Interior.ColorIndex = 46
End With
End Sub

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

Frequently Asked Questions

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

Conditional formatting in excel helps apply a specified format to one or more cells subject to the fulfillment of a condition. Prior to applying a conditional formatting rule, the cell or cell range needs to be selected. Conditional formatting can be applied to a row, column, table, or PivotTable.

Conditional formatting eases comparisons across data values and thereby facilitates data analysis. The conditional formatting feature is used in the following situations:

a. When there is a need to distinguish some cells from the others
b. When there is a need to highlight the important information of a worksheet

Note: For details related to the working of the conditional formatting feature, refer to the examples of this article.

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

It is possible to apply as many conditional formatting rules to one range, as required. However, in such cases, the rule at the topmost position in the “conditional formatting rules manager” assumes the highest priority. The lower the rule in this window, the lower is its importance (priority).

The steps to change the precedence of the conditional formatting rules in excel are listed as follows:

a. Select the range on which multiple conditional formatting rules have been applied.
b. In the Home tab, click the “conditional formatting” drop-down from the “styles” group. Next, click “manage rules.”
c. The “conditional formatting rules manager” window opens. Select a rule by clicking it once.
d. Change the importance of the rules by moving them up or down with the “move up” or “move down” buttons. These buttons are in the form of upward and downward arrows displayed to the right of the “delete rule” button.
e. Select the “stop if true” checkbox, if required. When this check box is selected, Excel stops evaluation at that rule, if it evaluates to true. For instance, there are three rules and the “stop if true” checkbox is checked for the first rule. Then, either of the following outcomes can be obtained:
• If a particular cell within the selected range meets the first rule, the formatting set for this rule is applied. The remaining two rules are not evaluated for this cell.
• If a cell within the selected range does not meet the first rule, the other two rules are also evaluated and accordingly, formatting is applied.

The importance of the multiple conditional formatting rules (applied to the selected range) is changed.

3. How to copy and delete the conditional formatting rules in Excel?

The steps to copy the conditional formatting rule from one range to the other are listed as follows:

a. Select any cell of the first range to which a conditional formatting rule has been applied.
b. From the Home tab, click “format painter.” The mouse pointer changes to a paintbrush icon.
c. Click the first cell of the new range where the conditional formatting rule has to be pasted. Drag the paintbrush icon to the last cell of this range. Release the mouse when done.

The conditional formatting rule is copied to the new range.

The steps to delete (or remove) the conditional formatting rules are listed as follows:

a. Select the range from which the conditional formatting rule has to be removed.
b. From the “conditional formatting” drop-down in the Home tab, choose “clear rules.” Select an option depending on whether the formatting rule has to be cleared from the selection, worksheet, table or PivotTable.

The conditional formatting rule is cleared depending on the selections in both the preceding pointers.

Note: Alternatively, after selecting the range, one can click “manage rules” from the “conditional formatting” drop-down. The “conditional formatting rules manager” window opens. Next, remove any of the applied rules by selecting the rule and clicking “delete rule.” Note that only one rule can be selected at a time.

Recommended Articles

This has been a guide to what is conditional formatting in Excel. Here we discuss how to use it along with formulas and examples. You may also look at these useful functions in Excel –

• How to Apply Conditional Formatting for Blank Cells?
• Conditional Formatting in the Pivot Table
• MS Excel Training Course Online