fbpx
Search
Close this search box.

5 conditional formatting top tips – Excel basics

Share

Facebook
Twitter
LinkedIn

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. Voila, 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 | Web analytics dashboard with conditional formatting & sparklines

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

20 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

  10. Jacob says:

    Hello There
    I am Jacob and New to this site

    I have a requirement with conditional formatting to show the progress bar in a cell.
    Column A has all the target data and Column B has all the incoming data.
    When anyone types the data(number) in B, it should compare the percentage against target from A and show the bar. I tried in excel with single cells and it works, but when I select all the rows (nearly 500 cells) it says circular reference is not allowed.

    Can anyone guide me?

    Thanks
    Jacob

  11. MikeW says:

    I'm using CF to display an Icon Set.
    Is it possible to show no icon for certain cases?
    I have a range of text vales call 'NoShow' for values I do not want to display an icon. My table contains a 'Updated' column for when a record was last changed and a 'Status' column.
    I want to display an icon ONLY if Status is not in 'NoShow'.

    I know how to test the status column against my named range:
    =SUMPRODUCT(--ISNUMBER(SEARCH(NoShow,B5)))>0

    and how to test for a date range;
    =TODAY()-$J19>=4

    I'm just not sure how to combine everything to do what I want.

  12. Mike says:

    Good website.
    I've been using Excel for years but your still teaching me things.
    Small observation, but it's one of my pet hates. I don't know if it is across the site or just on the one page.
    https://chandoo.org/wp/conditional-formatting-top-tips/
    You say "Viola, you can instantly see which..."
    "Viola" is a musical instrument, The word should be "Voila".

  13. Fenn says:

    I have two columns.
    A column, shows the hired date of an employee.
    B column, shows the years in service based on the date from column A. Here's my formula: =INT(TODAY()-A1/365)

    Every five years, an employee will get an award. 5, 10, 15, 20, 25, etc.
    I have managed to put conditional formatting on B column (fill cell with green color and font in yellow) every 5 years.

    However, what I can't figure out how to highlight the cell in different color, 1 year before the "5th anniversary sequence". (4, 9, 14, 19, 24, etc.) Is this possible with conditional formatting?

    I would like to thank everyone that will answer my question in advance.

Leave a Reply