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

Do not worry , 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**)*

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

**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.**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.**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.**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.**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
: select*cell format**Green Fill with Dark Green Text*using the**drop-down**menu, - then click
**OK**

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**

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**

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.

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**

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:

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

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 2^{nd} 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**

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

**Select the cells**to which the rules are applied- Click the
**Conditional Formatting**button, and then click**Manage Rules**. - In the Conditional Formatting
**Rules Manager**, select the rule you want to delete. - Click
**Delete Rule** - Click
**OK**

*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:

- Highlight with
**green**if performance is**more than 10%** - Highlight with yellow if performance is between
**10% to -10%** - 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 3
^{rd}step with two different formula and format**=AND(E5<=10%,E5>=-10%)**with Yellow color format- Again, formula
**=E5<-10%**with Red color format

Check Image instructions and try to replicate the above steps.

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

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:

- 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
- Use
*live preview*just by hovering each option**.** - Select an
**appropriate Data Bar**

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**

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:

- Go to worksheet ‘
**ColorScales**’ of Excel working file, select cell range**E6:E35***(Image instructions below)* - Click the Home>Conditional Formatting button,
- Then, click
**New Rule** - Select last option ‘
**Use a formula to determine which cells to format’** - Enter formula
**=AND(E6=0)**; don’t select any format - Click
**OK** - Select
**New Rule**again - And, select
**2 Color Scale**, and Click**OK** - Go to Manage Rule
- Make formula as the
**first rule**using up arrow and tick ‘**Stop If True**’ box - Click
**OK**

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 3^{rd} , 4^{th} or 5^{th} , 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:

- 4 rating if sales are
**10% more**than average;**=AVERAGE($C$3:$F$8)*1.1** - 3 rating if sales
**average**(+/-10%);**=AVERAGE($C$3:$F$8)** - 2 rating if sales are
**10% less**than average;**=AVERAGE($C$3:$F$8)*.9** - 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:

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

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.