Here’s the answer to a question about THE Excel Functions To Know.
The request was for the top 10 but 10 is too many. After 3 or 4 functions, they get difficult to rank because they fall into areas of infrequent use or exclusive use. Things like INDIRECT, INDEX, RAND, and CHAR are powerful but an ordinary Excel user might go months between uses. Also, some functions are useless on their own and signal that you’re doing something layered and complex. (I’ve never used INDEX by itself but have used INDEX and MATCH many times)
The 5 Functions that I chose for the Top 5 are ones that will give you serious mileage in building Excel Formulas and allow you the read formulas that someone else built. So, here we go!
Top 5 Excel Functions (according to Oz)
 IF
 IF allows us to work based on conditions
 IF a person attended at least 2 of 5 sessions
 IF a product is over 2 lbs AND perishable
 IF the City equals EITHER Baltimore, Philadelphia, Miami, OR, Indianapolis
 Setting these conditions allows us to dig out the information we need. Otherwise, we might have a separate set of data for everything we want to do, and that becomes a big errorprone mess.
 IF allows us to work based on conditions
 VLOOKUP
 HOT DAMN! For many of Excel users, VLOOKUP by itself is worth the price of admission. Simple example:
 You have 2 lists. List A is 488 rows, List B is 506 rows. Uh oh! They’re supposed to be the same. How do we find out what’s on List B that’s NOT on List A, and vice versa? Answer: VLOOKUP.
 In 5 minutes you can have the answer. Compare that to eyeballing those 994 rows!
 HOT DAMN! For many of Excel users, VLOOKUP by itself is worth the price of admission. Simple example:
 Operators allow us to create sophisticated conditions and combine formulas for precision
 AND
 OR
 IFERROR
 Showed up in Excel 2007 and allows us to keep spreadsheets uncluttered when we know that an error will be the result. It also tells Excel what to do with an unexpected error.
 Example: If there’s an error, enter “assign to Kevin.” This will be an alert for entries that Kevin will have to research.
 SUMIFS
 Another 2007 newbie. Let’s say you’ve built a budget and want to create summaries out of a massive block of data. SUMIFS allows us to create multiple criteria. It’s nice to know, say, October’s overall expenses, but we might want to know something more specific. Examples:
 October, TRAVEL expenses
 October, Uncategorized expenses, that are amounts over $25, AND were online transaction
 Prior to 2007, there were complicated workarounds for accessing that granular information. SUMIFS makes this so easy.
 Another 2007 newbie. Let’s say you’ve built a budget and want to create summaries out of a massive block of data. SUMIFS allows us to create multiple criteria. It’s nice to know, say, October’s overall expenses, but we might want to know something more specific. Examples:
WHAT? Excel Formulas & Basics
(This may seem like a basic place to start but a lot of people who type answers into cells after they’ve done all of the math with a pencil and calculator.)
Calculating the Hours Volunteered Let’s say that we’re adding the hours volunteered by Li (7), George (16) and Margo (3).
Getting the most out of Excel requires the ability to write formulas. Formulas start with ‘=’ and can be basic math
=7+16+3
That is surely a formula but it’s not very helpful because it’s static. It will always be 26. It would be better to write a formula like:
=B2+B3+B4
The variables allow us to change the numbers and the result will automatically change. If we discover that Li volunteered an extra 2 hours, we can replace the 7 with a 9 and the total updates. Thus, writing formulas and thinking in terms of variables is much more useful. So let’s summarize why not just type 26 into the cell?
 It’s not dynamic
 We don’t know where the numbers came from
 26 shows that 26 hours were volunteered but how many did Margo do? We don’t know.
 Where is the math? Was it calculated (possibly miscalculated) in someone’s head? Is it scrawled on the back of a grocery receipt?
 We can’t troubleshoot the data if there’s a complaint
 Margo says that she volunteered 5 hours. Whomever typed in the 26 did mean to type 28 and fortunately remembers that it should have been 28.
SYNTAX
Formulas have syntax. Think of this as ‘proper grammatical structure.’ This is critical in making Excel formulas work. As formulas get more advanced and formulas get nested inside other formulas, the syntax is often what generates errors and makes people crazy. But if you can develop intimate familiarity with formulas … OMG! This is where Excel gets juicy! You get to do the wildest tasks that increase efficiency and allow you access to analysis (i.e., knowledge) that isn’t handed to you all nice and neat.
This is where it helps to think in English. Develop your own language. Let’s look at the IF function:
=IF(condition, if true, otherwise) 

The syntax has 3 parts 
I think of it this way 


EXAMPLES  
Oz’s “Grammar”  Ex. 1  Ex. 2 
What do you want me to look for?  Is the actual start date equal to or less than the expected start date?  Is the score over 75 AND the student is a Senior? 
If I find it, then what?  Don’t do anything  Enter “Ok to graduate” AND calculate final GPA 
Otherwise ….?  Calculate how late the project started  Enter the student’s score 
As we go through the Top 5 Excel Formulas, I’ll include examples and discussion of the syntax. Let’s jump in!
The next post will cover the IF function.
matchsticks photo credit: marfis75 via photopin cc
dice photo credit: @Doug88888 via photopin cc
typeset 5 photo credit: Leo Reynolds via photopin cc
graffiti photo credit: Zlatko Unger via photopin cc
5th floor photo credit: boklm via photopin cc
Hey Oz!
I just started watching Excel.tv recently. I started at the beginning and I plan on watching all episodes. In one episode, you mention Chandoo’s blog as one of your main starting points. I just thought you should know that you’ve really been the top dog for me. I’ve learned a lot in a short time watching your videos and reading your blog posts. I, like you, come from the viewpoint that your data must be clean. Really, there is no next step if you’re working with unclean data. So thanks for doing what you do, and keep up the good work!
My top 5 included two of yours, IF and Vlookup. My top 5 has CONCANTANATE (&), Trim and Clean, and DATEDIF. I know DATEDIF is an outdated function, no longer supported by MS, but I use it in a report every week.
I rarely use MATCH/INDEX, but it is POWERFUL. I use RANK a lot as well as MAX and MIN. LEFT and RIGHT, especially when used with FIND are powerful tools as well. I agree, IFERROR is also a great function.