Productivity Portfolio

Valuing your time & technology

  • Home
  • Tutorials
    • Excel
    • Google
    • Word
    • Terms
  • Quick Tips
    • Cell
    • Email
    • Internet of Things
    • Security
    • Web
    • Windows
  • Reviews
    • Health
    • Hardware
    • Services
    • Software Reviews
  • About
    • About Us
    • Contact Page
    • Cookie Policy
    • Privacy Policy
  • Newsletter
  • Resources
    • Books
    • Favorite Podcasts
    • Gadgets
    • Online Courses
    • Online Services
You are here: Home / Tutorials / Excel / Highlight Important Data with Excel Conditional Formatting

Highlight Important Data with Excel Conditional Formatting

Last Updated on 26-Mar-2018 by Anne Hennegar

Walk into most offices and you’re apt to find someone using an Excel spreadsheet. It’s one of those common business tools. The problem with many spreadsheets is they make the reader find the key information rather than highlight key items. Fortunately, Excel conditional formatting can make your data automatically stand out with a few steps. (See sample spreadsheet in the Resources section.)

I see it happen all the time. You get a spreadsheet from someone with rows of data and it all looks the same. But is the data the same? Are there cell values that are different from the rest? Is something outside the norm? These are the type of questions Excel users have when they see spreadsheets. The problem gets worse the larger your spreadsheet.

Formatting Excel Cells Based on Conditions

Instead of having the reader scan each cell, you can have Excel do some of the legwork by using conditional formatting. This allows Excel to apply a defined format to cells that meet specific criteria. The styling might include a different background color, font color, gradient, symbol or border. The goal is to make important information stand out so you can find them easier.

Excel already does some of this for you. As an example when you format numbers, there are options such as displaying negative numbers in red. This is an example of a predefined format.

Excel allows you to use formatting on individual cells or rows. The simplest method is to have Excel apply the conditional formatting if the cell meets certain conditions. This uses the “Cell Value Is” method.

Excel cell rule lists

Example of Excel Cell Rules

Some Cell Value examples include:

  • Apply a red background if the value is less than 50
  • Apply an italic bold font if the value is between 70 and 90
  • Apply a green font color if the value is Montana
  • Apply a yellow background to duplicate values

Excel also allows you to use formulas for conditional formatting. One benefit to Excel formulas is you can reference the values elsewhere on your spreadsheet. In the example below, I’m requesting that if the value in B2 is greater than the value in C2, apply a green background color.

Excel conditional formatting formula

Excel Conditional Formatting with a Formula

Microsoft has greatly enhanced this feature over the years and now predefines popular examples so you don’t need to rely on formulas as much. Below are 3 popular examples.

How to Apply Excel Conditional Formatting

Adding these formatting rules is easy. The hardest part is finding out what you want to emphasize on your Excel spreadsheet. What are your audiences’ interests and what actions might they take.

Example 1: Highlight Cells Above a Specific Number

  1. Highlight your cell or range of cells.
  2. From the Home tab, click Conditional Formattingbutton.
  3. Excel Conditional Formatting button

    Excel Conditional Formatting button

  4. From the drop-down menu, select Hightlight Cell Rules.
  5. From the side menu, select Greater Than…
  6. Excel Greater Than...menu option

    Greater Than…menu option

  7. Adjust the value in the Format cells that are GREATER THAN: field.
  8. Note: Excel will pre-populute this field based on the existing values of your highlighted cells.

    Excel Greater Than dialog box

    Excel Pre-fills the GREATER THAN value

  9. Click the down triangle to make your color selection.
  10. Excel conditional format color menu

    Choosing a color scheme

    Note: You can create a different scheme by selection Custom Format…

  11. Click OK

You should now see your results applied. In my example, I selected 40 as my value and used the red color scheme.

Excel highlighted cell

Highlighted Entry Great Than 40

Example 2: Highlight Top 10 Entries

One popular request is trying to find the top entries, whether they be a set number or percentage.

  1. Follow the starting 3 steps from Example 1.
  2. From the Conditional Formatting drop-down menu, select Top/Bottom Rules
  3. From the side menu, select Top 10 Items…
  4. Highlighting the Top 10

    Highlighting the Top 10

  5. In the Top 10 Items dialog box, adjust your count or color scheme if necessary.
  6. Click OK.

Example 3: Show Data Bars Instead of Color Shading

Another useful option is to use “Data bars”. This is another type of visualization that you can apply.

  1. Follow the starting 3 steps from Example 1.
  2. From the Conditional Formatting drop-down menu, select Data Bars
  3. From the side menu, select your color fill.
  4. Excel color Fill and Gradient Options

    Color Fill and Gradient Options

In my example, I chose a solid green fill.

Excel Conditional Formatting with Data Bars

Excel Conditional Formatting with Data Bars

Excel’s conditional formatting can make your spreadsheets more useful if used appropriately. People won’t waste time looking for important changes or that “needle in the haystack”.

Additional Resource

Sample Excel Spreadsheet

Want More Tips & Tools?

Several times a month, we also publish a free newsletter with relevant stories, tips and special offers.

Thank you. You should receive an email shortly from [email protected] asking you to confirm your subscription. If you're a Gmail user, the email may show under your Promotions or Updates tab. We apologize for the extra step, but we do it for security reasons.

There was an error submitting your subscription. Please try again.

Powered by ConvertKit

Filed Under: Excel, Tutorials




Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
About
Cell
Email
Excel
Featured
Google
Hardware
Health
Internet of Things
Product & Service Reviews
Quick Tips
Screencasts
Security
Services
Software Reviews
Terms
Tutorials
Web
Windows
Word

Click for More results...

SPONSORED ADS

300x250







© 2018 · Productivity Portfolio | PO BOX 117361 Burlingame, CA 94011