Excel Tip: Conditional Formatting

Today I’m going to walk you through one example of a common use I have for conditional formatting in Excel. There are many applications of the conditional formatting function, but my hope is that this one example will help you with understanding how to utilize it.

While working in financial statements and various analysis spreadsheets, I often want to display variations in color: negative variances in red, positive in green. In this example, we will use +100 and -100. I am going to highlight the cells, click on “Conditional Formatting” in the ribbon bar, then click on “New Rule”

Next, I will choose the rule type: “Format only cells that contain.” Choose “Cell Value – greater than” and enter “0” as the value. I will then format this rule and do the fill in a green color and click OK. Click OK again, and now you see that the +100 is formatted in green.

Then I will create another rule, but this time I’ll choose a “less than” cell value, with zero as the value. This fill will be formatted to be red (or more of a pink so you can see the text). When you get back to your spreadsheet, you won’t see any change.

Next, I will right-click on one of the cells we just applied the conditional formatting to, click the format painter, and then highlight ALL the cells we want to have the same conditional formatting applied to. Notice how they are all formatted now.

If you’d like me to discuss a different type of conditional formatting in a future blog, please comment below, or CONTACT ME for personalized help.


Leave a Reply

Your email address will not be published. Required fields are marked *