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.
HOW TO APPLY 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.
The three automatic ways of applying Conditional Formatting in Excel:
- Data Bars
- Color Scales
- Icon Sets
Well, if you choose Data Bars, it offers two options:
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.
On the other hand, Color Scales are different, instead of one color of various length, Color Scales gives you different colors.
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.
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.
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.
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.
HOW TO APPLY ONE CONDITIONAL FORMATTING OVER ANOTHER IN EXCEL
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.