fbpx
Search
Close this search box.

Top 10 Excel Formulas for any situation

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

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

  6. Excel Tables are my favorite. It allows one to dynamically increase the range used in formulas.

  7. Shekhar says:

    Dear Chandoo Garu,
    With reference to https://www.youtube.com/watch?v=Q-XGJbHG8Vo(Top 10 Excel Formulas - for any situation)
    I am Very thankful to you Sir for allowing me to learn the Top 10 Excel Formulas. Here, I would like to ask you Sir is there any Road Map with hyperlinks in one place to cover
    for learning of all important formulas useful in our job profile. For Example, once I see this Road Map it should show at one place with enough examples/scenarios to cover perfect knowledge
    of that particular formula.In other words, with regard to subtotals formula, how many types of examples or scenarios we may come across in our practical life(My conscious is asking me to
    search for any other video records are available to gain more knowledge about that particular formula or else is this is enough to think that this example is more than enough) .
    Because, having hundreds of Examples in Youtube channel or your website:chandoo.org, how do I conclude that I learned all types of scenarios or criterias learned total knowledge
    of a particlar formula.I look forward to see your kind reply Chandoo Garu.

    Best Regards
    Shekhar

  8. Ashish says:

    Hi Team,

    Question :
    How to unhide multiple sheets in one excel in one single click without using any Macros. I have one Excel in which have 50+ sheets with pivots and Country wise, some time have to keep only 2 sheets and others should be deleted. Can any one help on thes.

    Regards,
    Ashish Mishra

Leave a Reply