How To Go Picasso On Your Data With Conditional Formatting

conditional formatting in ExcelAnyone who knows me knows I’m not a fan of tabular data (data in tables). If you want your data to be persuasive, it needs to be visual.

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.

Data Bars

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.

data bars in Excel

 

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.

data bar menu in Excel

Click for larger image

 

Icon Sets

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).

icon sets in Excel

 

Here’s how it looks in your data:

icon set example in Excel

 

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.

ordering data by icon sets

 

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.

custom icon sets in Excel

 

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
  • Between
  • 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.

Top/Bottom Rules

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.

Color Scales

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.

Using Formulas

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.

Learn More

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.

Opinions expressed in the article are those of the guest author and not necessarily Search Engine Land.

Related Topics: Channel: Analytics | Search & Analytics

Sponsored


About The Author: is an SEO and analytics consultant. Her areas of expertise are analytics, technical SEO, and everything to do with data — collection, analysis, and beautification. She’s on a mission to rid the world of ugly data, one spreadsheet at a time. If you just can’t get enough data visualization tips, you can check out her blog, Annielytics.com.

Connect with the author via: Email | Twitter | Google+ | LinkedIn



SearchCap:

Get all the top search stories emailed daily!  

Share

Other ways to share:

Read before commenting! We welcome constructive comments and allow any that meet our common sense criteria. This means being respectful and polite to others. It means providing helpful information that contributes to a story or discussion. It means leaving links only that substantially add further to a discussion. Comments using foul language, being disrespectful to others or otherwise violating what we believe are common sense standards of discussion will be deleted. Comments may also be removed if they are posted from anonymous accounts. You can read more about our comments policy here.
  • http://twitter.com/victorpan victorpan

    Love the guide, I’ve made one myself in the past – but the file size/load times of these things can get horrendous. Any pro tips on that?

  • http://twitter.com/bnspak Ben Spak

    Works perfect with the new keyword swing technique I learned, thanks Annie ^_^

  • http://twitter.com/AnnieCushing Annie Cushing

    I’ve seen people use conditional formatting for data that was clearly static (like making negative numbers red). So I try to avoid that and addressed how to use static formatting in this post: http://searchengineland.com/easy-to-advanced-uses-of-cell-formatting-in-excel-130203. Other than that, yeah, there’s really nothing you can do, except allot Excel more memory.

  • http://twitter.com/AnnieCushing Annie Cushing

    Keyword swing technique?

  • http://twitter.com/bnspak Ben Spak

    Keyword Swing – SEOmoz YouTube Contest Winner
    http://www.youtube.com/watch?v=Xfet4h2y-UI

 

Get Our News, Everywhere!

Daily Email:

Follow Search Engine Land on Twitter @sengineland Like Search Engine Land on Facebook Follow Search Engine Land on Google+ Get the Search Engine Land Feed Connect with Search Engine Land on LinkedIn Check out our Tumblr! See us on Pinterest

 
 

Click to watch SMX conference video

Join us at one of our SMX or MarTech events:

United States

Europe

Australia & China

Learn more about: SMX | MarTech


Free Daily Search News Recap!

SearchCap is a once-per-day newsletter update - sign up below and get the news delivered to you!

 


 

Search Engine Land Periodic Table of SEO Success Factors

Get Your Copy
Read The Full SEO Guide