fbpx

Top 10 Excel Formulas for any situation

Learn Excel - 7 comments

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.

#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…

#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.

Chandoo

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

7 Responses to “Top 10 Excel Formulas for any situation”

  1. Ari Kellman says:

    Why are you promoting VLOOKUP and not INDEX(MATCH()) ? I find Index/Match to be both more useful and efficient than VLOOKUP

    • Chandoo says:

      Why not? There is no extra benefit to using INDEX+MATCH for normal VLOOKUP use cases. I use VLOOKUP all the time. While learning INDEX+MATCH can let you do more, for a beginner (who are targeted in this post) VLOOKUP is a better formula to pick up.

      If you look at my follow up links under VLOOKUP section, you will see that I do teach INDEX+MATCH and other variations.

  2. Khan says:

    Nice, but would like to see some formulas which is combination of 2+ formula/function like an example vlookup+match/index, hlookup+match/index.. Thanks

  3. howtodash says:

    Cool! Thank you for your sense of humor)))

  4. Manoj says:

    Great website to learn MS Excel online. I am not proficient in Excel & want to learn several things to use it for preparing reports and collecting data. Just bookmarked your site for future reference.

    Thank you & all the best,

  5. Sonu Sharma says:

    My favourite one is 6 SUBTOTAL – Filter what you want, see summaries. other are also helpful.

Leave a Reply


« »