All You Need to Know About

Conditional Formatting

Learn how to add color & shapes to your data & reports to highlight key findings.

This section shows how much you can do in analyzing data in Excel.

You would think that Microsoft Excel is all about inputting data and creating reports to make sense of it. But there is much more! Conditional formatting in Excel is one of the coolest features and there is so much you can do with it.

Here are the top things on how to do Conditional Formatting in Excel:

Conditionally Formatting a Drop Down List

We are now going to take this concept one level further and apply some conditional formatting to the drop down data validation list.

This is useful if you want to highlight when a job is completed, check off items from a list or to evaluate risk in a project just like I have done in below´s example.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select the range that you want to apply the conditional formatting to.

Conditonal Format a Drop Down List

STEP 2: Go to Home > Styles > Conditional Formatting > Manage Rules

Conditonal Format a Drop Down List

STEP 3: Select New Rule

Conditonal Format a Drop Down List

STEP 4: Create the new rule for High values:

Select Use a formula to determine which cells to format

Type in the Formula =$A4=”high” 

This formula will ensure only the column is absolute.

Go to Format > Fill then select a color of your choosing. Click OK.

Conditonal Format a Drop Down List

Repeat the same steps for medium values. Click New Rule.

Select Use a formula to determine which cells to format

Type in the Formula =$A4=”medium” 

This formula will ensure only the column is absolute.

Go to Format > Fill then select a color of your choosing. Click OK.

Conditonal Format a Drop Down List

Repeat the same steps for low values. Click New Rule.

Select Use a formula to determine which cells to format

Type in the Formula =$A4=”low” 

This formula will ensure only the column is absolute.

Go to Format > Fill then select a color of your choosing. Click OK.

Conditonal Format a Drop Down List

This is how our new set of rules will look like:

Conditonal Format a Drop Down List

Now our table now has conditional formatting applied!

Conditonal Format a Drop Down List

Find Duplicates Using Conditional Formatting

Normally when we have dirty data, we tend to get a lot of duplicates. But in Excel it is very easy to spot the duplicates for your data cleanup!

Here is our sample list of words, you can see it has a lot of duplicates:

I explain how you can do this below:

DOWNLOAD EXCEL WORKBOOK

STEP 1:Select your list of words / data:

STEP 2: Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

STEP 3: You can select the formatting that you want. For our example, we selected Green Fill with Dark Green Text.

Click OK.

You will now see the magic happen, all of the duplicate values are now highlighted in your Excel worksheet!

Conditionally Format a Cell’s Value

A great way to highlight values within your data set, Excel Table or Pivot Table is to use Conditional Formatting rules.

Formatting cells that contain a specific criteria, for example, greater than X or less than X, is a good way to visualize your results.

When your criteria references a cell, then you can make this conditional format interactive.  So as you manually change the referenced cell´s value, the conditional format gets updated and you can see the live results, as shown below….

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select a cell in your Pivot Table.

STEP 2: Go to Home > Conditional Formatting > New Rule

STEP 3: Set Apply Rule to the third option: All cells showing “Sum of SALES” values for “MONTH” and “YEAR”

STEP 4: Select a rule type: Format Only Cells That Contain

STEP 5: Edit the Rule Description. Go to Cell Value > Greater Than > Select The Cell

STEP 6: Select the cell format. Click Format and select a color. Click OK.

Try it out now! The highlight now happens dynamically when you update the value.

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]