The Pandas library in Python has been predominantly used for data manipulation and analysis, but did you know that Pandas also allows for conditional formatting of DataFrames?
The tutorial explains the basics of Excel conditional formatting feature. You will learn how to create different formatting rules, how to do conditional formatting based on another cell, how to edit and copy your formatting rules in any version of Excel 2019, 2016, 2013, 2010 and earlier. Excel conditional formatting is a really powerful feature when it comes to applying different formats to data that meets certain conditions. It can help you highlight the most important information in your spreadsheets and identify variances of cells' values with a quick glance. At the same time, Conditional Formatting is often deemed as one of the most intricate and obscure Excel functions, especially by beginners. If you feel intimidated by this feature too, please don't! In fact, conditional formatting in Excel is very straightforward and easy to use, and you will make sure of this in just 5 minutes when you have finished reading this short tutorial. The basics of Excel conditional formattingThe same as usual cell formats, you use conditional formatting in Excel to format your data in different ways by changing cells' fill color, font color and border styles. The difference is that conditional formatting is more flexible, it allows you to format only the data that meets certain criteria, or conditions. You can apply conditional formatting to one or several cells, rows, columns or the entire table based on the cell contents or based on another cell's value. You do this by creating rules (conditions) where you define when and how the selected cells should be formatted. Where is conditional formatting in Excel?For a start, let's see where you can find the conditional formatting feature in different Excel versions. And the good news is that in all modern versions of Excel, conditional formatting resides in the same location, on the Home tab > Styles group. Conditional formatting in Excel 2007
Conditional formatting in Excel 2010
Conditional formatting in Excel 2013 - 2019
Now that you know where the conditional formatting feature is located in Excel, let's move on and see what format options you have and how you can create your own rules. How to create Excel conditional formatting rulesTo truly leverage the capabilities of conditional format in Excel, you have to learn how to create various rule types. This will help you make sense of whatever project you are currently working on. Conditional formatting rules in Excel define 2 key things:
I will show you how to apply conditional formatting in Excel 2010 because this seems to be the most popular version these days. However, the options are essentially the same in all Excel versions, so you won't have any problems with following no matter which version is installed on your computer.
Tips:
As you can see in the screenshot below, our newly created conditional formatting rule works right - it shades all the cells with a negative price change. Creating an Excel conditional formatting rule from scratchIf none of the ready-to-use formatting rules meets your needs, you can create a new one from scratch.
Excel conditional formatting based on cell's valueIn both of the previous examples, we created the formatting rules by entering the numbers. However, in some cases it makes more sense to base your condition format on a certain cell's value. The advantage if this approach is that no matter how that cell's value changes in the future, your conditional formatting will adjust automatically and reflect the data change. For instance, let's use the "Oil price" example again, but this time highlight all the prices in column B that are greater than February's price. You create the rule in a similar fashion by selecting Conditional formatting > Highlight Cells Rules > Greater Than... But instead of typing a number in step 4, you select cell B6 by clicking the range selection icon as you usually do in Excel. As the result, the prices get formatted as you see in the screenshot below.
This is the simplest example of Excel conditional formatting based on another cell. Other, more complex scenarios, may require the use of formulas. And you can find several examples of such formulas along with the step-by-step instructions in this article: How to change a cell color based another cell's value. This is how you do conditional formatting in Excel. Hopefully, these very simple rules we have just created was helpful to understand the general approach. Apply several conditional formatting rules to one cell / tableWhen using conditional formatting in Excel, you are not limited to only one rule per cell. You can apply as many rules as your project's logic requires. For example, let's create 3 rules for the weather table that will shade temperatures higher than 60 °F in yellow, higher than 70 °F in orange, and higher than 80 °F in red. You already know how to create Excel conditional format rules of this kind - by clicking Conditional Formatting > Highlight Cells rules > Greater than. However, for the rules to work correctly, you also need to set their priority in this way:
Using "Stop If True" in conditional formatting rulesWe have already used the Stop If True option in the example above to stop processing other rules when the first condition is met. That usage is very obvious and straightforward. Now let's consider two more examples where the use of the Stop If True function is not so obvious but also very helpful. Example 1. Show only some items of the icon set Suppose, you have added the following icon set to your sales report. It looks nice, but a bit inundated with graphics. So, our goal is to keep only the red down arrows to draw attention to the products performing below the average and get rid of all other icons. Let's see how you can do this:
Example 2. Remove conditional formatting from empty cells Suppose you created the "Between" rule to highlight cells' values between $0 and $1000, as you can see in the screenshot below. But the problem is that empty cells are also highlighted. To fix this, you create one more rule of the "Format only cells that contain" type. In the New Formatting rule dialog, select Blanks from the drop-down list. And again, you simply click OK without setting any format. Finally, open the Conditional Formatting Rule Manager and select the Stop if true check box next to the "Blanks" rule. The result is exactly as you would expect : ) How to edit conditional formatting rules in ExcelIf you've had a close look at the screenshot above, you probably noticed the Edit Rule... button there. So, if you want to change an existing formatting rule, proceed in this way:
If you want to apply the conditional format you have created earlier to other data on your worksheet, you won't need to create the rule from scratch. Simply use Format Painter to copy the existing conditional formatting to the new data set.
How to delete conditional formatting rulesI've saved the easiest part for last : ) To delete a rule, you can either:
Now you have basic knowledge of Excel conditional format. In the next article, we will focus on more advanced features that will help you push conditional formatting in your spreadsheets far beyond its traditional uses. And in the meantime, you may want to check out a few more examples of Excel conditional formatting: |