5 conditional formatting top tips – Excel basics

Posted on December 5th, 2017 in Excel Howtos , Learn Excel - 14 comments

Time for another round of unconditional love. Today, let’s learn about conditional formatting top tips. It is one of the most useful and powerful features in Excel. With just a few clicks of conditional formatting you can add powerful insights to your data. Ready to learn the top tips? Read on.

1. Highlight matching / missing items in two lists

Everyday millions of people ask – “Which items are common in these two lists?” and then most of them waste several minutes (or hours) comparing the lists. But you can answer the question in just five seconds. It is so simple and elegant.

  1. Select first list.
  2. Hold CTRL key and select the second list. This highlights both lists.
  3. Go to Home > Conditional Formatting > Highlight cell rules > Duplicate values
  4. Viola, you can instantly see which values are common in both lists.
  5. Bonus tip: If you want to see which values are unique to each list, just flip the highlight rule from dialog.

Related: Compare two lists in Excel [complete guide] | Compare things in Excel – podcast

2. Highlight top 10 items

Once again, a common problem faced by lots of people everyday. Which items are top / bottom n in this list?

The answer is simple. Just select your list and apply top / bottom rules.

Let’s say you have monthly customer walk-ins at your store as a list, like below.

You want to know which are top 10 days in November for customer walk-ins.

  1. Highlight walk-ins column
  2. Go to Home > Conditional formatting > Top/bottom rules > Top 10 items..
  3. Click ok (or change the number if you fancy)
  4. Done and done.

Pro tip: The default top / bottom rules only highlight the value column. If you want to highlight entire row or the corresponding date (or other data), you can use a formula based rule, like below:

Say your data is in A1:B30 and you want to highlight the rows where value in column B is top 10.

Select your data (A1:B30), go to Conditional formatting > New Rule. Select “Use formula…” option. Type in =$B1 >= LARGE($B1:$B30,10) and set up formatting. Click ok and top 10 items in your data will be highlighted.

3. Visualize changes over time with elegant icons

Things change, people change, money changes and most importantly, data changes… all the time. So how do you quickly and elegantly visualize how things have changed over time? Simple, apply conditional formatting icons to spot the changes.

Let’s go back to our store walk-ins example from #2.  We want to see the trend like this:

To get this, in the adjacent column, write this simple formula to compare walks-ins with previous day.

Now, select “Trend” column and go to Conditional formatting > New rule

Select format style as “Icon sets” and apply the rule as shown below.

Bingo, your cute trend icons are ready.

Related pro tip: Don’t just show simple numbers in your reports and dashboards.

4. Top customers by category

Time to ramp up the game. Let’s say you run a sporting goods store and you are looking the category-wise units sold to each customer, like below.

Your question: Which customers are top in each category?

Unfortunately, we can’t use default top / bottom rules to answer this question. But we can use a tidy little formula to get the answer. Let’s say our data is in the range $R$6:$T$124.

  1. Select your data, go to Conditional Formatting > New Rule
  2. Select “Use a formula…” type of rule
  3. Write the rule =$T6 = MAX(IF($R$6:$R$124 = $R6, $T$6:$T$124))
  4. Set up formatting as you want
  5. Done.

Check out below illustration to understand how this rule works:

And the result is awesome:

Related: MAXIF formula explained

5. Highlight values in a range

Often we want to narrow our focus to a small range so we can analyze better. Let’s go back to the store walk-ins example. If you want to highlight all days when the walk-ins are between 145 to 160 (the sweet spot as your manager calls it), you can use the built-in between rule, like below:

  1. Select walk-ins column
  2. Go to Conditional Formatting > Highlight cell rules > Between…
  3. Either type in the range or point to cells containing values.
  4. Done.

Related: BETWEEN formula in Excel

Top 5 conditional formatting tips – Example workbook

Click here to download the workbook with all these tips and sample data. Play with it to learn more. Try to implement your own rules to understand CF better.

 

What are your top conditional formatting tips?

Over to you. What are your top conditional formatting tips? Please share them in the comments section.

More conditional formatting tips:

Conditional formatting is one of my favorite Excel features. I talk about it all the time. Check out below tutorials for more awesome tips.

 

14 Responses to “5 conditional formatting top tips – Excel basics”

  1. Chihiro says:

    My favourite is case sensitive match. Assuming column A contains list of valid strings. And D contains strings where check should be performed on.
    CF formula.
    =ISERROR(MATCH(1,--EXACT($A$2:$A$14,D2),0))

    This will format all invalid entries in D.

  2. Mike says:

    How would u highlight variance about absolute value of 500,000 ? So above +500000 or -500000? Thx!

  3. Eddy says:

    Great Tips!
    By the bye, ”Basket Ball” is one word, "basketball".

  4. Eloise says:

    One of several favorite Conditional Formatting formats colors every other row to make a long list or large sheet easier to read.

    Click on Home tab, Conditional Formatting button.
    Select: Manage Rules then New Rule button.
    Use a formula to tell which cells to format. Using this formula:
    =MOD(ROW(),2)=0

    Applies to range: e.g. $A$1:$J$100
    Click Format button, then Fill tab, then select a very light color.
    I use the lightest shade of blue.
    Select OK, then Apply then OK.

    • Terence says:

      Depending on your use there may be a more suitable option. The mod approach doesn't adjust to filtered data so you can end up with adjacent cells both coloured.

      To make it adjust for filters you can use the following, assuming your data is in A1 to J100 and you want the whole row banded

      =isodd(subtotal(3,$A$1:$A1))

      Or depending on your Excel version just use tables, which I would highly recommend (insert tab for Excel 2010)

  5. Kirstin Larson says:

    Here is a truly awesome conditional formatting tip I picked up not too long ago--say you have a column of data on a report you submit regularly which contains confidential data that you may not want to print (for example, personal information such as SSN on a spreadsheet containing employee data)- at the top of the report, you can place a button that you can click, or a cell for T/F to either hide the data or show the data when printing. Then create a conditional formatting that states if the button/cell is TRUE, change the font in that column to white, otherwise leave the font black. This simple condition makes it very easy to hide/unhide the data with the click of a button!

  6. Juan says:

    Thank you very much Chandoo for sharing these awesome tips, number 1 is impressive, I didn't know it could be possible so easily!

  7. Somashekar says:

    Can someone please help know how to format this requirement:
    I have variance values polulated for Jan - Dec in columns B to L.
    i want the values greater than 0.05 and values greater than -0.05 to be highlighted.
    How do I achieve this.

    • Chandoo says:

      @Somashekar... you can use below logic.
      1. Select your data and go to Conditional Formatting > New Rule
      2. Set type of rule as "Formula..."
      3. Type =abs(B1) > 0.05 and set up formatting
      4. Done.

      Note: Change B1 to actual address of first cell in your data.

  8. Prashant N says:

    Thanks Chandoo after long time I refresh myself

  9. Mike B. says:

    What if I have a value in a cell but wanted to change the font of that cell (from normal to bold or one black to red) based on the value of another cell? Any suggestions?

    • Hui... says:

      @Mike

      That is exactly what CF is designed to do

      Give it a go

      Select the cell or range
      Goto CF
      Apply a new CF using a Formula
      Reference the other cell as required
      the Formula must evaluate to TRUE to trigger the CF
      eg: If you are in D10
      You can use a CF of =A2=1
      then CF will change format when A2 =1, it will be normal when A2<>1

Leave a Reply