How To Go Picasso On Your Data With Conditional Formatting
However, when you’re dealing with large data sets that’s not always feasible. For example, when I’m dealing with data from webmaster tools or Screaming Frog, there’s just too much data to analyze with charts.
That’s where conditional formatting comes in handy. Unlike custom cell formatting, which is static, conditional formatting is based on — you guessed it — conditions that you set.
You can find the Conditional Formatting options on the Home Tab under the Styles group (Mac: Home > Format). I can’t possibly cover all the great uses of conditional formatting, but I hope to at least whet your appetite and make you aware of what they can do. Then you can take these tips and run with them.
That said, let’s jump in.
The easiest way to get started with conditional formatting is with data bars. You can find them under Conditional Formatting > Data Bars.
To activate them, just click the column you want to apply them to, then choose the data bar color you want from the flyout menu.
Excel allows you to customize your data bars by clicking More Rules… at the bottom of the flyout menu.
For example, if you have outliers in your data that are throwing the data bars off, you can throw them out by adjusting the Minimum and Maximum values.
You can also opt to only show data bars, toggle between solid bars and gradient fills, change the color, format how you want negative numbers to look, set the border style, and change the bar direction from the New Formatting Rule dialog, as seen below.
Another popular conditional formatting option is icon sets (Conditional Formatting > Icon Sets). Their default options are pretty lame, but I like to customize them to show things like keywords that are ranking on page 1, page 2, and page 3 and beyond.
In the example below, I used the 3 Traffic Lights (Rimmed). But since I know Excel is going to break my data up by top 1/3, middle 1/3, and lower 1/3 — instead of by 1-10, 11-20, and 21+ — we’ll choose More Rules at the bottom of the flyout menu instead of clicking on the traffic lights.
Here are the steps to setting up the formatting options:
Step 1: Change the Icon Style to the traffic lights.
Step 2: Click the Reverse Icon Order option (since in the case of rankings lower numbers are preferable and should be visualized with green).
Step 3: Change Type to Number for both green and yellow. (It’s easy to forget to the do this, which is why I do it before touching the values. If you forget you’ll just have to take a couple extra steps later.)
Step 4: Set the red icon’s Value to >= 21 (or > 20) since this marks the top of page 3.
Step 5: Set the yellow icon’s Value to >=11 (or > 10).
Here’s how it looks in your data:
If you’ve used table formatting you can sort your data by the conditional formatting using the filter drop-down. Just click the down-facing arrow in the column heading you want to sort your table by and then choose Sort by Color > Custom Sort (Mac: Sort > By Color > Cell Icon > Custom Sort).
Here’s how you would set up a sort that puts all of Google’s page 1 keywords on top, followed by page 2, then page 3 and beyond.
Pro Tip: You’re not limited to the icon set combinations that come baked into Excel. For example, let’s say you want a green arrow for values greater than 1% and a red arrow for values less than -1%, but you don’t want the yellow arrow for values between -1% and 1%.
You can choose the icon set you want — in this case, the Three Arrows (Colored) option — but then choose No Cell Icon from the drop-down menu in lieu of the yellow arrow.
The Icon Style menu will update to say Custom instead of showing the three arrows. Theoretically, you could mix and match icons, but I wouldn’t recommend getting too off the chain with mixing icon types. It’s the data equivalent of littering your front lawn with garden gnomes.
Highlight Cell Rules
Excel gives you a number of options to format your data based on the contents of the cells in a data set — either a single column or row or an entire table. You have seven built-in options under Conditional Formatting > Highlight Cell Rules:
- Greater Than
- Less Than
- Equal To
- Text That Contains
- A Date Occurring
- Duplicate Values
I use the greater than and less than options a lot in reports that show values over time, like rank reports, month-over-month revenue, year-over-year visits, etc. I format values that move in a negative direction with red font and those that move in a positive direction with green font. I find that to be less distracting for larger data sets than having a bunch of red, yellow, and green arrows (which are an option under icon sets if you’re so inclined).
The text option is particularly useful for tasks like highlighting keywords that contain a certain word, landing pages from a particular directory, 404 status codes from Screaming Frog, etc.
I also use the duplicate values a lot when I download a Screaming Frog report to highlight duplicate content. Just apply this conditional formatting option to the Hash column. Then you can sort by that cell formatting, followed by an alphabetical sort (using the same custom sort options available to you from the table filter for that column). This puts all the duplicates at the top of the column and groups the pages with the same hash value together.
Excel gives you six built-in rules:
- Top 10 Items
- Top 10%
- Bottom 10 Items
- Bottom 10%
- Above Average
- Below Average
Of course, you’re not limited to these rules. If you select More Rules, you can set your own thresholds.
The color scales work exactly like the data bars. Personally, I don’t prefer them because I find that the distinctions get a little difficult to read and interpret, especially with larger data sets. But if you like them, knock yourself out.
As you get more comfortable with conditional formatting, using the formula option will save you some time. You’ll also need to use the formula option if you want to format the cells in one column based on the values in another.
For example, if you want to apply yellow highlighting to all landing page cells if they generated at least $5,000 in revenue, according to your analytics data, you’d use a formula because you’re not formatting the same cell you’ve selected.
Another example would be if you’re using a spreadsheet to keep track of your link building and want to highlight an entire row green if the link has been published and red if it hasn’t (using a Published column that has either Yes or No as the cell value), you would use a formula to get the job done.
Don’t let working with formulas intimidate you. If you’ve used formulas elsewhere in Excel, they’re very easy to apply in conditional formatting. The trick to using them is if the formula evaluated to a true condition, the cell(s) will be formatted; if it evaluates to false, they won’t.
For example, let’s say you have revenue data in column E (starting in row 4), and you want to highlight landing pages that have generated at least $5,000 in revenue. Your formula would read =E4>=5000. (Don’t let that initial = throw you. It doesn’t mean equal in this context; it just means you’re entering a formula.)
Also, you’re not limited to just one condition. Let’s say you have an organic landing page report with analytics data, and you had your data broken up like this:
- Column B: Visits
- Column C: Revenue
- Column D: Bounce Rate
Now, if you wanted to highlight your strongest landing pages, you could decide the thresholds for each metric and apply those against your data.
For example, let’s say you want to highlight landing pages that meet these criteria:
- Received at least 100 visits
- Generated at least $5,000
- Had a bounce rate under 30%
Here are the steps you would take:
Step 1: Select the cell with the first landing page.
Step 2: Go to Conditional Formatting >New Rule.
Step 3: Under Select a Rule Type, choose Use a formula to determine which cells to format (Mac: Choose Classic from the Style drop-down menu, then use a formula to determine which cells to format from the [unlabeled] drop-down menu below it).
Step 4: Assuming your data begins in the Row 4, you would enter this formula in the formula field: =AND(B4>=100, C4>=5000, D4<30%) Tip: You could also use .3 for bounce rate. The AND function just allows you to concatenate (or join together) more than one condition. You could even use OR to format a cell based on one condition or another.
Step 5: Choose the formatting option(s) you want by clicking on the Format button.
Step 6: Take the fill handle in the bottom-right corner of the cell and drag it down (or double-click on it if you’re using a formatted table) to apply it to the entire column. Then choose Fill Formatting Only from the Auto Fill Options menu that pops up.
Alternatively, you could select the first cell, click the Format Painter under Home > Clipboard and click-and-drag over the rest of the cells in the column to apply the format. If all of the landing pages have identical formatting, make sure you’re not using absolute references in your formula, e.g., =AND($B$4>=100, $C$4>=5000, $D$4<30%) because your formula will only reference the first record (or row) in your data set.
However, you could lock just the letters, if you wanted to be precise (e.g., $B4), but it’s not necessary in this case. To learn more about absolute and relative referencing, check out this article from the Microsoft website.
Modifying Your Conditional Formatting
If you ever need to modify your conditional formatting, all you have to do is go to Conditional Formatting > Manage Rules, then just double-click on the rule you want to edit or click the Edit Rule button.
Since your conditional formatting is applied in the order of the rules listed in Rules Manager dialog, you can also move the rules up or down using the arrow buttons. Furthermore, you can tell Excel to stop if a particular rule evaluates to true. To learn more about rule precedence, check out this article from the Microsoft website.
If you want to develop advanced conditional formatting skills, you’re not going to do that from one blog post. However, I read an ebook a couple years ago that made the more advanced conditional formatting options click for me. I don’t usually promote resources that aren’t free and I don’t benefit in any way from sales, but my $20 investment has paid off in spades by giving me the skills to wield the full power of Excel’s conditional formatting. You can check out the ebook here.
If you have any questions or get stuck on anything, feel free to reach out to me on Twitter. I help a lot of people in the industry with their spreadsheet woes. :)
Photo from keaggy.com. Used under Creative Commons license.
Analytics news and expert advice every Thursday.