Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.
Top 10 Excel Formulas – The list
#1 Table & Structural References
If you don’t know how to effectively talk to your data, then your formulas will suck. That is why, my #1 formula is not even a formula. Learn how to refer to data in the spreadsheet, especially in tables. You can use tablename[column name] notation to refer to entire columns of data. You can also use [@column] to refer to column value in the current row of table.
For example, you can write formulas like these:
- SUM(mySales[no. of customers]) to find how many customers we had.
- SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”
Learn more about tables & structural references in Excel.
#2 Bye bye nested IF, Hello IFS formula
You might already know about IF formula. We use it to test logical conditions and output one of two possibilities. But what if you have a very long, complex scenario that requires multiple IF functions? Simple, use IFS() instead. It can take any number of condition, output combinations and works elegantly.
Example Nested IF formula: =IF(A1>20, “Very high”, IF(A1>15, “High”, IF(A1>10, “Medium”, IF(A1>5, “Low”, “Very Low”))))
Same formula as IFS() =IFS(A1>20,"Very high", A1>15,"High", A1>10,"Medium", A1>5,"Low", A1<=5,"Very low")#3 SUMIFS / COUNTIFS
Almost all business analysis situations will involve questions like “what is the count / sum of things that meet conditions A,B…N”. And to answer them elegantly and swiftly, you need SUMIFS / COUNTIFS. The beauty of these functions is that they are easy to learn and use.
Example SUMIFS formula:
=SUMIFS(data[Purchase Amount], data[Lead Group],"Online", data[Quantity], ">3")
Sums up [Purchase Amount] column where [Lead Group] is online AND [Quantity] is more than 3. Learn all about SUMIFS formula.
#4 SWITCH – CHOOSE() for new age
SWITCH is one of the new functions introduced in Excel. This versatile function helps you select one of the many outcomes based on any type of conditions. In some ways, SWITCH is similar to IFS, but it also has default option, so if none of the SWITCH conditions are met, you get value in the default parameter.
Example SWITCH formula:
=SWITCH([@State],"CO","Other","WA","Other","TX","South","East")
What it does?
Looks at [@State] value and prints one of the outputs - "Other", "South" or "East"
#5 VLOOKUP – Always in style
The other day, I went to drinks with a few mates after work. We were sitting in a hip bar drinking best of Wellington beer on a sunny day. I overhear two ladies talking about, wait for it…. VLOOKUP. I turn around and look at them. They look like fresh graduates celebrating a busy week of work and they could be talking about almost anything, but VLOOKUP is trending.
So yeah, learn VLOOKUP you must. It is the quintessential Excel function for data analysis. You can answer questions about your data using VLOOKUP.
If you are an absolute VLOOKUP virgin, try introduction to VLOOKUP page or What is VLOOKUP video. For more advanced lookup trickery and examples, checkout VLOOKUP tag or get a copy of my VLOOKUP Book.
#6 SUBTOTAL – Filter what you want, see summaries
You know that SUM(), COUNT(), AVERAGE() etc. give you basic stats about your data. But what if you filtered out to look at data for “HR department” only or “people aged between 25 and 40”. Your SUM() doesn’t change.
This is where SUBTOTAL() comes in. By default, SUBTOTAL ignores anything that is filtered away. So what you see is what you get.
Example SUBTOTAL formula:
=SUBTOTAL(9,data[Purchase Amount])
Sums up (9) filtered values in data[Purchase Amount] column.Learn more about Excel SUBTOTAL formula.
#7 MAXIFS / MINIFS
You may already know about MAX() and MIN() formulas. But what if you want to know the maximum value based on a few criteria? Simple, use MAXIFS(). This newly added function is simple, versatile and easy to learn (if you know SUMIFS, then you know this too).
Example MAXIFS() formula:
=MAXIFS(data[Purchase Amount], data[Lead Group],"Online")
Returns maximum data[Purchase Amount] where [Lead Group] is "Online"#8 FIND / SEARCH
There are heaps of text formulas in Excel. But if you are just starting out, go with FIND(). It finds one text value inside another. If there is a match, FIND() returns the starting position of the match, else #VALUE error.
Keep in mind though, FIND() is case sensitive. Use SEARCH() if you don’t care about the case of data.
Examples of Excel FIND() and SEARCH() functions.
- =FIND(“l”, “Hello people..”) => 3
- =FIND(“P”, “Hello people..”) => #VALUE! error as P can’t be found.
- =FIND(“p”, “Hello people..”) => 7
- =SEARCH(“P”, “Hello people..”) => 7 as SEARCH doesn’t care for case
- =FIND(“p”, “Hello people..”, 8) => 10 Finds p after 8th letter – i.e. second p
#9 TODAY / NOW
Almost all business data will have a component of dates. So learning how to work with date & time values in Excel can be a huge help. If you are new to this, start with TODAY(). As the name suggests, TODAY() tells you the current date. This is a dynamic formula, so if you write =TODAY() in a cell, the date changes every day.
You can use NOW() formula to see current date & time.
Example – Calculating employee tenure in days:
If you have start date of an employee in a cell (A1), you can calculate their tenure (service) using TODAY() formula like this.
=TODAY() - A1
The answer will be number of days between A1 (Start date) and today.Learn all about working with date & time values in Excel.
#10 IFERROR – when #N/A happens
To err is human, to IFERROR is awesome.
Errors happen, but use IFERROR so that your audience see soothing messages rather than confounding #VALUE!s. IFERROR looks at internal formula or expression and if there is an error, prints alternative result you want.
Example IFERROR():
=IFERROR(VLOOKUP("THIS", Customers, 2, false), "Customer not found")
Looks for "THIS" in Customers table and returns 2nd column value if found, else says "Customer not found".Overview & Examples of Excel IFERROR formula.
Watch Top 10 Excel formulas – Video
I made a video about these top 10 formulas with an example data set. You will learn all the basics + some nifty tricks about these formulas in the video. Check it out below or watch it on my YouTube channel.
Download Top 10 formulas – Example workbook
Click here to download top 10 formula example file. Examine the formulas & table calculations to learn more. Use the data to write your own formulas and practice these skills.
Want to master formulas? – 3 resources for you
If you want to learn more about Excel formulas, you have come to the right place. Check out below three resources and be a formula master.
#1 – Read other formula articles
Formulas / functions are central to doing any kind of complex work in Excel. No wonder we have more than 400 pages on this on my site. Start with these examples and see how deep the rabbit hole goes.
- 100 Excel formulas for beginners
- Top 10 formulas for aspiring analysts – recommended
- What to do when Excel formula errors
#2 – Get a book
There are 100s of Excel books out there. I recommend getting one or two good ones to slowly & surely improve your skill. Start with these…
- Excel 2016 Bible by John Walkenbach
- Data Analysis & Business Modeling by Wayne Winston
- My book – The VLOOKUP Book by Chandoo
#3 – Learn from a course
Online video courses are easy and powerful way to learn everything you need from the comfort of your chair. I highly recommend either of these two for mastering Excel formulas & data analysis.
15 Responses to “Compare 2 Lists Visually and Highlight Matches”
Hi,
I solved this in a little different way.
We have 2 lists, one starts at A1 and other at B1, both are vertical arrays.
First thing is define 2 named ranges, list1 and list2:
list1 refers to "=OFFSET(Sheet1!$A$1;0;0;SUMPRODUCT(--(Sheet1!$A$1:$A$1000""));1)"
list2 refers to "=OFFSET(Sheet1!$A$1;0;0;SUMPRODUCT(--(Sheet1!$B$1:$B$1000""));1)"
this way lists will be dynamically sized when you had or remove elements (you can't have blanks and you can't have more than 1000 elements).
Then I use conditional formatting in column A when this formula is true:
"=NOT(ISERROR(MATCH(A1;list2;0)))"
and "=NOT(ISERROR(MATCH(B1;list1;0)))" to list2.
This way we eliminate the need for auxiliary columns or lists.
Hope you like my way! 😀
Nunes
Simple conditional formatting formula.
Assuming lists vertical lists starting in A1 & B1
To highlight just one column (assume B for example)
Conditional formatting>New Rule>by formula
=MATCH(B1,$A$1:$A$99,0)
Set the cell fill to what ever color you prefer & press OK
To highlight both columns repeat with this formula for cell in column A
=MATCH(A1,$B$1:$B$99,0)
This approach doesn't require named fields or addtl columns
glw
Say I had 1 list in A2:A20 and another in B2:B20.
To format all the items in column A that are repeated in column B I would use the following Conditional Formatting rule.
=IF(ISNA(VLOOKUP(A2,$B$2:$B$20,1,false)),true,false)
All the duplicates are highlighted. It us a very simple example of comparison.
I may be missing something here, but I usually highlight both my lists by holding ctrl eg A1:A20 E10:E40 then choose conditional formatting from the ribbon and then highlight duplicates, and this does it?
Lee, I was perplexed as well. I do the same thing you do with the conditional formating. A drag and click to highlight range and choose highlight duplicates does the trick for me.
I believe these methods are to check if an item from one list also appears in the other list. So if an item mentioned many times in one list if also mentioned in the other list or not.
The Conditional Formatting highlight duplicates feature will do this, but it will also highlight an item if it appears multiple times in the one column or list.
Hi, I would just like to know (if you are willing to share) which image editing program you use to make your image like above, like they are torn apart from bottom? I've been looking for long.
@i48998
Chandoo is on Holidays, but Chandoo uses Paint.Net
Paint.net is a free download available at http://www.paint.net/
.
I use CorelDraw/PhotoPaint
.
We both use the Snipping Tool (a freebe with Win Vista/10)
.
We both use Camtasia for doing screen captures to make animated GIFs where you see animation.
Here is how I would accomplish
(1) Define Names: List_1, List_2
(2) =ISNA(MATCH(D4,List_2,0))-1 (Conditional Format formula List_1)
(3) =ISNA(MATCH(D4,List_1,0))-1 (Conditional Format formula List_2)
ISNA will return 1 if NO Match and O if Match by adding a -1 will make: NO Match 0 and Match a -1 which is True
Hi all
this my first Post here
i think we can take Unique List for tow list to know what is not Duplicate By this Array formula
=IFERROR(INDEX($D$6:$D$33,SMALL(IF(ISERROR(MATCH($D$6:$D$33,$B$6:$B$33,0)),ROW($D$6:$D$33)-ROW($D$6)+1),ROWS($J$5:J5))),"")
and this one for Duplicate Value
=IFERROR(INDEX($D$6:$D$33,SMALL(IF(ISNUMBER(MATCH($D$6:$D$33,$B$6:$B$33,0)),ROW($D$6:$D$33)-ROW($D$6)+1),ROWS($J$5:J5))),"")
Don't forget to Enter This Formula by Pressing Ctrl+Shift+Enter
without wanting to ruthlessly self promote here, I do have an addin that does neatly compare two ranges, not just in columns, so you might want to check that out.
Having said that this is a pretty neat solution if you dont want to be going down the VBA or purchase route. I like it
however, could you not do something with the remove duplicates feature in Excel 2010 and then compare the resulting data set?
Hi, Chandoo! I've found yesterday your Excel website... What can I say? It's just awesome, Excellent. Being a developer for 30 years, more than 15 with Office products, and wow!, how many things I discovered in a couple of hours, and what pretty resolved.
I decided to take the long path of the newbies and read all your examples and write down by myself all of them, and when I arrived to this (the comparison of two lists) I think I've found a problem:
a) in "Step 4: Apply conditional formatting to Second List - Use the same logic, but this time the rule becomes =COUNTIF(count1s,$H6)" it should say "Step 4: Apply conditional formatting to Second List - Use the same logic, but this time the rule becomes =COUNTIF(count1s,$H6)>0", but this is a typing error that I believe all of us here might have discovered and corrected
b) the very problem: I wrote down two different lists, in different ranges, and with different number of elements, I specified the equivalent conditional formats, et non voilá!, I didn't get what expected. So I downloaded your example book, I checked range names, formulaes, conditional formats and all OK. So I copied -just values- from my book to yours, and I still couldn't achieve the goal.
I'm using Excel 2010 in spanish, I'm from Buenos Aires (Argentina), and my book is at your disposition whenever you considerate it appropiate.
Thanks in advance for your time, and again my congratulations for your work here.
Best regards.
SirJB7
Comparison of 2 lists visually with highlights
Author: SirJB7 / Date: 11-Dic-2011
Pros: no duplicated tables, no matrix formulaes, no named ranges, no VBA code, just conditional formatting
Cons: not found yet, comments and observations welcome
Features:
a) standard problem: highlights in orange/yellow elements existing in the other list
b) optimized problem: idem a) plus highlights in red/violet first occurrence of elements existing in the other list
Sheet contents:
a) conditional format, 1 rule per list (2 methods used)
A1:A20, first list
B1:B20, second list
a1) range A1:A20, condition =NO(ESERROR(BUSCARV(A1;B$1:B$20;1;FALSO))), format Orange ---> in english: =NOT(ISERROR(VLOOKUP(A1,B$1:B$20,1,FALSE)))
a2) range B1:B20, condition =CONTAR.SI(A$1:A$20;B1)>0, format Yellow ---> in english: =COUNTIF(A$1:A$20,B1)>0
b) conditional format, 2 rules per list (2 methods used)
D1:D20, first list
E1:E20, second list
b1) range E1:E20, condition 1 =Y(NO(ESERROR(BUSCARV(D1;E$1:E$20;1;FALSO)));COINCIDIR(D1;D$1:D$20;0)=FILA(D1)), format Red ---> in english: =AND(NOT(ISERROR(VLOOKUP(D1,E$1:E$20,1,FALSE))),MATCH(D1,D$1:D$20,0)=ROW(D1))
same range, condition 2 and format 2, same as a1)
b2) range E1:E20, condition =Y(CONTAR.SI(D$1:D$20;E1)>0;COINCIDIR(E1;E$1:E$20;0)=FILA(E1)), format Violet ---> in english: =AND(COUNTIF(D$1:D$20,E1)>0,MATCH(E1,E$1:E$20,0)=ROW(E1))
same range, condition 2 and format 2, same as a2)
Personally I like the a2) and b2) solutions, I think the formulaes are prettier.
I still don't know the rules of this website and forum, but it any precept is infringed I'm willing to share the workbook with the solution. If it breaks a rule, I apologize and promise that won't happen again.
Best regards for all!
Dear All i have a complicated situation...
1. I have two sheets of data Sheet1 and Sheet2 (from various sources) - Both of these contain data matching and Not matching as well..
2. Now for me i need to build an excel where in i need to get sheet 3 with values that are present in a column of Sheet 1.
What ever Sheet 1 doesn't have i dont want those rows from sheet 2 to be populated into Sheet3.
Can any one help me out.
Hi Team
The above example is to compare partial name from 2 different columns.
If I want to cross check it in a single column. I have both correct and partial correct/match entries in a column. Is there any way I can find both the entries in the column.
Regards