Dermot

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.

• Excel formula crash course – 6 module online class
• Excel School – online class on everything Excel

The post Top 10 Excel Formulas for any situation appeared first on Chandoo.org - Learn Excel, Power BI & Charting Online.

Before there were “The Penguin Chronicles” a friend and I, Lee Alsbrook, wrote a column called “The Recess Bell” for a local newspaper. This is one of those early columns.

December has always been a strange time of year for me. In addition to the holidays, my birthday is in December and so this month has become a watershed in my year and in my life. This was the month that society granted me important permissions. In December I became old enough to drive, old enough to get married, old enough to vote, and old enough to drink. I also got my induction notice in December.

As a child, though, December could be summed up with one word: Toys! My definition of a toy was fairly simple. If I could play with it, it was a toy. One year, my cousin and I both got full football uniforms with helmets and pads. We played some monumental one-on-one football that year. Those uniforms were toys.

I don’t remember when exactly, but at some point I stopped getting toys as presents. In fact, at some point I stopped getting gifts that I wanted and started getting gifts that I needed. I always thought that gifts should only be things you want, not things you need. If you need it, well, you need it, right? It shouldn’t count as a gift.

Looking back, I can see that, as my life evolved, what I wanted and what I needed changed. But probably the biggest change in what I wanted, and what I thought I needed, occurred when I became a runner. As a runner, the line between wanting and needing became hard to distinguish. As a runner, some of the gifts began once again to look a lot like toys. And getting toys is much more fun than getting things that you need.

One of the toys on my list this year is a pair of shoes. No, I don’t need shoes, but I want shoes. Not regular shoes, obviously. Not the kind of shoes that require polishing. Not shoes that are a part of my professional uniform. The toys I want have grid technology or gel or air pockets. I want toys with medial support. I want toys that cushion and stabilize and control.

More importantly, I want shoes/toys that will make me faster than I am. If they can’t really make me faster, I want some that make me feel faster. This year my toy list inlcudes clothes. Oh, I used to hate getting clothes. I hated getting shirts and sweaters – no matter how well intentioned the thought. (My grandmother used to spend months knitting us sweaters.) Somehow, the gift of clothes always seemed to be too adult for me.

But this year I want shirts. I want toys that keep me warm in the winter and keep me cool in the summer. Not T-shirts please. I’ve got enough race T-shirts to clothe a small army. No, I want toys that wick and layer. Give me toys made from materials that sound like chemicals. Give me polypropylene!!

This year, I’m asking for underwear and socks. Can you imagine? Underwear and socks as toys! If someone had given me underwear and socks before I was a runner I would never have forgiven them. But now I want toys that go 1,000 miles and are double layered or that are ultra thin and won’t make me blister.

I want underwear that wicks and protects and won’t make me chafe! What struck me as I began to assemble my list of wants and needs was that I had returned to my youth. I was asking for, and I had the hope of getting, toys. Gifts that I can use when I play. My shoes and socks and underwear have become items in my toy chest.

And I realized that in this December, as I creep without shame towards the half-century mark, I am really going backwards in time. I am going forward to the past. Not only is my body getting younger as I age, but my spirit is as well. This December I get a little older, but I’m not growing up.

So for me, and I hope for you, even if running does nothing more than bring back the joy of getting [and giving] gifts, that will be enough. Even if there are no other benefits, I will still put on my toys everyday and play with them.

Happy holidays. And waddle on, friends.

The post Giving and Getting appeared first on John Bingham.