Conditional Formatting in Excel

Leila Gharani
A free video tutorial from Leila Gharani
Microsoft MVP, Bestselling Instructor
4.6 instructor rating • 9 courses • 162,281 students

Lecture description

We’ll learn how we can influence the cell formatting based on a condition. Excel has a lot of inbuilt, easy-to-use conditions.

Learn more from the full course

Excel Essentials for the Real World (Complete Excel Course)

Microsoft Excel Beginner to Professional. Includes Pivot Tables, Power Query, NEW Formulas... (Excel 365, 2019 & 2016)

10:43:04 of on-demand video • Updated November 2020

  • Master the ESSENTIAL Excel formulas and features to accelerate your career
  • Learn Excel from scratch or fill in the gaps to become immediately confident
  • Learn to use Pivot Tables, Pivot Charts, Slicers and Time Lines for Interactive Excel Reports
  • Hacks and hidden Excel features from a Microsoft MVP with 15+ years of Experience
  • Explore the potential of Power Query. You'll be surprised at what you achieve with a bit of Power Query knowledge.
  • Get lifetime access to a proven and state-of-the-art Excel course including challenges, quizzes & exercises
  • Learn what's new in Excel for Microsoft 365 and how these changes impact the way you use Excel
  • Updated to include the new FILTER, XLOOKUP, SORT & UNIQUE functions
English In the next two lectures we're going to take a look at Excel's conditional formatting. So until now, we took a look at basic Cell Formatting, we looked at number formatting. Now we're gonna see how we can influence the Cell Formatting based on a condition. Excel has a lot of easy to use inbuilt conditions. Let's take a look at these two in this lecture. And we're going to take a look at data bars and icon sets in the next lecture. First off, what is conditional formatting? I've actually used this throughout all the workbooks you've been using on the index page. Whenever you come to select an option from here. So let's say, you say, I've done this and I understand this. These cells turn green. If you select some other option like I want to review this later, they turn another color. This uses conditional formatting. To get to it and to see what type of rule I'm using here. You need to go and manage the rules. If you already have rules inside, you can go and manage them or edit them. And you see the different rules that are used here. To edit them you can click on it and take a look at the type of formatting that is used. Now in our example we're gonna start from scratch. So let's go to our conditional tab. Let's see some of these in-built functions that we have. Let's say for yearly salary I want to highlight the cells that are greater than a specific value. So I'm going to go with this. Here I can directly input a value or I can use a cell reference. If I have some number sitting in a cell, I can reference it or let's just type something in. I'm going to go with a hundred thousand and then I can decide on the color that I want. So you have some built in custom formatting in there to make it easier for you. If you don't like any of these built in ones you can go to a custom format and decide on the fill color that you want, the font color and so on. And then click on Ok. Let's go back with this in-built one, click on OK, that's our formatting. So these were the numbers that are greater than 100,000. If I change this one to 90,000 press enter, it's not highlighted anymore. That's the beauty of using conditional formatting. So in addition to this we had other options. We can do less than, between, equal to, if a text contains a certain word, duplicate values, and so on. So for our dates for example, let's go and see who started to work between the first of January 2018 to first of January 2019 and decide and the formatting and then click on OK. And that's our dynamic formats. If for some reason you want to go and change that formatting or clear the formatting, you do it from here. First highlight the area, go to clear rules and clear the rules from either the selected cells or from the entire sheet. Entire sheet means any conditional formatting you've used anywhere on this sheet will be deleted. In this case I'm going to go and clear it from the selected cells. If you want to go and update the existing conditional formatting, you go to manage rules. You can see the existing formatting used. You can click to edit it, completely change the conditional formatting to another one. Or, you can add a new rule to this. You're not restricted to just one rule. You can select the cells that are greater than one hundred thousand and format them this way. But you can also add a new rule and go with format only cells that contain, if the cell value is less than, let's say thirty thousand. We want to format these in a red color, click on OK, and click on OK. And we see the two rules here. Click on Apply, if you like what you see, click on OK. Other useful options are to do a top bottom type of conditional format. So first off, let's go and clear the rules from the selected cells and let's go and highlight the top 10 items or the top 10 percent. Let's go with top 10 percent actually. I want highlighted in green and click on OK. In addition to this I want to highlight the bottom 10 percent. The red fill is ok. Click on OK. And that's all dynamic. So it takes a look at this dataset and from this data set It computes the top 10 percent and the bottom 10 percent and highlights them in a dynamic way. So if you take the 185,000 and and we change this to 85,000 that disappears from the top 10 percent. If we do the same for this one, that disappears. And this one, our top 10 percent changes to these values. Okay so that's how easy it is to use conditional formatting. The one thing you need to keep in mind is that when you copy cells that have conditional formatting behind them you will take the conditional formatting with you. So be aware of this. So if I copy this area and paste it here and now let's go to conditional formatting options, I see the rules in there. And I may not want this. So don't forget to go there and clear the rules from the selected cells. If you want to copy this without conditional formatting, just copy, go here, Paste special, paste the formulas and Number Formats but not the conditional formatting. Okay. In the next lecture let's take a look at how we can use conditional formatting together with data bars and icons to bring attention to specific data points.