Skip to content

TechRepublic

Trending Now

Businessman holds the model of business, made from wood blocks. Alternative risk concept, business plan and business strategy. Insurance concept.
Image: Michail Petrov/Adobe Stock
Article

Managing the element of risk in projects

Ransomware infects a computer's system.
Credit: Adobe
Article

How traditional security tools fail to protect companies against ransomware

featuremicrosoft461682196.jpg

Image: Microsoft News

Article

Tech News You May Have Missed: June 23-30

project team collaboration.jpg
Article

Collaborating across IT operations for a stronger enterprise environment

  • 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

Account Information

Join or sign in

Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.

Use Facebook
Use Linkedin

Join or sign in

We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.

Welcome back!

Invalid email/username and password combination supplied.

Reset password

An email has been sent to you with instructions on how to reset your password.

Welcome to TechRepublic!

All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).

Loading
Software

10 creative uses for Excel’s conditional formatting feature

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

Image
1
of 23
Previous Next

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

    Industrial Internet of Things: Software comparison tool

    IIoT software assists manufacturers and other industrial operations with configuring, managing and monitoring connected devices. A good IoT solution requires capabilities ranging from designing and delivering connected products to collecting and analyzing system data once in the field. Each IIoT use case has its own diverse set of requirements, but there are key capabilities and ...

    Downloads
    Published:  May 26, 2022, 5:00 PM PDT Modified:  May 28, 2022, 8:00 AM PDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    How to recruit and hire an Operations Research Analyst

    Recruiting an Operations Research Analyst with the right combination of technical expertise and experience will require a comprehensive screening process. This Hiring Kit provides an adjustable framework your business can use to find, recruit and ultimately hire the right person for the job.This hiring kit from TechRepublic Premium includes a job description, sample interview questions ...

    Downloads
    Published:  May 19, 2022, 5:00 PM PDT Modified:  May 21, 2022, 12:00 PM PDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Quick glossary: Industrial Internet of Things

    The digital transformation required by implementing the industrial Internet of Things (IIoT) is a radical change from business as usual. This quick glossary of 30 terms and concepts relating to IIoT will help you get a handle on what IIoT is and what it can do for your business.. From the glossary’s introduction: While the ...

    Downloads
    Published:  May 19, 2022, 5:00 PM PDT Modified:  May 21, 2022, 12:00 PM PDT Read More See more TechRepublic Premium
  • TechRepublic Premium

    Software Procurement Policy

    Procuring software packages for an organization is a complicated process that involves more than just technological knowledge. There are financial and support aspects to consider, proof of concepts to evaluate and vendor negotiations to handle. Navigating through the details of an RFP alone can be challenging, so use TechRepublic Premium’s Software Procurement Policy to establish ...

    Published:  April 14, 2022, 5:00 PM PDT Modified:  April 16, 2022, 1:00 PM PDT 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
© 2022 TechnologyAdvice. All rights reserved.
  • Privacy Policy
  • Terms of Use
  • Property of TechnologyAdvice