Conditional Formatting

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.
STEP 1: Select the range that you want to apply the conditional formatting to.
STEP 2: Go to Home > Styles > Conditional Formatting > Manage Rules
STEP 3: Select New Rule
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.
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.
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.
This is how our new set of rules will look like:
Now our table now has conditional formatting applied!
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:
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….
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.
Top 5 Tutorials
- Show the Difference From Previous Month with Directional Icons– Using a Pivot Table you can show the variance from your previous month’s sales very easily…Read more
- Icon Sets In A Pivot Table– An Icon Set is a Conditional Formatting icon/graphic that you can include in your cells or Pivot Tables.Read more
- Conditionally Formatting A Drop Down 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.Read more
- How to Find Duplicates in Excel– 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!Read more
- Conditionally Format a Pivot Table With Data Bars– Data Bars are a new Conditional Formatting feature in Excel 2010 and they add a colored bar to your values.Read more
Latest Tutorials