5 conditional formatting top tips – Excel basics

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.

• Conditional formatting basics – how to use it?
• Become a conditional formatting rock star with these tips
• Link conditional formatting with slicers for awesome results
• Case study: Design awesome financial metrics dashboard
• Case study: Web analytics dashboard