Pinterest Pixel
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.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!