All the Things You Wanted to Know About Conditional Formatting in Excel

What are conditional formats? Simply put: They make a wall of data more readable. Tell me which dataset can you more quickly identify the best player:

Here’s a great example of a time where conditional formating helps a lot. We’re looking at the 2005 NFL Draft. It makes sense to organize it by the order the players were picked, but our emphasis is on Career Average Value (CarAV) and Drafted Team Average Value (DrAV) — two simple, but useful stats that Pro-Reference-Football.com provides to estiamte a player’s total worth.

The conditional formatting immediately draws our attention to DeMarcus Ware, Aaron Rodgers, and Logan Mankins — the three most valuable players from the first round. And the conditional formatting draws our attention to these players while also helping us notice — immediately — that neither Rodgers nor Mankins were top picks.

The NFL Draft is a great example of when conditional formatting helps most. If I just wanted to compare the Career AV numbers of all the players who entered the league in 2005, irrespective of draft location, then I’d probably make a simple list and sort it large to small. But when we want to preserve a specific order of the data — or want to represent multiple components at once — conditional formatting does a stalwart job.

Here’s another instance, this one unrelated to sports — my spreadsheet for looking for a second car on Craigslist:

Here, I care mostly about the Kelley Blue Book value of the vehicle (“KBB Value”), but I also want to know about gas mileage and the other facts of the car. I’ve set up the color coding, though, so that I don’t have to worry about 28 MPG throwing off a $4,000 asking price. Also, the higher the value of the car the more green it is, but the more expensive the asking price, the more red it is.

In other words, I just look for as much green as possible, and that’s my best bet.

So let’s talk about setting up our own table with conditional formatting. First, we need data pertinent for conditional formatting. Let’s go with 2014 NFL Team Efficiency stats from Football Outsiders.

First, I scrape the data with a little copy/paste action. Just highlight the table area in the middle and paste it into your Excel document. I recommend pasting without formatting. To do this, just right click on the spreadsheet and choose the special paste icon:

Now, after a little cleaning up — deleting those extra headers in the middle of the data, combining the two-line headers into a single line, and moving those headers above the appropriate column — we have something like this:

This is another ordinal setup — except we’re not looking at draft positions, but DVOA* rankings.

*DVOA stands for Defense-adjusted Value Over Average. It’s Football Outsider’s total value measurement, much like WAR is for Baseball-Reference and FanGraphs.

But one of the big problems with an ordinal ranking is that the space between No. 1 and No. 2 may not be the same as between No. 2 and No. 3. So conditional formatting helps us see tiers and groupings much more easily.

In order to add a conditional format to this data, we just need to highlight the C column and choose Conditional Formatting > Color Scales > the appropriate color scale.

Even though we highlighted the entire column, only the rows with data in them show the conditional format.

So that’s how you set up a basic conditional format! You can play around with the different format types and see which ones you like. There’s no harm in slapping a conditional format on top of another conditional format — as long as you have the same cells selected, it will just delete the old format and apply the new one.

But let’s say you have an issue like we have in Column I:

If you ever have a format that’s not working quite right, just click wherever the format is looking weird, and choose “Manage Rules…” from the Conditional Formatting drop down:

If you have the delinquent cells selected (and your Conditional Formatting Rules Manager is set to show “Current Selection” rules), you should see the formatting rules in the window:

If you double-click on the format name (“Data Bar” in this instance), you will open the “Edit Formatting Rule” window. This window (and the windows nested inside it) allow us to do a lot of fun stuff.

I’m presently happy with most of what’s going on in Column I, so I really only want to change two things: The positive color and the negative color. So in the Edit Formatting Rule window, I will change the bar color to red:

And then, right beneath that, I’m going to click the “Negative Value and Axis…” in order to change the red bars to green:

But let’s say I want to do something even more complicated. Let’s say I want to highlight the teams with bad special teams — and I don’t want to just highlight the special teams column. I want the whole row to broadcast the shame of their punters and kickers.

So I’m going to create a special conditional format. First, I’ll highlight the entire table other than the titles (from A2 to L33). Then, I’ll click Conditional Formatting > “New Rule…” to open the New Formatting Rule window.

So, because we want everything to look at the K column and change its format based on what’s in the K column, I’m going to write a formula that says:

=IF($K<0,1,0)

All this formula says is:

• =IF: This creates an IF formula. The syntax asks for (1) a formula that can be proved true or false, (2) a value for if the formula proves true, and (3) a value for if the formula proves false.
• $K2<0: I’m telling Excel to stay in Column K — that’s what the $ in front of $K means. So if the K columns is negative (<0), then the formula is true. The conditional formatting is going to start with the highest row in the selected area, so since our selection begins with A2, we’ll reference $K2 because that’s on the same row. (If we put $K3, it would look at the row beneath the current row.)
• 1,0: If the formula is true, then 1. If false, then 0. This tells Excel to apply the conditional format (1) if the formula is true (if Column K is negative), and to not format (0) if the formula is false.

After I enter the desired formula, I will set the formatting. Do whatever you want here. Change the fill. Change the font color. Make it bold. The pop up in the “Format…” window is just a typical Excel formatting window, so it should be easy to navigate.

I went ahead and set the format to a dark red fill and a bold white font. This will make the formatted rows very obvious, but also make the the table really busy visually — but I’m doing this for the learning, not for the beauty.

Anyway, we get this:

Let’s do one final this: Change format priorities. Notice how our conditional formatting in Column C is getting smushed by our new special teams formatting? Well, that’s no good.

So let’s open the “Manage Rules…” dialogue. then, look at the formatting rules for “This Worksheet”:

Then, with the special teams format select, let’s press the down button (note the second red arrow above) until it’s at the bottom of the list. Hit Apply or OK and you should get something like this:

This is a good cautionary tale about changing font colors and formats. Since we’re using a simple conditional format for Column C (as in, not a formula-based format), we can’t edit the fonts in that column. So the only real solution is to change the special teams font — or to selectively apply that format.

The second option is simple enough. Just open the “Manage Rules…” window and change the selection area for the format:

All we need to do is change the “Applies to” textbox. Once we select the A2 to B33 area and the D2 to L33 area, the weird formatting disappears from Column C.

The final step to using conditional formatting is presenting that data. Microsoft has done a good job catching up with Google Drive and the sort, allowing us to post table and the sort online. Using File > “Save & Send” should direct you to the SkyDrive services that will enable you to post the spreadsheet online. If there’s enough interest, I can walk through the process for this as well.

Anyway, I hope this has been interesting and useful. Happy Exceling!