Cool Ways to Use Conditional Formatting
Wouldn’t it be cool if you could automatically highlight certain words or numbers on your spreadsheet? Well you can with Conditional Formatting! Conditional formatting is exactly what it sounds like, formatting based on certain conditions or criteria. With conditional formatting, you can change the font color, font style, border style, or background color, every time a word or number appears on your spreadsheet. It’s a really easy way to make important things visually stand out.
Say you’re working on a budget like the picture below, and want to see where you were overspending. You could use conditional formatting to highlight any negative differences in red.
Download my budget example to follow along.
You probably already know that you can manually select each negative number in the difference column, and format them individually. But why do that when there is a much faster way?
Here’s how to do it automatically with conditional formatting. First select the entire area (the difference column) that you want to format.
Then, go to the Conditional Formatting button on the Ribbon, and select New Rule.
Where it says Select a Rule Type, select “Format only cells that contain.” Next, enter the following parameters in the three boxes, “Cell Value,” “less than,” and “0.” When you click the Format button, you will choose the formatting options you want. In this example, we chose a light pink background and dark red font.
Click OK, and now all your negative differences are highlighted in red!
But what if you wanted to conditionally format the entire row from from column C to F instead of just the cell in column F? Well, you can do that too! This time, select your entire data section like this.
Go to the Conditional Formatting button and select New Rule again, but this time select “Use a formula to determine which cells to format” under Rule Type. In the formula box, type in “=$F3<0" (I will explain what this means below). Then click the Format button and choose your formatting.
Now, whenever there is a negative value in F, the entire row from C to F is highlighted!
So what does the “=$F3<0" mean? The formula says if the cell F3 is a negative number, apply conditional formatting to the area you've selected (which is the range C3:F32). The conditional formatting formula you entered is technically different for each row in your data set. Because you put a "$" in front of the "F", the formula is always looking to see if the value in column F is negative. But because there is no "$" in front of the "3" however, the formula is changing for each row. For row 3 it's looking at F3, for row 4 it's looking at F4, for row 5 it's looking at F5, etc. But why did you enter the "3" in the formula if it's not actually looking at row 3 every time, you might wonder. That is a very good question, and the short/simplified answer is that when you selected your data range, you started with the third row. If you started with the fourth row, you would need to change the formula to "=$F4<0," and if you started with the fifth row, the formula would be "=$F5<0," and so on. With no "$" in front of the row number, you are telling Excel to dynamically change this part of the formula to the corresponding row number. So those are two neat ways to use conditionally formatting. Play with it and I'm sure you will think of many more cool ways to use it!