I started appreciating the wonders of conditional formatting when I was working in Ad Operations and in charge of a daily delivery report, which was basically a giant data dump on all currently live ad units. It was my job to parse through the data and figure out which units were at risk of underdelivering (and losing us money!) so we could take action to prevent it. But at any given time, there are hundreds of ad units running, and what was I going to do, read through them row by row?!
That’s where conditional formatting came to my rescue. I used conditional formatting to highlight the rows that were most important, with different colors to signal varying priority levels, based on how much money was at risk and how long we had to fix it. So out of hundreds of rows, I could quickly and easily find and focus on the 12 that needed attention.
I’ll go over the basics of working with conditional formatting here, and then you can check out my next post on some advanced topics so you can become a true conditional formatting master. You’ll never have to stare blindly at too much data again!
disclaimer: none of the data pictured is real: I had some help from the handy RAND() function and my genius fake ad campaign naming abilities.
Conditional formatting in Excel allows you to format cells based on their value, allowing you to quickly and easily identify the data that’s important in your report. Sure, you could filter or sort the data and highlight rows or cells manually, but then what if the data ever changed? You would have to remember to go back and reformat the data each time, which is obviously not scalable and also very error-prone. Plus, you’re better than that.
Setting Up a Conditional Formatting Rule
Excel makes conditional formatting fairly painless - there are a lot of built in, easy to use options, which allow you to do things like:
- Highlight cells with values greater or less than a certain number (ex: cells with values > 100)
- Highlight cells that contain certain text (any cell that says “Monday”)
- Highlight the 10 cells with the highest or lowest values in range of cells
- Shade cells based off their value (low values are red, high values are green)
To use these, all you have to do is select the range of cells you want formatted, and then click on the Conditional Formatting button in the Home section of your ribbon (or alternatively Format > Conditional Formatting in the top nav bar.) From here you can choose from a bunch of options.
Once you choose a rule type (such as Highlight Cell Rules > Less Than, as pictured below), you’ll be able to set any specifics for your formatting rule, plus choose how you want your cells to look if they match your criteria. The below will make the cells that are less than .5 have a light red fill and dark red text.
If you aren’t happy with any of the formatting options in the “Format with:” dropdown, you can customize the cell format with the “custom format” option, where you’ll be able to set the font properties, fill and border of cells matching your criteria.
You can set all sorts of crazy custom formats this way. In the below, I formatted cells in column B that contained the text “300x250” to be orange, with dark orange bolded text and a green border, because I have a really keen eye for design, as you can see.
Deleting Conditional Formatting Rules
What if an hour later I realize that orange was so not the right color for this spreadsheet. Luckily, you can always clear or edit any rule you create!
In the dropdown of the Conditional Format button on the Home ribbon, there is a “Manage Rules” option that triggers a new window to open, listing out all of the rules you’ve set in your spreadsheet. From there you can delete, change or re-prioritize your rules.
Manage Rules defaults to showing only the rules that pertain to the cells that are currently selected. I clicked away from column B (where I had set up my orange rule), so no rules were showing up when I opened the Manage Rules window. But I can change the top drop down so that it displays all rules in my current sheet by selecting “This Sheet”
Now that I’ve found and selected my orange rule, I can either:
- Delete it, by clicking the negative sign in the bottom left-hand corner of the “Manage Rules” window
- Modify it, by clicking “Edit Rule…”, which is right next to the negative sign. I can change the specifics of the rule itself (format cells containing the text “970x250" instead of “300x250") or the formatting.
- Re-prioritize it, by clicking the up or down arrows in the top right to change the rules position in the list. Excel allows you to set multiple formatting conditions on a range of cells, and if a cell fits more than one rule criteria, it will get formatted based off the first rule in the list that it matched (ex: if I had a second rule below my orange rule, that turned cells in column B blue if they contained the text “Video”, the cell “Emotional Video 300x250", which matches both rules, would be highlighted orange because that rule has priority over the blue rule.)
Now go off and play around with conditional formatting! And then check out the second part of this series, where I go over using custom formulas to create more complex formatting rules.