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.
20 Responses to “5 conditional formatting top tips – Excel basics”
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.
How would u highlight variance about absolute value of 500,000 ? So above +500000 or -500000? Thx!
@Mike.. check out below reply to Somashekar. You can use the same logic.
Great Tips!
By the bye, ”Basket Ball” is one word, "basketball".
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.
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)
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!
Thank you very much Chandoo for sharing these awesome tips, number 1 is impressive, I didn't know it could be possible so easily!
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.
@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.
Thanks Chandoo after long time I refresh myself
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?
@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
Perfect, worked like a charm. Thank you very much.
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
@ Jacob
Please ask the question at the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file to ensure a quicker more accurate answer
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.
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".
Thanks Mike. I fixed that mistake now 🙂
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.