Skip to content

TechRepublic

  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Accounting
  • CRM
  • Academy
Resources
  • TechRepublic Premium
  • TechRepublic Academy
  • Newsletters
  • Resource Library
  • Forums
  • Sponsored
Go Premium
Popular Topics
  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Accounting
  • CRM
  • Academy
  • Project Management
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
View All Topics
Go Premium
Software

10 creative uses for Excel’s conditional formatting feature

By Susan Harkins April 11, 2012, 5:25 AM PDT

Image
1
of 23

00conditional_fig.jpg
00conditional_fig.jpg
10 creative uses for Excel’s conditional formatting feature

Introduction

Introduction

ntConditional formatting lets you apply specified formatting only when certain conditions are met. Here are some creative ways you can push conditional formatting beyond its expected uses. These techniques assume a basic knowledge of Excel’s conditional formatting feature.

n

nt

n

ntNote: If you’d prefer to view this information as a blog post, check out this entry in our Five Apps blog. You can also download sample Excel files that demonstrate the techniques covered here.

n

ntPhoto: iStockphoto.com/NadyaPhoto

10 creative uses for Excel’s conditional formatting feature

Distinguish business rule violations

Distinguish business rule violations

ntUsing conditional formatting, you can spot when something is breaking a business rule. For example, this figure shows a simple timekeeping sheet that highlights a workday that exceeds eight hours. Why? Because your organization requires approval for anything over an eight-hour day.

10 creative uses for Excel’s conditional formatting feature

Distinguish business rule violations

Distinguish business rule violations

ntAs you can see here, working with the time values complicates things a bit. This solution uses >.34 to represent time values greater than eight hours, which will work in most cases — you can’t use the value 8 or even the time value 8:00. You can also use the predefined Greater Than rule in Excel 2007 and 2010, which will automatically use the more accurate value of 0.333333….

10 creative uses for Excel’s conditional formatting feature

Display simple icons

Display simple icons

ntUsing conditional formatting (in 2007 and 2010), you can display icons that may be easier to interpret than the values they represent. For instance, a simple checkmark might be quicker to discern than the text value yes, on, true, and so on. Here we have an icon solution for the rule violation we saw earlier.

10 creative uses for Excel’s conditional formatting feature

Display simple icons

Display simple icons

ntTo produce this effect, select the values in column E and apply one of the default icon sets. Then, use Manage Rules to manipulate the results. Make sure you click Reverse Icon Order first.

10 creative uses for Excel’s conditional formatting feature

Highlight a row based on a single value

Highlight a row based on a single value

ntFilters are great for limiting what you see, but sometimes you want to compare records. When this is the case, conditional formats can distinguish records. Here’s a data set of products with a conditional format highlighting only Condiment records.

10 creative uses for Excel’s conditional formatting feature

Highlight a row based on a single value

Highlight a row based on a single value

ntSelect the entire data range (not the column headings) so Excel can format the entire record (row). The figure above shows the formula-based settings. The $G2 component creates a relative address, which updates with each row: G4, G5, G6, and so on. When the value in the referenced cell equals the string “Condiment,” Excel highlights the entire row.

10 creative uses for Excel’s conditional formatting feature

Create a dynamic record highlight

Create a dynamic record highlight

ntHighlighting an entire record is convenient. But what if you want the conditional format to be more… conditional? For instance, suppose you want users to choose the category on the fly.

10 creative uses for Excel’s conditional formatting feature

Create a dynamic record highlight

Create a dynamic record highlight

ntFirst, use the Advanced Filter feature to copy a unique list to an out-of-the-way spot. Then, use the Data Validation feature to create a list.

10 creative uses for Excel’s conditional formatting feature

Create a dynamic record highlight

Create a dynamic record highlight

ntWith the list in place, update the conditional format formula to reference the input list cell. Instead of referencing a cell within the row, the formula references the validation list in B2. Selecting an item from the validation list updates the conditional formatting.

10 creative uses for Excel’s conditional formatting feature

Compare values

Compare values

ntSometimes, you need to compare values. For instance, you might track inventory levels by comparing the stock on hand to a reorder level. Using conditional formatting, you can alert users when it’s time to reorder.

10 creative uses for Excel’s conditional formatting feature

Compare values

Compare values

ntSelect the values you want to format — in this case, that’s B2:B46. (You could highlight the entire row or one of the inventory values.) Then, apply the format shown here.

10 creative uses for Excel’s conditional formatting feature

Compare lists

Compare lists

ntYou can find discrepancies between two lists using a conditional formatting rule.

10 creative uses for Excel’s conditional formatting feature

Compare lists

Compare lists

ntThis rule compares each value in column A to its counterpart in column B. If they’re not the same, Excel highlights the value in column A. To highlight the values in column B instead, select the values in column B and update the rule formula to reference the values in column A.

10 creative uses for Excel’s conditional formatting feature

Create alternating bands

Create alternating bands

ntMany sheets highlight every other row (banding) to improve readability. The Table feature offers several predefined formats that include bands, but you end up with a table instead of a plain data set. When you don’t want a table, use conditional formatting to create alternating bands.

10 creative uses for Excel’s conditional formatting feature

Create alternating bands

Create alternating bands

ntThe rule shown here highlights cells to achieve the alternate band effect.

10 creative uses for Excel’s conditional formatting feature

Find duplicates

Find duplicates

ntTo find duplicate values or records, you can use a filter, but conditional formatting can pinpoint duplicate values on the fly. For instance, this sheet shows duplicate values in a single column.

10 creative uses for Excel’s conditional formatting feature

Find duplicates

Find duplicates

ntSelect the values you want to format and apply the formula-based rule shown here.

10 creative uses for Excel’s conditional formatting feature

Find duplicates

Find duplicates

ntTo ignore the first occurrence and highlight only subsequent values, use this formula:

n

=COUNTIF($A$2:$A2,A2)>1

n

ntIf you want to check for duplicate values across multiple columns, concatenate the values and apply a similar rule to the results, as shown in the figure above. You can also hide duplicates (which I don’t always recommend) by selecting a font color that matches the sheet’s background.

10 creative uses for Excel’s conditional formatting feature

Find discrepancies

Find discrepancies

ntVerifying data is an important task, and Excel’s conditional formatting can help by alerting you to inconsistencies. The figure above shows a common accounting tool known as cross-footing — the process of double-checking totals by comparing subtotals across rows and columns — in cell F16. Adding the conditional format makes the discrepancy hard to miss when the two totaling values don’t match.

10 creative uses for Excel’s conditional formatting feature

Find discrepancies

Find discrepancies

ntSelect either of the cross-foot formulas and apply the rule shown here.

10 creative uses for Excel’s conditional formatting feature

Find the smallest or largest duplicate

Find the smallest or largest duplicate

ntAs you can see above, the value 3 is the smallest value in the column, but Excel highlights each occurrence of the value 5.

10 creative uses for Excel’s conditional formatting feature

Find the smallest or largest duplicate

Find the smallest or largest duplicate

ntThis rule is unstable if you use normal referencing, so apply a range name to your data set before applying the conditional formatting rule. The rule shown here will highlight the value 3 in the range named List only if 3 is also a duplicate. (To find the largest duplicate value, substitute the MIN() function with MAX().)

  • Software
  • Account Information

    Share with Your Friends

    10 creative uses for Excel’s conditional formatting feature

    Your email has been sent

Share: 10 creative uses for Excel's conditional formatting feature
Image of Susan Harkins
By Susan Harkins
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.
  • Account Information

    Contact Susan Harkins

    Your message has been sent

  • |
  • See all of Susan's content

Daily Tech Insider

If you can only read one tech story a day, this is it.

TechRepublic TechRepublic
  • TechRepublic on Facebook
  • TechRepublic on X
  • TechRepublic on LinkedIn
  • TechRepublic on YouTube
  • TechRepublic on Pinterest
  • TechRepublic RSS
Services
  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information
  • Careers
Explore
  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • Editorial Policy
  • Legal Terms
  • Privacy Policy
© 2025 TechnologyAdvice. All rights reserved.
CLOSE

Create a TechRepublic Account

Get the web's best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let's start with the basics.

Already registered? Sign In
Use Facebook
Use Linkedin

* - indicates required fields

CLOSE

Sign in to TechRepublic

Not a member? Create an account
Use Facebook
Use Linkedin

Lost your password? Request a new password

CLOSE

Reset Password

Please enter your email adress. You will receive an email message with instructions on how to reset your password.

Check your email for a password reset link. If you didn't receive an email don't forgot to check your spam folder, otherwise contact support.

Back to login
1 Finish Profile
2 Newsletter Preferences
CLOSE

Welcome. Tell us a little bit about you.

This will help us provide you with customized content.

No thanks, continue without
1 Finish Profile
2 Newsletter Preferences
CLOSE

Want to receive more TechRepublic news?

Newsletter Name
Subscribe
Daily Tech Insider
Daily Tech Insider AU
TechRepublic UK
TechRepublic News and Special Offers
TechRepublic News and Special Offers International
Executive Briefing
Innovation Insider
Project Management Insider
Microsoft Weekly
Cloud Insider
Data Insider
Developer Insider
TechRepublic Premium
Apple Weekly
Cybersecurity Insider
Google Weekly
Toggle All
No thanks, continue without

You're All Set

Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add [email protected] to your contacts list.

Back to Home Page
×