Conditional Formatting in Microsoft Excel – Highlight Cells

Excel’s conditional formatting can transform raw data into meaningful insights. In this clip from my Conditional Formatting in Microsoft Excel, you’ll explore how to apply conditional formatting in Excel by using Highlight Cells!

Full Conditional Formatting in Microsoft Excel Course (opens in a new tab)

For more information, read the transcript blog below, or watch the video above!

Video Transcript

Learning conditional formatting is a lot more fun inside a demo environment. So, let’s start with the Highlight Cell Rules. First, let me go to the claim amount over here, where I have all the claims in my auto-claims Excel file. What I want to do is select all of it, and then to quickly access conditional formatting, we have it here in the ribbon inside the Home category. I can click on it, and then I will see the five different types of conditional formatting that we just talked about in the slides: Highlight Cell Rules, Top and Bottom Rules, Data Bars, Color Scales, and Icon Sets.

But for this demo, let’s focus on Highlight Cell Rules. What I want to do for this first demo is highlight in red all the different claims that are over $4,000. So, I will select “Greater Than,” and now I’ll have a new popup that appears in which I will be able to select my options. Remember, the first part is my condition: highlight cells that are greater than $4,000. Then, I choose my formatting. Do I want it to be a light red fill with dark red text, a yellow fill with dark yellow text, a green fill with dark green text, and a few more built-in options? But of course, we can build our own.

Let me start with the light red fill with dark red text here. Click on OK, and it’s done. If ever you have a cell you see here, I selected the claim amount. I don’t want it to apply to the claim amount as well. I can always go to Conditional Formatting here, clear rules, and only clear the rule from the selected cell. Now, every single cell in the values I selected that are greater than $4,000 are now shown with a red background and red text. Remember, we said greater than, not greater or equal to, so this one that is exactly $4,000 will not show up. But if I change it to $4,001, the conditional formatting will now apply. So, it’s very, very important to be exact in the conditional formatting that you want, especially when you build that condition.

Now, let’s take a look at another example. Let’s take the claim status over here. What I want to do now is, as long as they’re approved, show them in green. So again, I selected a column. I’ll go to Conditional Formatting, Highlight Cell Rules, when it’s equal to or a text that contains. Both of them would work in this case. Let’s go with “Equal To.” Let me type in “approved,” and I will apply a green fill with dark green text. That’s it. Now, of course, as values change, so if I take this one over here and then I change the value to “approved,” it will now show in green. So, it’s dynamic. As long as it’s in the cells that we apply this formatting to, whenever you change the data, the formatting will apply or go away depending on your condition. Awesome. Let’s take a look at another one.

Have you ever opened an Excel file and then tried to import it into another tool, and it says, “Hey, I cannot do that because you have duplicate values,” or you simply want to find duplicate values inside an Excel file? Well, I can simply select this here, go to Conditional Formatting, Highlight Cell Rules, and Duplicate Values here, which is one of the built-in options, and I can either find duplicate or unique values. But in my case, I want to find duplicate values, so let’s fill them with yellow over here. You can see that I have 15 twice over here. This should have been 16. Let me fix it. Now the formatting went away on both because none of them are duplicate anymore. And I had another one here. Let me fix it. Perfect. So, you can quickly highlight duplicate values inside a column.

Before we finish this demo, I also want to show you the Conditional Formatting Manager. So, I’ll go under Conditional Formatting, then I will go to Manage Rules. By default, this will show you all the rules for the current selection, but I can also see all the rules for this worksheet. You can see the duplicate values apply to the A column, if the cell value equals to “approved” applies to my Y column, and if the cell value is over $4,000 applies to my H column over here. You can see this one actually has a limit just because we didn’t apply it to the top one there. We removed it, so it shows a bit different. You can always go on a rule, so I’ll double-click on it.

You see when I mentioned that if it was $4,000, it was not applying because we said greater than. I can change it to greater than or equal to, apply it over here, and now you saw some of them, like the $4,000 over here, changed because it now fixed the condition. Something else that I can do is maybe I don’t like the red formatting. I want to have formatting that applies to my company brand. What I can do is go back to the rule here, and then near the preview, go to Format here, and then I can change what is the font that I want. Do I want an underline? What is the color that I want?

Let me go to white here, and then under Fill, I can go to More Colors here and then maybe pick something that is more on-brand. So, I want this magenta over here. Of course, if you want to follow your company brand, you’re better off copying the hex code and pasting it here. But let’s click on OK. We can go to Font is white, and click on OK. Here we have a preview. Let’s apply it, and now my formatting changed with some custom colors. But this is it. This is how easy it is to apply conditional formatting by highlighting cells that fit a certain criteria.