its a feature that enables you to highlight cells based on certain criteria. It helps you to focus on key information, visualize data, make worksheets easier to understand and keep you on top of your workload. It allows you to apply it like data bars, colors, icons, etc to one or more cells based on the cell value.

Each rule has two components:

     * A conditional expression that returns TRUE or FALSE.

     * A format that should be applied if the condition is met.

Conditonal Formatting In Excel can either use the built-in formatting rules available or create your own formula as per your requirement. Adding your own formula to a conditional formatting rule gives you the power and flexibility to apply exactly the formatting you need. You can use formatting to highlight cells meeting the conditions you specify. For example, you would want to highlight:

     * Dates that fall in the next week

     * Top 10 customers by sales

     * All duplicate entries

     * Scores that are above average, etc.

To understand how conditional formatting works, let’s consider a Basic Example. In the data below:

Conditonal Formatting In Excel 1

You have a list of details regarding various invoices. There are a few entries that are duplicate, and you want to highlight those invoice numbers. You can use the conditional formatting feature in excel to do this. Follow the steps below to get the desired output:

* Select the cells you want to format i.e. A2:A21

Conditonal Formatting In Excel 2

* Go to Home > Under Styles Group > Select Conditional Formatting

* Click Highlight Cells Rules > Duplicate Values

Conditonal Formatting In Excel 4

* In the dialog box, select the formatting style > Click OK

Conditonal Formatting In Excel 5

* The duplicate invoice numbers will be highlighted.

Conditonal Formatting In Excel 6

Just like highlighting duplicate values, there are various In-Built Conditional Formatting Functionality in Excel. Let’s look at each of those functionalities one by one:

Conditional Formatting In Excel Highlight Cells Rule

Greater than - You can use conditional formatting in Excel to quickly highlight cells that contain values greater than a specified value. Example - You can use Excel to highlight sales values that have exceeded the sales target specified by the manager or highlight costs that are lower than the budget specified. The steps to do this are as follows:

    * In the dialog box, type in the amount that should be the sales target and select the formatting.

     * Select the cells where you have the formatting

     * Go to the Home tab > Conditional Formatting > Highlight Cells Rule > Greater than

Conditonal Formatting In Excel 7

     
     * Click Ok

Note: You can follow the same steps if you want the condition to be either greater than, between, less than or equal to.

Note: If you want the condition to be “greater than or equal to” instead of just “greater than”, you can edit the existing condition. To do that, Select Manage Rule option from the Conditional Formatting dropdown. In the dialog box, click on “Edit Rule” and from the dropdown select “greater than or equal to”.

Conditonal Formatting In Excel 8

Text that contains- Click on Home > Conditional Formatting > Highlight Cells Rule > Text that contains. In the dialog box, type the text and select desired formatting.

Date Occurring- Click on Home > Conditional Formatting > Highlight Cells Rule > A Date Occurring. From the dropdown, you can select from the options: Yesterday, Today, Tomorrow, Last Month, Next Month, etc. Finally, you select the formatting from one of the pre-defined formats or set up your custom format. One of the limitations is that the conditional format is always applied based on the current date.


Top/Bottom Rules

Conditonal Formatting In Excel ,This rule is helpful to highlight/identify the top (or bottom) nth data in the set (by value or percentage). It can be used to identify top customers by sales amount or bottom candidate by his/her score. It can also be used to mark values that are above or below average. Note that this works only for cells with a numeric value.

Conditonal Formatting In Excel 9

You can follow the steps below to apply this conditional formatting rule:

* Select the range/dataset.

* Go to Home > Conditional Formatting > Top/Bottom Rules > Select appropriate rule _Top 10%, Bottom 10,       Above average, etc.)

* Based on the selection in step 2, a dialog box will open. Let’s say you have selected Top 10 Items from the dropdown, the dialog box will give you the option of changing the value of '10' to a different value and choosing the formatting to apply to cells satisfying the chosen criteria.

Conditonal Formatting In Excel 10

* Click Ok to get the result.

Conditonal Formatting In Excel Data Bars

Data Bars is simply a bar graph inside a cell and the length of the bar relates the value of the cell to other cell values in the selected range. A short bar means the value in that cell is low compared to the rest of the values in the range and a long bar means the value is high compared to the rest of the values in the range.

Note: Make sure that you do not include any Row/Column total in the range.

You can follow the steps below to apply this conditional formatting rule:

* Select the range/dataset.

* Go to Home > Conditional Formatting > Data Bars. You’ll see two options - one for Gradient Fill and one for Solid Fill. Their function is identical, just select the option and color you prefer.

Conditonal Formatting In Excel 11

* Click Ok and then the sheet will reflect the added rule.

Color Scales

Color scales in conditional formatting applies a color to cells in a cell range based on their values. The color scales are shades with gradations of two or three colors. If it is a two-color scale, the lowest value is assigned one color and the highest value is assigned another color and the rest are a blend of these two colors. It can be used to highlight general patterns in data, especially with a large data set.

You can follow the steps below to apply this conditional formatting rule:

* Select the range/dataset.
* Go to Home > Conditional Formatting > Color Scales and then click the color scale format that you want.

Conditonal Formatting In Excel 12

* The top color represents larger values, the center color, if any, represents middle values, and the bottom color represents     smaller values.

* Click Ok.

Icon Sets

Icon sets is used to display icons that correspond to the values in the cell. This rule contains various icon formatting shapes like:
* Directional: Up/down arrow
* Shapes: Traffic lights, circles and other colored shapes
* Indicators: Colored flags and Correct/Cross icons
* Ratings: Star Rating, Signal and other shapes

You can follow the steps below to apply this conditional formatting rule:
* Select the range/dataset.
* Go to Home > Conditional Formatting > Icon Sets and then click the icon set that you want.
* By default, it highlights values greater than 67% numbers in Green, 33% to 67% in yellow and less than 33% in red color. An example is shared below.

Conditonal Formatting In Excel 13

* Click Ok and you will get the desired result.

Conditonal Formatting In Excel 14

Rules Manager

After selecting any conditional formatting rules from the built-in menu, you may need to edit the rule to define different format. To do that, select one of the cells in the range, go to Home > Conditional Formatting > Rules Manager.

All the existing conditional formatting applied to the current selection will appear in the box

Conditonal Formatting In Excel 15

Note: From the drop-down list, change Current Selection to This Worksheet, to view all conditional formatting rules in this worksheet.

 In the dialog box, you can select the Edit Rule option to modify the existing conditional formatting applied.
Conditonal Formatting In Excel 16


If Icon set is applied to the range of data, you can select edit rule and in the “Edit Formatting Rule” box, you can either edit the value or icon as per your needs and click Ok. Then click on “Apply” to see the change you have applied and if you are okay with the result, click Ok.

Conditonal Formatting In Excel There are various things you can do using the Rule Manager option, and these are especially useful if you've applied multiple rules to a worksheet. They are summarized below:

Conditonal Formatting In Excel 17

* New Rule - To create a conditional formatting rule
* Edit Rule - To edit an existing rule
* Delete Rule - To delete an applied rule
* Up/Down Button - To change the position of a rule when multiple rules exist. To move the selected rule up       in precedence, click Up. To move the selected rule down in precedence, click Down. These rules are               evaluated one at a time starting with the rule listed at the top. A later rule cannot override the formatting           already modified by a preceding rule.

* Stop if True Checkbox - Select the Stop If True checkbox to stop rule evaluation at a specific rule.

* Applies to Column - Specifies the range of cells to which a rule is applied. If you want to change the range,    you can do it from here.

Conditional Formatting In Excel Based On Text

Conditional Formatting allows you to format a cell based on the text entered in it. Let’s understand with the help of an example.

Conditonal Formatting In Excel 18

In the example above, you have a list of transactions and you want to highlight the cells in Column D for which the order hasn’t been delivered yet i.e. the text in that cell is “No”. To do that:

* Select the range D2:D19
* Go to Home > Conditional Formatting > Highlight Cells Rule > Equal To
* In the dialog box, type the word “No”.

Conditonal Formatting In Excel 19

* Select the pre-defined formats from the dropdown or select custom format

* Click Ok.

Conditional Formatting based on Another Cell

We can highlight an entire row based on a cell’s value using conditional formatting based on a simple formula. Let’s understand with the help of an example.

In the example above, you have a list of transactions and you want to highlight the row for which the order hasn’t been delivered yet. With the help of this article you now know how to highlight the cells in the column “Delivered” if the cell value is No.

 But how to highlight the entire row. Say, for transaction no. 4001 the delivery status is undelivered, so you want to highlight the range A2:D2 rather than just the cell D2. How to do that? Just follow along.

First follow the steps to just highlight the cells in column D. To do that:

* Select the range D2:D19
* Go to Home > Conditional Formatting > Highlight Cells Rule > Equal To
* In the dialog box, type the word “No”.

* Select the pre-defined formats from the dropdown or select custom format
* Click Ok.

These steps will help you highlight only the Delivered Column. But what we want is that the formatting is applied on all columns (Transaction No, Customer Name, Sales & Delivered) based on the value mentioned in Delivered Column. So, when we want to format a cell based on the value in a different cell, we’ll need to use a formula to define the conditional formatting rule.

So, first select the entire range i.e. A2:D19 and then select "Use a formula to determine which cells to format" after clicking on New Rule from the Conditional Formatting menu.

Type the formula =$D2="No” in the dialog box and click on “Format” to select the desired custom format and then click Ok.

As you can see all the columns have been highlighted based on the value in the delivered column.

If you look at the formula used, you will see only one formula =$D2=”No” works for the entire range A2:D19. Excel is essentially copying this formula to each of the cells in the Applies To range. To make this formula dynamic, a mix of absolute and relative reference is used.

 We don’t want the column reference D to change i.e. for all cells we want Excel to refer to the Delivered column, column D to determine if the row should be formatted or not. So, we placed a “$” sign in front of D to prevent Excel from changing the column reference.

 When the same formula is used for the rows below, we want the row reference to be updated accordingly. Let’s say Excel is trying to determine the formatting for cell A5 so it would want to refer to the cell D5 i.e. the column reference is the same, but the row reference has been updated. So, to keep the row reference flexible you should not put a “$” in front of it.

 So, the desired formula will contain the cell $D2. 

This allows us to highlight the entire row by conditional formatting.

Note: we can use the other criteria by entering different conditions under the Formula text box depending on your requirement.

Conditional Formatting In Excel - Font, Borders & Number

As Excel provides the flexibility to input custom formula, it also allows you to select custom formats including fonts, borders, fills and number format. For instance, if the data is negative, you may want the data font color to be red or when the sales region is US, you want currency to be $, etc.

You can use Conditional Formatting to obtain these results. To change the font color to red if the data is negative follow the steps below:

* Select the cell values
* Go to Home > Conditional Formatting > New Rule
* In the dialog box, select “Format only cells that contain”
* In the edit rule section, from the dropdown select “Cell Value” and then “less than” and then type 0.

* Then click on Format, and in the Format Cells dialog box, under Font Tab, select red color from the dropdown and click OK.

Similarly, if we wish to add borders instead of changing font color, simply click on the Borders tab in the Format Cells dialog box and then click on outline to insert a border. You can also select a style from the options given in the left panel or change the color of the border line from the color dropdown provided.

Lastly, you can also change the number format of a value if a certain condition is met using Conditional Formatting. Let’s understand this with the help of an example.

In the data above, sales transaction has been provided and the sales have taken place in two countries - USA & UK. But the sales amount column contains $ sign only. Now, using conditional formatting you want to change the number format of sales column and add a £ sign when the country is UK, and let the $ sign remain when country is US.

 First, select the column where you want to apply the conditional formatting i.e. Sales Column (D2:D19). Go to Home > Conditional Formatting > New Rule. In the formula bar, type =$C2=”UK” and then click on format. In the Format Cells dialog box, Go to Number Tab > Select Currency > From Symbol dropdown select £ and finally make the decimal point 0. Click Ok and you see that for the rows where country is UK, the sales amount is displayed in £.


As you have changed the number formatting with respect to currency, you can also change the date format of any value based on a condition. You can select any pre-defined format of dates like dd/mm/yy or mm/dd/yyyy or dd/mmm/yy etc. Or else you can set a custom format by selecting custom from the Format Cells dialog box.

Troubleshooting Conditional Formatting In Excel

Sometimes, even though you follow the steps to apply a specific conditional format, the rules you apply don't display the results you intended. Troubleshooting of those errors are covered in this section of the article.

When there are multiple rules that returns TRUE for same cell

Let’s look at an example. Suppose in the data below you want to fill cells with green color when the Sales Amount is greater than 30,000 and fill sales column’s cells with yellow color when the Country is UK.

Now let’s add the first rule i.e. fill cells with green color when the Sales Amount is greater than 30,000. To do that, Go to Home > Conditional Formatting > Highlight Cells Rule > Greater than. Type 30000 and then select custom from the dropdown. Go to Fill Tab, select green color.

This will give you the result as shown below; all values greater than 30000 is now green.

Now let’s add the second rule i.e. fill sales column with yellow color when the Country is UK. Select Sales Column, Go to Home > Conditional Formatting > New Rule > Use formula. Type =$C2=”UK” and then click on format and under the fill tab select yellow.

Click ok and the result will be as shown below:

You might have noticed that the cell D2 which was initially green fill as the value was greater than 30,000 has now been changed to yellow. This is because cell D2 satisfies both the rules and since both rules apply the same format i.e. fill color, precedence will matter here. Had the formats been different, Excel would have applied both the formats.

 The rule you enter first has the least precedence, and the rule you enter last has the highest precedence as by default it sits at the top. So, here the fill yellow rule overlaps the fill green rule.

You can change this order at any time by clicking on Manage rule under Conditional Formatting. You will see all the rules applied in the dialog box, the one on the top is of the highest priority. Here it is the fill yellow rule. To move the fill green rule above, simply click on that rule and then click on the “^” symbol.


This will force Excel to fill the cells with a value greater than 30,000 green in color even though it meets the condition for other rules having the same format.

Note:

        * When applying different formats, Excel applies all formats because there's no conflict between the                    formats themselves.

        * When applying the same format, Excel applies the rule (format) with the highest precedence.

Another error you might encounter is that format should be applied to the one column whereas the condition should be checked from another column. It might happen that both have been applied to the same column. To prevent it,

Make sure that you select the column where the format must be applied and use cell reference of the column where the condition is when mentioning the formula. If you forget to do so, you can simply change the column to which the format will be applied by clicking on “Manage Rule” under Conditional Formatting option in Home Tab.

In the rule manager dialog box, click on the rule where you want the change (here to let it be the first rule). Then click on the arrow symbol highlighted in the image, select the desired range, click on the arrow again and then click OK. These steps will help you change the “Applies to” range for any rule.

Another point to make sure is that the formula you type in the rule should always start with an “=” sign (just like any formula in Excel). If in case you forget to put an equal to sign (=), Excel will treat it as a text and not a formula. To fix, just remove the double quotes Excel added at either side and make sure the formula begins with equal to sign (=).

Lastly, if none of these points can fix your error, it means that there is an issue with the formula itself. Had the formula been typed directly on the worksheet, you could have used Evaluate feature to thoroughly check each step one by one. But unfortunately, this feature cannot be used when the formula is typed in the conditional formatting box. So, what to do?

You can check your conditional formatting formulas directly on the worksheet, so you can see what they're doing. You can enter the formula across the range of the cells matching the exact shape of your data i.e. the number of rows and columns of the data. When a condition is met and it returns TRUE, the format is applied and when it returns FALSE,

Excel does nothing. By writing the formula directly on the worksheet, you can visualize how formulas will behave before you create a rule. In this way, you make your formula free of any error and then paste that formula on the conditional formatting rule.

Copy Conditional Formatting In Excel

If you have applied conditional formatting to any specific range, you can copy it to other range/columns as well. Select the cells from which you want to copy the conditional formatting and then press Ctrl+C. Select the cells where you want to paste the formatting and then press Alt+E+S (shortcut to open paste special box). In the paste special box, select formats and click Ok.

Another way to do this is to use the Format Painter to copy the conditional formatting to that data.
* Click on the cell that has the conditional formatting you want to copy.
* Click Home > Format Painter. The pointer changes to a paintbrush.
* To paste the conditional formatting, drag the paintbrush across the cells or ranges of cells you want to                format.

Note: To cancel using the paintbrush, press Esc on your keyboard.

Conditional Formatting In Excel Using Formula

Highlight values based on Today’s Date

You can apply a formatting based on a rule that it is related to a date. Look at the data below, there is a table containing details regarding various invoices.

Each invoice contains information including product name, amount and the delivery date. If you want to highlight each row with say green color if the delivery date is within the next 7 days from today, you can use conditional formatting for that. (As you probably know Microsoft Excel provides the TODAY() functions for various calculations based on the current date). To do that, follow the steps below:

* Select the entire range i.e. A2:D22
* Go to Home > Conditional Formatting > New Rule > Use Formula to determine which cells to format.
* Now, you need to insert the formula to check if the date is between today and today +7. The formula will be     =AND($D2>=TODAY(),$D2<=TODAY()+7)
* Select the format and then go to Fill tab and select green

* Click Ok and the formatting will be applied to the range.

Let’s say today’s date is 18th Sep 2019, you will see that all rows that’s between 18th Sep and 25th Sep will be highlighted in green color.

You can also use complex formulas in conditional formatting and apply desired formats in Excel. Let’s look at an example. Here, you have data of sales of various products and you have a search option available in cell B1 and once you type a word in it, any row containing the word in their product name should be highlighted. 

To do that, follow the steps below:

* Select the range i.e. A4:C22
* Go to Home > Conditional Formatting > New Rule > Use formula to determine which cells to format
* Enter the following formula: =ISNUMBER(SEARCH($B$1,$B4)
* Click on format > Under Fill Tab > Select Green Color
* Click Ok

Conditonal Formatting In Excel ,All the rows with a product name that contains the word “sofa”, have been highlighted.

This article has focussed on covering all possible topics related to Conditional Formatting and by following this you would have understood how powerful and flexible this feature is. With just a few clicks of conditional formatting, you can add powerful insights to your data.

For more interesting and useful tips and tricks related to Excel, keep following our blogs!