Conditional Formatting in Excel

Excel has a neat alert system that is often overlooked. This feature allows you to visually point out discrepancies in data, sort out duplicates and even highlight every other row in Excel. The conditional formatting feature automates these things and more.

Watch this quick tip to learn how to bring color and uniqueness to your Excel spreadsheets.

Download the sample worksheet here.

If you would like an alert when the latest video debuts, you can subscribe to our YouTube Channel

How to use Conditional Formatting in Excel video transcript:

While using Excel, have you ever wanted to make data more visible with colors and styles that actually change based on the data itself?

For example: what if we wanted the force numbers in this data table to follow rules: One - all numbers between 30 thousand and 60 thousand must become bold, and have a box. Two -the top 10 numbers will be awarded a golden highlight. And any number below average must pay the penalty of purple. And finally, the remaining numbers will be highlighted light blue.

We’re not going to get this complex, but we are going to learn how to create simple rules likes these with the Conditional Formatting tool in Excel.
This table might look familiar from a previous quick tip. I’ve already shaded the title axes in the Font section of the Home tab. This type of shading is different from Conditional Formatting.

I’m going to show you how to make it look like this giving us a clear visual transition from negative numbers to positive numbers. As well as a nice fade from light green to dark green. I’ll be sure to point out additional features along the way.

First, highlight the values that we want formatted. Go to the Home tab, under the Styles section, and click Conditional Formatting.
Excel has a variety of pre-set templates with rules for highlighting particular cell ranges, adding bars based on values, color scales, and even icons. Feel free to explore these different template rules.

But for now, select New Rule. The New Formatting Rule window gives us six different Rule Types that can essentially create each of those templates we just looked at fairly easily.

To get the look we want, make sure the top Rule Type is highlighted – we want to format all cells based on their values, then go to the Format Style drop down and click on 3-Color Scale. We want to make anything below the Minimum | Number of 0 light red and anything above the Number of 0 light green and the Highest Value in my range can remain green. Click O.K.

And there you have it.

To further demonstrate the power of Conditional Formatting, I will copy this data table below to allow editing of these values. Highlight, CTRL C, and select, CTRL V. Now we have two sections to where the Conditional Formatting is applied. When we change various values, the rule stays true.

Now you know how to use the Conditional Formatting feature in Excel to make your data look more visible and fantastic.