Skip to content

TechRepublic

  • Top Products Lists
  • Developer
  • 5G
  • Security
  • Cloud
  • Artificial Intelligence
  • Tech & Work
  • Mobility
  • Big Data
  • Innovation
  • Cheat Sheets
  • TechRepublic Academy
  • CES
  • TechRepublic Premium
  • Top Products Lists
  • Developer
  • 5G
  • Security
  • Cloud
  • Artificial Intelligence
  • Tech & Work
  • Mobility
  • Big Data
  • Innovation
  • Cheat Sheets
  • TechRepublic Academy
  • CES
  • See All Topics
  • Sponsored
  • Newsletters
  • Forums
  • Resource Library
TechRepublic Premium
Join / Sign In
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

TechRepublic Premium

  • TechRepublic Premium

    Hiring kit: Back-end developer

    PURPOSE Finding a back-end developer with programming and technical expertise as well as superior collaboration and communication skills will require a comprehensive recruitment strategy. This Hiring Kit from TechRepublic Premium provides an adjustable framework your business can use to find, recruit and ultimately hire the right person for the job. From the hiring kit: TYPICAL ...

    Published:  March 21, 2023, 12:00 PM EDT Modified:  March 22, 2023, 3:00 PM EDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Hiring kit: Application engineer

    PURPOSE Application engineers need to have technical expertise in programming, design, business and the software and hardware required to run the application. This Hiring Kit from TechRepublic Premium provides an adjustable framework your business can use to find, recruit and ultimately hire the right person for the job. From the hiring kit: DETERMINING FACTORS, DESIRABLE ...

    Published:  March 21, 2023, 12:00 PM EDT Modified:  March 22, 2023, 3:00 PM EDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Employee performance review policy

    PURPOSE The purpose of this policy from TechRepublic Premium is to provide guidelines for employee performance reviews, which will clearly document accomplishments and areas of opportunity via an encouraging and thought-provoking analysis. This will assist in determining the next steps for employees as well as their future at the organization. This policy can be customized ...

    Published:  March 20, 2023, 12:00 PM EDT Modified:  March 21, 2023, 4:00 PM EDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Employee time off policy

    PURPOSE The purpose of this policy from TechRepublic Premium is to provide guidelines for requesting, filing and permitting paid/unpaid time off as well as to ensure coverage during holidays, vacation(s) and other absences where staffing levels must be consistent to meet the needs of the business. From the policy: TIME OFF GUIDELINES All time off ...

    Published:  March 20, 2023, 12:00 PM EDT Modified:  March 21, 2023, 4:00 PM EDT Read More See more TechRepublic Premium

Services

  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information

Explore

  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • TechRepublic
  • TechRepublic on Twitter
  • TechRepublic on Facebook
  • TechRepublic on LinkedIn
  • TechRepublic on Flipboard
© 2023 TechnologyAdvice. All rights reserved.
  • Privacy Policy
  • Terms of Use
  • Property of TechnologyAdvice