Conditional formatting in Excel.

Conditional formatting in Excel allows you to format numbers and dates according to their value.

Most times, Excel users, use it to highlight numbers that are especially high or low, but you can also use it to highlight numbers that fall into a certain range and even find duplicates values.

Take a look at the Excel worksheet below, at the end of this article, you’ll find the Excel file to download for practice.

Before you begin applying conditional formatting to an array of cells, best practice is to give a named range, so as to select all cells at a go.

Select all cells without using named ranges.

Click the first cell with a number and press the shortcut key below.

  • Control (Ctrl) + Shift + End: Extend selection to the last used cell.

Conditional formatting in Excel



In the Home tab, under the Styles section that’s where you find Conditional Formatting, click it and you find three automatic way of applying Conditional Formatting.


How to apply conditional formatting in Excel

The three automatic ways of applying Conditional Formatting in Excel:

  1. Data Bars
  2. Color Scales
  3. Icon Sets

Well, if you choose Data Bars, it offers two options:

Conditional formatting-Data Bars-Gradient fill

Gradient Fill


Conditional formatting-Data Bars-Solid Fill

Solid Fill

What you’ll notice is that low numbers have short bars and the highest numbers have the longest bars. You can also change the colors of the bars based on personal preference.

Gradient and Solid Fill conditional formatting

On the other hand, Color Scales are different, instead of one color of various length, Color Scales gives you different colors.

Color Scales Conditional formatting in Excel


This clearly shows you that the highest numbers are in darkest blue and the smallest numbers are white. Also, you can choose reverse or various color depending on personal preference.

Color Scales options

Icon sets are a little bit tricky with directional arrows differentiating between lowers numbers and larger numbers and it also contains more different options based on personal preference.

Icon sets conditional formatting in Excel


Here you have some directional arrows in this type of conditional formatting in Excel.

A red arrow is for the lowest number and a green arrow indicates the highest number.

Icon sets options

There is a whole lots options to select from in Icon Sets conditional formatting in Excel. Maybe, instead of three arrows, you prefer four to five arrows, you can select that.




Here’s a little trick, Excel allows you to apply one conditional formatting over another conditional formatting on same set of data.

Sometimes that’s good, other times you might end up with something tedious like this below.


So, if you want a very quick way of applying formatting to compares your numbers, I believe this is your best bet and quick way.

In the next articles I’m going to show you how to take control of the formatting you want, rather selecting the automatic formatting Excel provides.

Download File: Conditional Formatting

Here is a visual explanation. Thanks, sharing is caring.