SubscribeTop 10 Formulas for Aspiring Analysts
Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.
While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.
If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.
A good analyst should be familiar with below 10 formulas to begin with.
1. SUMIFS Formula
If you listen very carefully, you can hear thousands of managers around the world screaming… “How many x we did in region A, product B, customer type C in month M?” right now.
To answer this question without the song and dance of excessive filtering & selecting, you must learn SUMIFS formula.
This magical formula can sum up a set of values that meet several conditions.
The syntax of SUMIFS is like this:
=SUMIFS( what you want to sumup, condition column 1, condition, condition column 2, condition….)
Example:
=SUMIFS(sales, regions, “A”, products, “B”, customer types, “C”, month, “M”)
Learn more about SUMIFS formula.
2. VLOOKUP Formula
Pop quiz time ….
Which of the below things would bring world to a grinding halt?
A. Stop digging earth for more oil
B. Let US jump off the fiscal cliff or hit debt ceiling
C. Suddenly VLOOKUP formula stops working in all computers, world-wide, forever
If you answered A or B, then its high time you removed your head from sand and saw the world.
The answer is C (Well, if all coffee machines in the world unite & miraculously malfunction that would make a mayhem. But thankfully that option is not there)
VLOOKUP formula lets you search for a value in a table and return a corresponding value. For example you can ask What is the name of the customer with ID=C00023 or How much is the product price for product code =p0089 and VLOOKUP would give you the answers.
The syntax for VLOOKUP is simple.
=VLOOKUP(what you want to lookup, table, column from which you want the output, is your table sorted? )
Example:
=VLOOKUP(“C00023″, customers, 2, false)
Lookup customer ID C00023 in the first column of customers table and return the value from 2nd column. Assume that customers table is not sorted.
Click here to learn more about VLOOKUP Formula.
Bonus: Comprehensive guide to lookup formulas.
3. INDEX+MATCH Formulas
For every 10 people using VLOOKUP, there is someone realizing its most annoying limitation. VLOOKUP formula can only search on left most column.
That means, if a table of customers has customer ID in left column and name in right column, when using VLOOKUP, you can search for customer ID only.
You cannot ask questions like what is the customer ID of “Samuel Jackson” ?
VLOOKUP would choke and bring your Excel world to a grinding halt.
Thankfully, INDEX+MATCH formulas come to rescue. These 2 beautiful formulas help us lookup on any column and return corresponding value from any other column.
Syntax:
=INDEX(list of values, MATCH(what you want to lookup, lookup column, is your lookup column sorted?))
Example:
=INDEX(customer IDs, MATCH(“Samuel Jackson”, Customer names, 0) )
Click here to learn more about INDEX & MATCH formulas.
4. IF Formula
Q: What do you call a business that does not make a single decision?
A: Government!
Jokes aside, every business needs to make decisions, even governments!!! So, how do we model these decisions in Excel.
Using IF formulas of course.
For example, lets say your company decides to give 10% pay hike to all people reading Chandoo.org & 5% hike to rest. Now, how would you express this in Excel?
Simple, we write =IF(employee reads Chandoo.org, “10% hike”, “5% hike”)
The syntax of IF formula is simple:
=IF (condition to test, output for TRUE, output for FALSE)
Click here to learn more about IF formulas.
5. Nesting Formulas
Unfortunately, businesses do not make simple decisions. They always complicate things. I mean, have you ever read income tax rules?!? Your head starts spinning by the time you reach 2nd paragraph.
To model such complex decisions & situations, you need to nest formulas.
Nesting refers to including one formula with in another formula.
An example situation: Give 12% hike to employees who read Chandoo.org at least 3 days a week, Give 10% hike to those who read Chandoo.org at least once a week, for the rest give 5% hike.
Excel Formula: =IF(number of times employee reads chandoo.org in a week >=3, “12% hike”, IF( number of times employee reads chandoo.org in a week >0, “10% hike”, “5% hike”))
You see what we did above? We used IF formula inside another IF formula. This is nothing but nesting.
You can nest any formula inside another formula almost any number of times.
Nesting formulas helps us express complex business logic & rules with ease. As an analyst, you must learn the art of nesting.
Lots of nested formula examples & explanations here.
6. Basic Arithmetic Expressions
=(((123+456)*(789+987)) > ((123-456)/(789-987)))^3 & " time I saw a tiger"
If you read the above expression and not had to scratch your head once, then you are on way to become an awesome analyst.
Most people jump in to Excel formulas without first learning various basic operators & expressions. Fortunately, learning these requires very little time. Most of us have gone thru basic arithmetic & expressions in school. Here is a summary if you were caught napping in Math 101.
| Operator | What it does | Example |
|---|---|---|
| + – * / | Basic arithmetic operators. Perform addition, subtraction, multiplication & division | 2+3, 7-2, 9*12, 108/3, 2+3*4-2 |
| ^ | Power of opetator. Raises something to the power of other value. | 2^3, 9^0.5, PI()^2, EXP(1)^0.5 |
| ( ) | To define precedence in calculations. Anything included in paranthesis is calcuated first. | (2+3)*(4+5) calcuates 2+3 first, then 4+5 and multiplies both results. |
| & | To combine 2 text values | “You are ” & “awesome” returns “You are awesome” |
| % | To divide with 100. | 2/4% will give 50 as result. Note: (2/4)% will give 0.5% as result. |
| : | Used to specify ranges | A1:B20 refers to the range from cell A1 to B20 |
| $ | To lock a reference column or row or both | $A$1 refers to cell A1 all the time. $A1 refers to column A, relative row based on where you use it. For more refer to absolute vs. relative references in Excel. |
| [ ] | Used to structurally refer to columns in table | ourSales[month] refers to the month column in the ourSales table. Works only in Excel 2007 or above. Know more about Excel Tables. |
| @ | Used to structurally refer to current row values in a table | ourSales[@month] refers to current row’s month value in oursales table. |
| { } | To specify an inline array of values | {1,2,3,4,5} – refers to a the list of values 1,2,3,4,5 |
| < > <= >= | Comparison operators. Output will always be boolean – ie TRUE or FALSE. | 2>3 will be FALSE. 99<101 will be TRUE. |
| = <> | Equality operators. Check whether 2 values are equal or not equal. Output will TRUE or FALSE | 2=2, “hello”=”hello”, 4<>5 will all return TRUE. |
| * ? | Used as wild cards in certain formulas like COUNTIF etc. | COUNTIF(A1:A10, “a*”) counts the values in range A1:A10 starting with a. For more on this refer to COUNTIF & SUMIF in Excel |
| SPACE | Intersection operator. Returns the range at intersection of 2 ranges | A1:C4 B2:D5 refers to the intersection or range A1:C4 and B2:D5 and returns B2:C4. Caution: The output will be an array, so you must use it in another formula which takes arrays, like SUM, COUNT etc. |
7. Text formulas
While there are more than two dozen text formulas in Excel including the mysterious BHATTEXT (which is used to convert numbers to Thai Bhats, apparently designed by Excel team so that they could order Thai take out food #), you do not need to learn all of them. By learning few very useful TEXT formulas, you can save a ton of time when cleaning data or extracting portions from mountains of text.
As an aspiring analyst, at-least acquaint your self with below formulas:
- LEFT, RIGHT & MID – to extract portions of text from left, right & middle.
- TRIM – to remove un-necessary spaces from beginning, middle & end of a text.
- SUBSTITUTE – to replace portions of text with something else.
- LEN – to calculate the length of a text
- TEXT – to convert a value to TEXT formatting
- FIND – to find whether something is present in a text, if so at what position
You can find several examples of all these formulas & their users in our site. Just search.
8. NETWORKDAYS & WORKDAY Formulas
“There aren’t enough days in the weekend” – Somebody
Whether a weekend has enough days or not, as working analyst, you must cope with the working day calculations. For example, if a project takes 180 working days to complete and starts on 16th of January 2013, how would you find the end date?
Thankfully, we do not have to invent a formula for this. Excel has something exactly for this. WORKDAY formula takes a start date & working days and tells you what the end date would be.
Like wise NETWORKDAYS formula tells us how many working days are there between any 2 given dates.
Both these formulas accept a list of additional holidays to consider as well.
- NETWORKDAYS: calculate the number of working days between 2 dates (assuming Saturday, Sunday weekend)
- NETWORKDAYS.INTL: Same as NETWORKDAYS, but lets you use custom weekends [Excel 2010+ only]
- WORKDAY: Calculate the end date from a start date & number of working days
- WORKDAY.INTL: Same as WORKDAY, but lets you use custom weekends. [Excel 2010+ only]
More on working with Date & Time values in Excel.
9. SMALL & LARGE Formulas
Almost nobody asks about “Who was the second person to climb Mt. Everest, or walk on moon or finish 100 mtrs race the fastest?”.
And yet, all businesses ask questions like “Who is our 2nd most valuable customer?, third vendor from bottom on invoice delinquency? 4th famous coffee shop in Jamaica?”
So as analysts our job is to answer these questions with out wasting too much time. That is where SMALL, LARGE formulas come in handy.
- SMALL: Used to find nth smallest value from a list. Use it like =SMALL(range of values, n).
- LARGE: Used to find nth largest value from a list.
- MIN: Gives the minimum value of a list.
- MAX: Gives the maximum value of a list.
- RANK: Finds the rank of a value in a list. Use it like =RANK(value, in this list, order)
10. IFERROR Formula
Errors, lousy canteen food & dysfunctional coffee machines are eternal truths of corporate life. While you can always brown bag your lunch & bring a flask of finely brewed coffee to work, there is no escaping when your VLOOKUP #N/As. Or is there?
Well, you can always use the lovely IFERROR formula to handle errors in your formulas.
Syntax:
IFERROR(formula, what to do in case of error)
Use it like:
IFERROR(VLOOKUP(….), “Value not found!”)
Click here to learn more about IFERROR Formula.
3 Bonus Formulas
If you can master the above 10 formulas, you will be ahead of 80% of all Excel analysts. Here are 3 more important formulas that can come handy when doing some serious data analysis work.
- OFFSET formula: to generate dynamic ranges from a starting point and use them elsewhere (in charts, formulas etc.).
- SUMPRODUCT formula: Unleash the full power of Excel array processing by using SUMPRODUCT.
- SUBTOTAL formula: Calculate totals, counts & averages etc. on a range with filters.
What formulas do you think are important for analysts?
During my days as business analyst, not a single day went by without using Excel. It was an important tool in my journey to become an awesome analyst. I cannot stress the importance of formulas like SUMIFS, VLOOKUP, INDEX, MATCH enough. They play a vital role in analyzing data & presenting outputs.
What about you? What formulas do you think are important for analysts? Please share your ideas & tips using comments.
Want to become an Awesome Analyst? Consider our Excel School program
If you are a budding analyst or manager, adding Excel Skills can be a very valuable investment of your time. My Excel school program is designed to help people like you to learn various basic & advanced features of Excel & use them to create kick ass reports, trackers & analysis. This program has 24 hours of Excel training, 40 example workbooks & 6 month online access.
Click here to know more about Excel School.
| ||||
|
| ||||
|
Leave a Reply
 |
Insert Multiple Columns at once [Quick tip] | What is Power Pivot – an Introduction [video] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
49 Responses to “Top 10 Formulas for Aspiring Analysts”
Awesome!! Thanks for sharing. I can’t thank you enough for your posts. They really help me become awesome in MS Excel.
This is a great post, thanks sooo much for it.
I guess I probably knew all of these Formulas/formulae already, but to see them all in 1 post just makes it easier.
How about SUMPRODUCT()? It’s a very powerful and flexible function.
You are right. SUMPRODUCT is very versatile and powerful. That said, for a budding analyst, it might be a huge roadblock. If someone has access to Excel 2007 or above, it is better to use SUMIFS / COUNTIFS / AVERAGEIFS for most things. When SUMPRODUCT has to be used, then just learn it
Rather than just SUMIFS, I’d say all of the -IFS formulae as a group (AVERAGEIFS, COUNTIFS..). Not to mention array formulas so that you may IF other functions where the IF(S) version doesn’t exist, like MIN(IF(, MAX(IF(, SMALL(IF(
@PPH & Chiquitin: I agree. I did not mention the other IFS / IF formulas as they all have similar syntax and use. Once you know SUMIFS, you know the rest.
I would add the following functions for Analysts:
Basic Statistical Functions
Linest(), RSQ(), Slope()
Forecast/Trend Functions:
Forecast() and Trend()
All are described in:
http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/
http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/
http://chandoo.org/wp/2011/01/27/trendlines-and-forecasting-in-excel-part-3/
DataTable:
http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
I would also recommend understanding Averages and Weighted Averages
http://chandoo.org/wp/2010/06/15/weighted-average-excel/
Thank you, Hui. I think it would surprise a lot of people new to the business world just how often average vs weighted average has to be explained.
Awesome post. Really, If you master these formulae you can do almost anything with excel.
But I miss one formula: COUNTIFS
Regards from Spain.
I consider myself pretty awesome with Excel, but you always show me there’s a zillion things I still don’t know.
I’m wondering about the % under #6 – Basic Arithmetic Expressions. How does ‘Divide with 100′ make 2/4% = 50? I don’t understand what the % is doing.
As always – excellent post!!
Hi. It seems it takes precedence over the “/”. So it will do “4%” first. = 4 / 100 as % divides by 100. It gives 0.04.
Then 2 is divided by that result to give 50.
Hi,
Shar, use “Evaluate Formula” to understand formula behavior.
I would add also that it’s good to get used to the boolean side of things, they can replace IF(), particularly when making calculated helper columns in data tables to use later in pivot tables
Thanks for the post! More financial modelling tips would be much appreciated!
Disregard my previous post … it’s just a percent (ie /2% = /.02). It’s just worded strangely.
I have offset() as one of my primary formulas & I would include indirect() as well. Most of that is for creating automated reports/dashboards but invaluable for this.
Didn’t know indirect() function. Thanks for the tip!
Thank you. This is perfect!
In healthcare, I can’t live without the unique count =IF(SUMPRODUCT(($c$2:$C2=C2)*($d$2:$D2=D2))>1,0,1)… with huge reports of patient activity, I need sometimes to identify and discard duplicates easily either with conditional formatting to highlight or filter out the zero value.
I would love to share this with my co-workers in a monthly newsletter. Can I get your permission to do so if I give you full attribution and include a link back to your site? (If not I understand)
Thanks for all your great tips/tricks/knowledge sharing!
Mark
Thanks Mark. Please proceed. You can include a short excerpt & A link back to this page.
regarding the segments of the vlookup formula… I believe that the final segment of the formula snytax is asking you to input “True” if returning the nearest match is ok or “False” if you require exact matches only… not whether or not your list is in ascending order as presented below…
VLOOKUP Formula
Pop quiz time ….
Which of the below things would bring world to a grinding halt?
A. Stop digging earth for more oil
B. Let US jump off the fiscal cliff or hit debt ceiling
C. Suddenly VLOOKUP formula stops working in all computers, world-wide, forever
If you answered A or B, then its high time you removed your head from sand and saw the world.
The answer is C (Well, if all coffee machines in the world unite & miraculously malfunction that would make a mayhem. But thankfully that option is not there)
VLOOKUP formula lets you search for a value in a table and return a corresponding value. For example you can ask What is the name of the customer with ID=C00023 or How much is the product price for product code =p0089 and VLOOKUP would give you the answers.
The syntax for VLOOKUP is simple.
=VLOOKUP(what you want to lookup, table, column from which you want the output, is your table sorted? )
Example:
=VLOOKUP(“C00023?, customers, 2, false)
Lookup customer ID C00023 in the first column of customers table and return the value from 2nd column. Assume that customers table is not sorted.
Click here to learn more about VLOOKUP Formula.
Bonus: Comprehensive guide to lookup formulas.
You are right. Most people would find nearest / exact match switch confusing (as your list needs to be sorted in ascending order for nearest match to work). So I used plain English version of it in the syntax.
In 1998 I went on an Excel Data Analysis & Reporting course. One of the things covered was vlookup – I could immediately see a use for it in creating a price book for my company.
In the 15 years since, this has become my most used formula. At the course we also learnt about if statements, and scenarios and pivot tables & lots of other things but it was the vlookup that grabbed me ….
It has only been in the last few months, since starting to work on excel 2010 that I have managed to master pivot tables. Back in 1998 I couldn’t see how they were of any use, now I love them – especially with the use of slicers (and they are so much easier to construct now)
I will slowly work through mastering some of your other Top 10 Formulas – thanks for sharing them in such a clear way.
Sally
Well, this is really a great post, i need to re-learn some of them, like OFFST and Match. How about Date, Year, Month, Day formula, they are very useful as well.
Chandoo: Your list is absolutely must with if formulas added
Good day Chandoo
Can I please borrow your chrystal ball. (Not sure how else you do it). Everytime I start a new project you publish what I need to get it done. Thank you so much for this great site and your information sharing. With your help I am now one of the “Guru’s” in my division and people come to me for help.
I knew you are going to ask, so I already mailed it you. Give it a few days to arrive.
Jokes aside, I am so glad you are learning and growing in your work. More awesomeness to you.
great List – I crunch lots of data for mailing lists – one sort of “text” field I use constantly with imported data is =value() to turn what should be numbers in numbers. Much easier to use with large numbers of rows than filling down with the tag.
I don’t often find myself trying to convert text numbers into numbers, but I have always just multiplied the cell by 1 (= A1 * 1). The new cell is recognized as a number. I was unaware of the Value() function. Thanks for the new info.
I use a lot of data exported from other systems where the number values come across as text. Rather than adding another column to convert the text (e.g., =–(A1) ), I lke to use the Data/Text to Columns feature to convert text to numbers in place.
In fact I found that I use it it enough that I wrote a little macro that I keep in Personal.xlsb just to perform this task. One limitation is that it o (note that like the text to columns tool, it will only convert one column at a time.
Sub ConvertToText()
‘ convert selection to text
‘ cjhx 1/9/2012
‘
‘ SHORTCUT: ctrl + shift + t
‘
Selection.TextToColumns , DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, FieldInfo:=Array(1, 2)
End Sub
Your list is well thought out… I was originally just looking for 10 things to teach to a new co-worker
Thank you!
A great and comprehensive list. I think the $ deserves its own post because when used effectively an analyst is much faster and also less error prone.
Great list, some of my personal favorites are listed. I would consider adding in the bonus area something about using absolute vs relative cell references (as mentioned by Jon). I transformed from someone who was just good at Excel to a SME when I learned how to use the $. For all of the formulas mentioned above, the $ can make pasting across rows/columns much more efficient and effective
I think its pretty unfair to leave out ROW() and COLUMN() which help you to iterations in a formula
Countif is surely very helpful in a lot of cases. Sumif would be second, and the combination of offset and match functions so that they can be used as vlookup on the left side of data is also helpful.
Thanks,
Anand Kumar
My sister claims that learning vlookup helped her get three promotions. At my own job it has helped me become “The Magic Report Guy”.
Small and Large… I beat my head against the wall for ages trying to find a function that did that.
I know these are not formulas but pivot tables and basic macro creation and editing can make you appear to be a genius. I thought one guy was going to cry when I created a macro that automated a report he spent about a week every month doing by hand. After that my boss stopped giving me grief about surfing the net to look at Excel sites.
vlookup is great, but I’ve switched to index & match for pretty much everything. After using vlookup for so long the syntax seemed weird at first, but once I got used to it I didn’t want to go back.
Consider the difference here:
=VLOOKUP($G4,$A2:$D13,2,FALSE)
This looks at the value in G4 and finds a match in the range A2:A13, then when it finds a match it returns the value in the 2nd column on the match row.
=INDEX(B2:B13,MATCH($G4,$A2:$A13,0))
This gets the same result, it’s just formatted a little differently.
One significant difference here is that with vlookup, if you copy the formula and paste it one column to the right, it returns the same result. In order to return the value from the third column, you need to change that 2 to a 3. When you’re dealing with large amounts of data you end up copying/pasting the formula, then doing find/replace to change the “,2,”s to “,3,”s, then in the next column to “,4,”s, etc.
Using the index/match method, you just copy/paste the formula over to the next column and you’re done.
Whichever method you use, you need to be sure the $s are in the right places for absolute referencing.
Another significant difference is that not only does index/match not require the lookup value to be in the far left column, but it doesn’t even require that the lookup values and return values are in the same rows. You can have A2:A5 be the return values and ZZ32:ZZ35 be the lookup values.
Yeah, I never ever use VLOOKUP and actually find it annoying, particularly when I have to review and edit someone else’s work that uses it extensively.
This works well. It is a little more work to set up but I really like that the columns returned increment unlike they do with vlookup. I have never understood the reason that they don’t. They want to increment the search column instead. I hate to think how much time I have spent redoing column return values in vlookup. Thanks!
With column references in vlookups – if I need to do a number of consecutive columns, I use the column() function with a constant to retrieve the column so that the vlookup becomes fully dynamic….
=VLOOKUP($G4,$A2:$D13,column() + 3 ,FALSE)
Sir I like your site very much especially my father like most because he use always in excel.
Can you give me some tips that how can I earn from my website at your level.
@Ravi
Start with the Beige box at teh top of the main Chandopo,.org web page
It has a section called ” Welcome to Chandoo.org. New here?“
In there there is a whole section on Learn Excel Topics and Courses
Consider exploring both
[...] Top 10 Formulas for Aspiring Analysts [...]
It’s wonderful to have all this functions in a great compilation, it helps me a lot to start learning the functions I am not familiarized with. Personally I felt in love with VLOOKUP and MATCH functions, they simplified my life a lot and increased the productivity very fast. Excel is full of surprises and awesomeness is just around the corner.
[...] Top 10 Formulas for Aspiring Analysts by Purna “Chandoo” Duggirala. [...]
This is a great post and I feel honoured to contribute to it. A combination I get excited about when I get a chance to use it is SUM and IF in an array. The format goes like this:
{=SUM(IF(E5:I5=E5,IF(D6:D15=D9,E6:I15)))}
It allows you to sum across a range, E6:I15 in this case, based on reference values in both your row and column headers. It’s like a two-dimensional SUMIFS. It works left to right or right to left, up or down, like INDEX MATCH. It will also sum based on the same reference value in two different columns, which I’m not sure SUMIFS will allow you to do.
The list is pretty much what I would recommend for budding analysts – but I have a criticism about terminology.
VLOOKUP, SUMIFS, etc are not formulae but Excel FUNCTIONS.
Formulae in Excel are algebraic statements (expressions) which use a combination of mathematical operators and at least one or more of the following:
constants, range references, functions
to return a result. Many formulae in a model may not even contain a function!
Many will see this as pedantic, but to avoid confusion Function anf Formula should NOT be used interchangeably.
User Definable Distinct rows formula.
Hi Chandoo, I meant to respond last week with this formula but forgot. I almost always use this formula in a helper column to identify distinct rows. I then use them in Pivot tools:
Column D: If(sumproduct(($a$2:$a2=a2)*($b$2:$b2=b2)*($c$2:$c2=c2))>1,0,1)
However, I usually create 2 helper columns as follows:
Helper Column 1
Column D: =a2&b2&c2
Helper Column 2
Column E: =If(sumproduct(($d$2:$d2=d2)*($d$2:$d2=d2))>1,0,1)
LeonK