Learn Conditional Formatting in Excel (An Essential Guide)

Do you sometimes wonder, why Excel cell changes its color or how we can change the cell background color automatically ❓

Do not worry :mrgreen: , the answer is by using Excel’s Conditional Formatting

Conditional Formatting is a robust Excel tool that allows you in performing a painless yet powerful visual analysis of raw data by identifying patterns and trends. It highlights Excel cells through features like colors, icons, and data bars.

The key topics which we will be covering here are:

In this step-by-step guide, you will find 10 examples covering all essential aspects of conditional formatting.

Download Your Example Excel File

While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.

What is Excel conditional formatting?

Once an Excel worksheet gets enable with conditional formatting, the cell format dynamically changes to colors, icons, and data bars if specific criteria or rules are met resulting an eye-catching visual pattern for analyzing a data range. Thus, you can simply say “a given format based on certain conditions.

Where is Conditional Formatting in Excel? 

  • Open an Excel workbook
  • click Home tab
  • Go to the Conditional Formatting menu under the Styles group
  • Select options as required (Keep learning below)
Where is Conditional Formatting Excel
Highlighting Cells with Conditional Formatting

As you can see, Excel offers five predefined options for formatting features. Let’s take a bit to understand each option:

  1. Highlight Cells Rules: The very first option is ‘highlight cell rules’ are self-explanatory, this rule gives freedom to dynamically change cell format based on cell’s value (numeric or text) only if it passes conditions.
  2. Top/Bottom Rules: Highlight all cells in a range that are greater than or less than a given threshold or value compared to other cells.
  3. Data Bars: A data bar enables a cell format with a mini horizontal bar indicating the value of each cell. It fills the cell with the bar’s, length depending upon the numbers of each cell in the range.
  4. Color Scales: Fill color on a cell range based on the value of each cell relative to other formatted cells. Excel apply color scale from the largest to smallest values following Munsell’s Neutral Value Color Scale.
  5. Icon Sets: Display different inbuilt icons on cell based on relative value to all other adjacent cells within the same format range.

So, let’s find out what we have in ‘Conditional Formatting’ examples.

Color Excel cells based on a Value

To highlight or color a cell automatically as per the value (numeric or text) the Conditional Formatting is a handy feature of Excel.

You have a Sales data set of 24 months and your task is to highlight cells with green color if the value is more than 100.

To highlight cell range based on values :

  • Go to worksheet ‘Highlight Cells Rules‘ of Excel working file (Image instructions below)
  • select the range C6:C29. For highlighting less than, negative or equal to the number, follow the same instructions.
  • Click the Conditional Formatting button, point to Highlight Cells Rules,
  • and then click Greater than…, or click the type of rule you want to create.
  • The Greater Than dialog box appears, or similar box depending upon your rule type.
  • Select the first box, and enter 100 (in this example)
  • Go to next box to specify cell format: select Green Fill with Dark Green Text using the drop-down menu,
  • then click OK 
Color Excel cells based on a value in Excel
Highlight Cells only if specific conditions are met
Colour Excel cell if it passes the condition or criteria in Excel
Define trigger values with the color format in its dialog box

Excel automatically highlight the cells with more than 100, the formatting is dynamic which means the color will automatically change to default format once the cell does not meet this criterion.

If we investigate the numbers in the range, we will find that there were specific months of both years when sales were more than 100 indicating a pattern or seasonality of sale.

  • Use “Live Preview” feature which allows you to preview changes before you finalize them.
  • To generate random numbers I have used an exciting function =RANDBETWEEN(bottom,top).
  • No other color format will take place If Conditional Formatting is active on a cell.

Highlight Text in Excel

To highlight or find specific text or number within an extensive data set the conditional formatting can be a very convenient tool.

In the next example, you have a list of 13 fruits, and the task is to highlight “Berry” fruits only.

To Highlight similar text in Excel:

  • Go to worksheet ‘Text’ of Excel working file (Image instructions below)
  • select the cell to apply the rule for a conditional format
  • Click the Conditional Formatting button, point to Highlight Cells Rules
  • And then click Text that Contains…
  • A Text That Contains dialog box appears
  • Select the first box and enter Berry (in this example)
  • then click OK
Highlight duplicate text in Excel
Find a Text value of a range

The result will highlight cells containing text “Berry”.

  • Text search is not case sensitive.
  • Space before text value does not impact the formatting

Find duplicate or unique value in Excel

Many times, we encounter a problem where we need to find same or identical data points in a large data set.

Let’s take an example from our Excel file.

In the next worksheet, you have two lists of 30 students each in the highest-to-lowest order of score in two classes (Physics and Maths). Moreover, you want to highlight the name of students who were in the top 10, both class.

To find duplicate or unique value:

  • Go to worksheet ‘Duplicate&Unique’ of Excel working file (Image instructions below)
  • select the first 10 cells of both columns to apply the rule
  • Click the Conditional Formatting button, point to Highlight Cells Rules
  • and then click Duplicate Value…
  • Select Duplicate in the first box, or select Unique from the drop-down menu (as required)
  • then click OK
Find duplicate unique in Excel
Highlight Unique and Duplicate value of a range

Now we can see that in our case we have a total of Five students who were in the top 10 for both subjects.

A similar example, you have a task in hand to find top 1000 listed companies as per their market capitalization or sales. There is a perfect chance that some companies will fall under both criteria. Use Conditional Formatting to manage such issues.

Highlight Top and Bottom value in Excel

In many occasions, you will find data where you want to highlight cells that are meeting a minimum or maximum benchmark. You can highlight cells based on their values relative to other cells.

Like, to color top or bottom 10% of performing dealers of a company or everyone scoring more than Average.

Highlight Top or Bottom cells of data in Excel
Predefined Top-Bottom Rule and adjust by using More Rules

In our example, you have 30 employees with yearly sales, and the task is to find the top 20% performers among them :/.

To find the top or bottom value:

  • Go to worksheet ‘Top_Bottom Rules’ of Excel working file (Image instructions below)
  • Select the range of cells to apply the rule.
  • Click the Conditional Formatting button, point to Top/Bottom Rules
  • And then click Top 10%…
  • Type 20 in the first box, or use up-down control
  • then click OK
Find top or bottom numbers in Excel
Highlight to cells in a range that are greater than or less than a given threshold.

The result will show the top 20% of sales (not employees), refer employee’s column to know the name of each employee.

Adding your Own Conditional Format rule

The example so far you have covered were format based on the cell’s value. Excel provides freedom to go beyond that.

Creating your own formatting rules using New Rule.

By clicking New Rule command, a New Formatting Rule dialog box pops out which enable you in creating, editing or adding your own rules as well.

Following are
six rule types in the dialog box:

Create new rule or formula conditional format Excel
Create new rule using Six different options; each option has its trigger values and format.

As you can see, some of the rule types at the top of the New Formatting Rule dialog box are predefined options that are similar to what we discussed earlier here.

Highlight cells using a formula in Excel

Although Excel has lots of built-in conditional formatting options, you may find some time that these options are not enough or choices available is not feasible to solve your query.

In this case, Excel provides you with the flexibility to create your own rule and allow access to go beyond predefined options.

Let’s take an example from the Excel file.

You have three columns with information employees name, sales target and actual sales of each employee and looking to highlight those sales figures that were higher than target by at least 5%.

To create conditional formatting using a formula:

  • Go to worksheet ‘Formula’ of Excel working file (Image instructions below)
  • Select range D5:D34 to apply the rule.
  • Click the Conditional Formatting button, and then click New Rule
  • Select last option Use a formula to determine which cells to format
  • Type formula =D5>(C5*1.05)
  • Don’t forget to select Format..
  • Choose the color using Fill section in the Format Cells dialogue box,
  • then click OK (twice)
Create your own conditional formatting formulas in Excel
Use the last rule type in the list to create your own conditional formatting formulas.

The result will highlight sales data in column C, only if it is at least 5% higher than target sales.

We can also use the Excel SEARCH function to find duplicate texts just like in our 2nd example, using the formula:

=SEARCH(“Berry”,B5) 

Edit the existing conditional formatting formula in Excel

It is very common to change or update the current rule whenever you think necessary. The Excel Rules Manager enable you to create new, edit or remove conditional formatting with one or multiple control, all in one dialog box.

Moreover, you can also find what conditional formatting rules are already existing on a worksheet. You can create or modify one or multiple pre-set rules.

Let’s continue with the previous example; management wants to give a bonus to employees who surpassed the target by at least 10%, not just 5%.

To edit existing conditional formatting formula:

  • Go to worksheet ‘Formula’ (with format) of Excel working file (Image instructions below)
  • select any cell in range D5:D34 to edit rule.
  • Click the Conditional Formatting button, and then click Manage Rule (bottom)
  • Click Edit Rule in Rules Manager
  • Change the formula to =D5>(C5*1.1) from =D5>(C5*1.05)
  • Click OK
Edit existing formula conditional formatting Excel
Use the last rule type in the list to create your own conditional formatting formulas.
Edit color format rules in Excel
Use the Conditional Formatting Rules Manager dialog box to tweak any rules that have been applied in a workbook.

After editing the existing formula, the result will highlight sales data in column C only if it is at least 10% higher than target sales.
Of course, if we adjust the number in any of the two columns C or D the formatting will change automatically.

To delete a conditional format 

  1. Select the cells to which the rules are applied 
  2. Click the Conditional Formatting button, and then click Manage Rules.
  3. In the Conditional Formatting Rules Manager, select the rule you want to delete.
  4. Click Delete Rule
  5. Click OK
Delete color format in Excel
To clear the conditional formatting from Clear Rules or using Manage Rules (Delete Rule)
Delete a conditional format using Manage Rule dialogue box
  • Once a format of conditional formatting is active you won’t be able to add a format directly.
  • Don’t clutter data with unnecessary highlighting

Download Your Example Excel File

Let’s try two more conditional formatting example using formula, to understand its capabilities further.

Create multiple formulae for conditional formatting in Excel

For now, we have entered one or two formats using default options. What if we have three scenarios? Let’s understand what that means,

Suppose we have a task to create three groups of Employees based on performance:

  1. Highlight with green if performance is more than 10%
  2. Highlight with yellow if performance is between 10% to -10%
  3. Highlight with red if performance is below negative 10%

In this case, we need to create three formula for all three different scenarios, and the color will change based on the value of other cells.

Let’s do it…

Multiple formulae for conditional formatting:

  • Go to worksheet ‘F_Percent’ of Excel working file, select cell range D3:D32 (Image instructions below).
  • Click the Conditional Formatting button, and then click New Rule
  • Select last option Use a formula to determine which cells to format
  • Type formula =E5>10%
  • Select Format..
  • Go to Fill section and select green, then click OK
  • Click OK again
  • Select any cell in the range again
  • then click the Conditional Formatting button, and then click New Rule
  • Repeat the same task twice from 3rd step with two different formula and format
    1. =AND(E5<=10%,E5>=-10%) with Yellow color format
    2. Again, formula =E5<-10% with Red color format

Check Image instructions and try to replicate the above steps.

Formula to highlight cells in Excel
Insert the first criteria and format to highlight cells
Add formula and color format based as per criteria in Excel
Insert the second criteria and format to highlight cells
Add formula and color format based condition Excel
Insert the third criteria and format to highlight cells

The result will show three different colors either, Green, Yellow or red depending upon the sales performance, segregating employees in three different categories.

Apply conditional formatting based on dates in Excel

You may encounter an issue where you would like to highlight numbers based on relative, absolute and mixed cell reference.  Example of relative and absolute formula are =B4*C4 and =$B$4*$C$4, respectively.

The examples so far you have covered are a relative cell reference. In this example, you will investigate an absolute cell reference example. Moreover, you will see how to Highlight adjacent or another cell, in this case, ‘customer name’.

In this example, you have two variables next EMI date and last payment date. Following is the criteria of the conditional format:
‘Highlight customer name with red if payment due date is more than 60 days

To highlight another cell as per due date:

  • Go to worksheet ‘F_Date’ of Excel working file, select cell range B7:B36  (Image instructions below)
  • Click the Conditional Formatting button, and then click New Rule
  • Select last option Use a formula to determine which cells to format
  • Type formula =$C$4-C7>60
  • Select Format..
  • Go to Fill section and select Green, then click OK
  • Click OK again
Highlight adjacent or other cell in Excel
Color the customer name if the due date is more than 60 days

In this example customer name in Column B get highlight as per the last payment date.

Highlight with Data Bars in Excel

We can also use data bars feature of Conditional Formatting to highlight cells on the value based on a formula.

Well, we can always select a whole column or row for conditional formatting, but If you are looking to highlight only specific cells with formula, please refer to the next example as well.

In this example, the assignment is to highlight value with data bars in employee age cell.

To find age (Year) from the given date of birth, the formula is =DATEDIF(C4,TODAY(),”Y”). This formula returns only the ‘Year’ component of the date.

Steps to add data bars in Excel:

  1. Go to worksheet ‘DataBars’ of Excel working file, select cell range D5:D34 (Image instructions below)
  2. Click the Home>Conditional Formatting button,
  3. and then highlight Data Bars
  4. Use live preview just by hovering each option.
  5. Select an appropriate Data Bar
Compare the data and adds a dynamic horizontal bar to the background of a cell containing a number in Excel
Compare the data and adds a dynamic horizontal bar to the background of a cell containing a number.

Well, you may find it annoying some time that Data bars overlap numbers. Or you may want to reduce its length or to hide numbers in a cell.

We will continue on the same worksheet;

To reduce data bars length and hide texts:

  • Go to worksheet ‘DataBars’ of Excel working file, select cell range D5:D34 (Image instructions below)
  • Click the Home>Conditional Formatting button,
  • and then highlight Data Bars
  • Select More Rules to open New Formatting dialogue box
  • Select Show Bar Only (to hide numbers)
  • Select Drop-Down under Maximum box
  • Change it to Number
  • Type 30 next box below to reduce Data Bars length
  • Click Ok
Modify the length of the data bars by altering the rules of the conditional format in Excel
Modify the length of the data bars by altering the rules of the conditional format.

With this example, you will be able to hide numbers from a cell and reduce the size of data bars. The size of data bars decreases because we adjust the maximum benchmark value of the cell range.

Highlight with Color Scales in Excel

Well, Color Scales highlight numbers with different shades of color as per the given format.

Let’s see the next problem;
“Highlight only those cells that pass a criterion given by formula”.

We have monthly Sales data of 30 Employees with a goal and actual sales in Column C and D, respectively. We aim to calculate commission with 7% rate if actual sales are higher than Goal and to make sure that you do not highlight cells with no commission.

To calculate the commission in Column E use =IF(D6>=C6,C6*7%,0) formula. This formula returns commission for only those employees who were able to exceed expectations.

Now check steps to shade cells with commissions only in Column E.

To highlight cells only if it passes a given condition:

  1. Go to worksheet ‘ColorScales’ of Excel working file, select cell range E6:E35 (Image instructions below)
  2. Click the Home>Conditional Formatting button,
  3. Then, click New Rule
  4. Select last option ‘Use a formula to determine which cells to format’
  5. Enter formula =AND(E6=0); don’t select any format
  6. Click OK
  7. Select New Rule again
  8. And, select 2 Color Scale, and Click OK
  9. Go to Manage Rule
  10. Make formula as the first rule using up arrow and tick ‘Stop If True’ box
  11. Click OK
Color Scales in Excel
Apply Color Scales in Excel
Color Scales with formula and use Only If True Excel conditional formatting
Include formula in Color Scales and use Only If True criteria

How does it work?

First, you highlight cells that is equal to Zero without any format and Second, to create a new rule with Color Scale in remaining cells. Moreover, Stop If True to make sure the first rule does not interfere with the second rule. Now you have two formats with two rules.

Highlight Cells with Icon Sets

We can stand out data using icon sets to provide strong visual emphasis. We have lots of choices like Traffic Light, Pie or many other options. Some of these Icons will divide data in 3rd , 4th or 5th , check example to understand what I mean.

In this example, you have six months of sales data of 4 salespeople in cell range C3:F8. The task is to analyze the sales value and show a 4-rating histogram in cell D14 based on average monthly sales.

The criteria and formula using AVERAGE function are the following:

  1. 4 rating if sales are 10% more than average; =AVERAGE($C$3:$F$8)*1.1
  2. 3 rating if sales average (+/-10%); =AVERAGE($C$3:$F$8)
  3. 2 rating if sales are 10% less than average; =AVERAGE($C$3:$F$8)*.9
  4. 1 rating for remaining;

I have used Excel Drop-Down list feature to select respective Month and Employee, and INDEX and MATCH function to extract sales figure.

For more, check a detailed analysis of how INDEX and MATCH formula work together. Moreover, don’t forget to refer the example worksheet as well.

Once we have a sales number in cell D14, we can start applying conditional format.

To apply Icon Sets with Formula:

  1. Go to worksheet ‘Icon’ of Excel working file, select cell D16 (Image instructions below)
  2. Click the Home>Conditional Formatting button,
  3. Click the Conditional Formatting button, point to Icon Sets
  4. Click More Rules… (last Option)
  5. Select Format Style: Icon Sets & Icon Style: 4 rating Histogram
  6. Change all option of Type to Formula using the drop down bar.
  7. Enter all 3 formula of AVERAGE function mention “above”
  8. Click OK
Icon Sets from drop-down menu and add formula to highlight in Excel
Icon Sets from drop-down menu and add formula to highlight

Sales Data is divided into four different sets now. After every selection of month and employees, the rating will update automatically.

  • You cannot use relative references in Conditional Formatting criteria for Color Scales, Data Bars, and Icon Sets.
  • The $ character keeps the cell reference from changing if that formula copied to another cell.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments