Top 10 Formulas for Aspiring Analysts

Posted on January 16th, 2013 in Learn Excel - 87 comments

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

SUMIFS Formula is used to sum a range of values subject to various conditionsIf 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 - Syntax, explanation & exampleVLOOKUP 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.

NETWORKDAYS formula tells us the number of working days between a start and end date

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.

IFERROR Formula - Syntax & Help

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.

Your email address is safe with us. Our policies

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

87 Responses to “Top 10 Formulas for Aspiring Analysts”

  1. Narain says:

    Awesome!! Thanks for sharing. I can’t thank you enough for your posts. They really help me become awesome in MS Excel.
     
     

    • Asoka says:

      Your stuff is fantastic & more than that your helping & sharing attitute is really appreciated. Not everyone will do that.

      may you be blessed by the triple gem of Lord Buddha

  2. TheQ47 says:

    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.

  3. DFruge says:

    How about SUMPRODUCT()? It’s a very powerful and flexible function.

    • Chandoo says:

      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 :)

  4. PPH says:

    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(

    • Chandoo says:

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

    • Kenneth says:

      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.

  5. Chiquitin says:

    Awesome post. Really, If you master these formulae you can do almost anything with excel.
    But I miss one formula: COUNTIFS
    Regards from Spain.

  6. Shar says:

    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!!

    • Anton says:

      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.

    • Suhas says:

      Hi,
      Shar, use “Evaluate Formula” to understand formula behavior.

  7. 5antiago says:

    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
     

  8. Analyst says:

    Thanks for the post! More financial modelling tips would be much appreciated!

  9. shair says:

    Disregard my previous post … it’s just a percent (ie /2% = /.02).  It’s just worded strangely. 

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

  11. Jay says:

    Thank you. This is perfect!

  12. DataDame says:

    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.

  13. Mark says:

    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

  14. Guy says:

     
    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.

    • Chandoo says:

      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.

  15. Sally says:

    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

  16. Kiev says:

    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.

  17. zur says:

    Chandoo: Your list is absolutely must with if formulas added

  18. Wimpie says:

    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.

    • Chandoo says:

      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.

  19. Roberta says:

    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. 

    • Dave says:

      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.

      • Chris says:

        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

  20. Brian says:

    Your list is well thought out…  I was originally just looking for 10 things to teach to a new co-worker
     
    Thank you!

  21. Jon says:

    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.  

  22. Dave says:

    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

  23. sam says:

    I think its pretty unfair to leave out ROW() and COLUMN() which help you to iterations in a formula
     

  24. Anand Kumar says:

    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 

  25. Mark K says:

    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.

    • Kenneth says:

      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.

      • PPH says:

        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.   

      • Mark K says:

        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!

        • Steph Locke says:

          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)

  26. Ravi says:

    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.

    • Hui... says:

      @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

  27. Juan says:

    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.

  28. […] Top 10 Formulas for Aspiring Analysts by Purna “Chandoo” Duggirala. […]

  29. Stan says:

    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.

  30. Colin says:

    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.

  31. LeonK says:

    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

  32. DinoWeb says:

    I have used the vlookup function for 15+ years, but I still remember how excited I was when I first discovered it. I have tended to move away from it more now though.

    One thing I have always found usefull in functions like vlookup, sumif etc, is when you want to match values across multiple columns, to make a lookup value column, by adding the apporopriate columns together.

    eg: to find “John Smith”, when the name is stored in two columns as FirstName, Surname. In the LookupValue column just use =FirstName & ” ” & Surname, and then look up on this value. It’s a simple concept but one that took me years to realise.

  33. jason chroman says:

    Several thoughts:

    1. LOOKUP is simpler and more useful, in most cases than =VLOOKUP. The main reason to use =VLOOKUP is if you need to do an exact match through unsorted data. Otherwise, the constraint of having to put in a column offset with VLOOKUP makes it less useful than LOOKUP.

    2. Being able to work with real dates is essential in a financial model. As such, it’s important to know how add days or months to a date. So, the date number format is important, as well as the DATE function, and also the EDATE and EOMONTH formulas.

    3. Once you have your cashflows and dates, running an XNPV or XIRR is fundamental. They allow you to be much more precise in your return calculations than NPV or IRR, which assume regular cash flows.

    4. Data Table functions (by this I mean “Data/Data Table” and not the Excel 2007/2010 feature called “Tables”)are essential for automating sensitivity analysis. Also, it’s much more important to be able to do a one factor data table than a two factor.

  34. Jackie says:

    Wow! I just discovered your blog. You’re an awesome person for putting this valuable resource for others to see. Thank you and I look forward to increasing my nerdy ways through Excel.

  35. shree says:

    hi…
    chandoo,it’s amazing using sumifs, thanks for it.

  36. Ken Stephens says:

    Are you aware of any Excel macros that compute the Probability of Acceptance for double sampling plans, such as, n1, c1, n2, c2?

  37. pankaj says:

    Thanks a ton for sharing…

    We can use “ASAP uitilities” (readymade Micro) which helps in solving many Complicated excel operations like identifing Duplicates, Fill In, and many more….

  38. Jonathon Williams-Ross says:

    Appreciate your great explanations as always. Working with Excel in Search Engine Marketing, sometimes I’ll download a list of Keywords & I’ll need to add a + sign in front of them. For example, one of my keywords may be “Green Shoes”, & I may also have “Black Shoes”, etc. I may have 500 keywords & will often need to add a + in front of all of them. Basically I need something that will add a character to all of the individual characters in a cell. Any formula suggestions?
    I tried =”+”&B8&”+” for example, but that puts the + in front of the first word and behind the last one. I need the plus in front of both.
    Thanks!!

    • Satya says:

      Hi,
      You can try this. Use Text to columns to separate your key words, add + with & formula before all the words and again join them by “concatenante”. It might look a little lenghty but you can write a macro to automate it.
      Enjoy!

  39. shilpee says:

    Hello,

    Thank you for providing tips for Excel.

    I have a query for changing the text in upper or lower case.

    When we change the text in upper or lower case with the formula: =upper(C4), the case of the text changes. However, when we delete the text in the origional cell, the cell where the changes were made also gets deleted automatically.

    Please provide assistance in how to save the text with the changed case and delete only the origional text.

    Thank you.

  40. indesignkat says:

    You can copy the cells with the upper formula then paste over them using paste special/values, then the origin cells can be deleted. As long as there is still an active formula referencing them, deleting the origin will mess up the result.

  41. Mark says:

    Are Pivot Tables formulas too? I would put them on this list.

  42. Top 10 Formulas for Aspiring Analysts | Chandoo.org – Learn …

    Jan 16, 2013 … They really help me become awesome in MS Excel. ….. macros that compute the
    Probability of Acceptance for double sampling plans, such as, …
    http://chandoo.org/wp/2013/01/16/top-10-formulas-for-aspiring-analysts/

    The above was an entry on a web search, but I couldn’t find anything on “Probability of Acceptance for double sampling plans” as mentioned in the entry. Please advise

  43. Qualitist says:

    Why does the following VBA Code give an error, “A value used in the formula is of the wrong data type” ??
    ‘Function to Compute Probability of Acceptance for Dodge and Romig Type Double Sampling Plans
    Function DRDSPa(N, P, N1, C1, N2, C2)
    Dim i As Long
    Dim D As Long, PA1 As Long, PA2 As Long
    D = N * P
    ‘ PA1 is Probability of Acceptance on First Sample
    PA1 = Application.WorksheetFunction.HYPGEOM.DIST(C1, N1, D, N, True)

    PA2 = 0
    For i = C1 + 1 To C2
    PA2 = PA2 + Application.WorksheetFunction.HYPGEOM.DIST(i, N1, D, N, False) * Application.WorksheetFunction.HYPGEOM.DIST(C2 – i, N2, D – i, N – N1, True)

    Next i
    DRDSPa = PA1 + PA2
    End Function

    • Hui... says:

      @Qualitist

      I suspect that your first line should be
      Function DRDSPa(N as Double, P as Double, N1 as Double, C1 as Double, N2 as Double, C2 as Double) as Double

      Is it possible for you to upload the file as I suspect it is the lack of definition of the data types or the data itself that is at error

  44. pankaj says:

    Hi,

    Thanks a ton for sharing…..

    Can you light on “Macro”, as they help a lot and saves time. Most of the time we do the same operations daily just the data base changes, and even if you know formulas it takes a lot of time to save the time and get accuraccy Macro is the solution.

    Thanks in Advance…

    Regards,

    Pankaj from INDIA

  45. Robert says:

    this is a great list that everyone who uses excel should have. I’ve had issues teaching people the basics of SUMIF. with SUMIFS, SUMIF should just be eliminated.

    one thing to note:
    trim does NOT take care of internal spaces (above it references “middle” of text), but substitude can do it just fine.
    also, thank you for introducing me to trim, it would have saved me several minutes over the last few years, but now i know!

  46. Sashi says:

    Can u please give us some more no of examples on “Nested formulas”

  47. What a great set of Excel functions. Thank you so much. Are you happy for me print and share these with my Maths and IT students? I am certainly going to keep them at my side when analysing large speadsheets. Cheers

  48. New functions are added or renamed / improved in every Excel version.There are almost 500 functions in Excel 2013.

    By using the most appropriate function for your task, you will get the most accurate results possible, plus your Excel models will be easier to maintain and audit.

    You can navigate to any function help webpage using the navigation (unlocked VBA) Add-in created with the innovative Ribbon Commander framework. See link:

    http://www.spreadsheet1.com/syntax-and-usage-navigation-add-in-for-excel-2013-functions.html

  49. […] Top 10 formulas for analysts [Visitors: 65,638] Employee vacation tracker [Visitors: 42,659] Interactive chart in Excel – How to make it? [Visitors: 42,416] Angry Formulas game… [Visitors: 36,392] Learn top 10 Excel features [Visitors: 25,723] To-do list with priorities – Excel templates [Visitors: 19,947] Introduction to Power Pivot [Visitors: 21,298] Best new features in Excel 2013 [Visitors: 21,539] How to create interactive calendar in Excel? [Visitors: 17,478] 5 Keyboard shortcuts for writing better formulas [Visitors: 18,577] […]

  50. Asif says:

    sir I really enjoying ur tips but I must say that you should post vidios as well regards

  51. Sindhu says:

    Your website make my projects go from good to amazing! I think me should also add date functions to this list along with text. . And of course substitute and find.. they work best for searching text in cells

  52. Suresh says:

    Hi Chandoo,

    This is a very useful compilation… Thank you and God Bless!

    I have found the countifs along with Named Ranges very useful.. The operators to be used inside the countifs for comparison, along with refering value using the & operator might be a topic for a future post, if not already done.

    Regds, Suresh

  53. Krishna Singh says:

    Hi Chandoo,
    Thanks for all the tips.
    I wnat to know if there is any excel tool that can convert the excel image into excel table. I know there are some optical tool called “OCR” that we need to buy from internet, but I want to know if excel 2010 can convert this so that I don’t have to buy this program.

  54. Dina says:

    Love this post and love your blog – the 2014 Easter Egg hunt was fun! On item #2 in this list, did you know that there’s a typo in the attached image? It says:

    “vlookup(“John”,list,2,false) = finds where Jon is in the list and returns the value in the 2nd column”

    The equation is looking up the name John with an H and the explanation references Jon without an H. In vlookups where EVERYTHING matters (i.e. spaces, periods, commas, etc), that could be misleading.

    Thanks agian for all the info! Great stuff!!

  55. Krishna Teja says:

    Hi,

    I agree with most of the comments.
    But I think one formula missing in the comment section is “IFERROR” formula. Because its the presentation that matters in the end.

    Krishna

  56. I would add one of the simplest built-in functions to the list:
    Ctrl-H i.e. Find and replace. Bringing data out of places like SAP and other text-formatting products it would take hours and days to clean it up if you didn’t have “Find and replace” in your toolbox.

    Thanks for a great list!

  57. javed akhter says:

    a lot of thanks and hat’s of to you for this awsome job

  58. Thank you , this is the best post regarding those formulas.

  59. Mehak Naz says:

    hI CHndoooo…
    Really Awsome Formulas..but i dnt take a rightanswerin SUMIF How when i put a righr formula

  60. Mehmet Gunal OLCER says:

    I use Range Names very frequently.

  61. melanie says:

    In healthcare, I use the Rand() function to provide clinical reviewers with random patient information pulled from SQL database for chart reviews. It’s a great function!

  62. Picky says:

    Cool stuff. Very interesting and motivating.

    I use the rand() and int() functions to randomly pull out a name from a list of names. Excel is awesome!

  63. Raheem Ali says:

    SUM IF is so important for my work as i am using in another way and right and left is always using as well. thank you and most helpful.

Leave a Reply