in Excel

5 uses for conditional formatting in Excel

Everyone knows that Excel is a complex piece of software, at a conference a person was saying that we use our potential the same way we use Excel, only about 10% :).

In this article I am writing about ways we can use conditional formatting in Excel, hoping that this can help you be efficient and do more. As I don’t think that conditional formatting it’s not “brain surgery”, I won’t tell you how to click on conditional formatting, but rather give you 5 ideas.

Conditional formatting 1

1. Validation

If you don’t like data validation, conditional formatting offers a great alternative. Let’s say that on a certain column you want to make sure that all values are numbers between 1 and 100, if not you want the cells to become red (“burning red”).

This is what you should do: select the column, click on conditional formatting, highlight cells rules, more rules and “punch” in the settings from the picture bellow.

Conditional formatting 22. Clear duplicates

Finding duplicates in Excel can be done very easy using conditional formatting, 3 clicks and the problem is solved.

Conditional formatting 3

3. Graphic number representation

Images are more easy to understand, we see trends quicker when we see numbers as “Data Bars”, like in the image bellow.

Conditional formatting 4

4. Showing top/bottom values

Let’s say that we have a table with multiple numeric values, maybe with all the grades in the class. If I would like to highlight the top 3 and bottom 5. We have the “top/bottom values” straight from Microsoft with an autograph.

Conditional formatting 5

5. Showing trends

It is very trendy to talk about trends, right? Let’s say that we want to breathe some life into a monthly cashflow report. We can have arrows that show us the evolution as you see bellow.

Conditional formatting

But how

I did not give you many details about how exactly you can use these features because things are very simple and intuitive. My recommendation is just to click around and see how things work out.

How about number 6

Ok, that the 5 from my side, I know that the list is not exhausted, help me out with your opinion in the comments bellow.

Write a Comment



  • Think you are good in Excel? here is what you should know | FCS

    […] This is where conditional formatting comes in handy, you should know about it. […]