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.
- Select first list.
- Hold CTRL key and select the second list. This highlights both lists.
- Go to Home > Conditional Formatting > Highlight cell rules > Duplicate values
- Voila, you can instantly see which values are common in both lists.
- 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.
- Highlight walk-ins column
- Go to Home > Conditional formatting > Top/bottom rules > Top 10 items..
- Click ok (or change the number if you fancy)
- 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.
- Select your data, go to Conditional Formatting > New Rule
- Select “Use a formula…” type of rule
- Write the rule
=$T6 = MAX(IF($R$6:$R$124 = $R6, $T$6:$T$124)) - Set up formatting as you want
- 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:
- Select walk-ins column
- Go to Conditional Formatting > Highlight cell rules > Between…
- Either type in the range or point to cells containing values.
- 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.
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub