Eric P. Butts

Career Coach. Management Consultant. Writer. Marketer. Accountant.

  • Home
  • About
  • Useful Resources
  • Articles
  • Products
    • Free! Member-Only Content Library
    • Success Factory Coaching Application
    • Excel Bootcamp
    • Breakthrough Resume Writing
    • Copywriting Services
    • Corporate Ladder University (Book)
  • Privacy Policy
  • Terms of Use
  • Disclaimer
  • Login
You are here: Home / Excel / 10 Excel Formulas Every Consultant Should Know

April 24, 2016 by Eric Butts 15 Comments

10 Excel Formulas Every Consultant Should Know

Top 10 excel formulas

Excel is one of those things you can spend a lifetime trying to master everything it can possibly do. Luckily you don’t need to know everything Excel can do to get a lot out of it.

I work with a partner now who is known to make the claim that Excel is the world’s largest Enterprise Resource Planning (ERP) system.

Also, I can tell you when I started in business school at Kellogg, we used two fancy macro workbooks to run sophisticated statistical analysis until they shifted to having everyone use STATA.

STATA was/is infinitely better than Excel, but you’d be amazed at what you can do in Excel, with a little bit of coding know-how.

This post is about the Excel formulas, though – the little statements you type into a cell to make magical things happen.

Read on to dive into the ten Excel formulas you need to know and love to…excel as a consultant.

1. Vlookup

Vlookup is a formula that allows you to link data from two difference sources based on a unique key. They syntax is =vlookup(value, lookup table, column number in the lookup table, match type). 

A couple of notes, the column numbers should be counted started with the column containing the lookup key. In other words, your lookup table should be formatted such that the column with values that might match are the first column of that table.

If you want the lookup to pull the nearest match – aka fuzzy match – put true in the last term and don’t forget to make sure your lookup table is sorted in ascending order. I don’t think I’ve ever used a fuzzy match in this way, but the functionality is there.

Generally speaking, the lookup value needs to be a unique or you may distort the results.

2. Match

Match is a formula that returns either the row number or column number of a value you’re looking for in a table. The syntax is =match(value, lookup row OR column, match type).

The thing to note here is that match type has a few variations. If you put “1” as the match type, your lookup array row/column needs to be ascending order. This will tell Excel to return the position of the largest value that is less than or equal to your lookup value.

Using “-1” tells Excel to return the position of the smallest value that is greater than or equal to your lookup value. This means your lookup row/column has to be sorted in descending order.

If you want an exact match, then you just put “0” in for the match type.

I rarely use the match formula independently to figure out a value’s position in a column or row because you can generally get that answer by highlighting the row/column and doing a search for the lookup value.

You might be asking why match is on this list then if I never use it, but the operative word in my statement above was “independently”. When you get to number 4, you’ll understand what I mean.

3. Index

Index is vlookup on steroids. Perhaps the best part of the index formula is the lookup value doesn’t need to be in the first column of the lookup table. If you’ve ever found yourself wishing you could do a negative vlookup, this is for you.

There are two syntaxes for using the index but the one I use is =index(lookup table, row number, column number). In most instances, you can probably just type in the column number like the vlookup formula, but if your table has more than one row, index needs you to provide the row of the lookup value so it can return the result from the intersection. You can either type in the lookup value row manually for each occurrence of the formula or you can use formula #4 instead.

4. Index + match

Ok, so technically this is two formulas combined,  but it is a slick way of doing lookups without needing to re-sort any of your data. The syntax is =index(lookup table, match(lookup value, lookup value column in lookup table, match type), column number).

You can also you match for the column number argument if you’re feeling too lazy to count how many columns it is to get from column A to column AC.

5. Text

When it comes to lookups, formatting matters and one thing that’s happened too many times for me to count is I get a data set where the lookup key doesn’t match on the lookup table because one of the files has dropped the leading zero. For the lookups to work the lookup value needs to be formatted the same in each table.

Let’s say we have two files. File #1 has my ID listed as 01234 and File #2 has my ID listed as 1234 in cell A2. I would fix file 2 by typing into a new cell =text(A2,”00000″). The first argument is the value that needs to be transformed, and the second value defines the format. In this case, the format is 5 digits.

6. Sumifs

The sumifs formula allows you to return the sum of a specified column based on certain conditions. The syntax of the formula is = sumifs(sum_range, criteria range1, criteria1, criteria range2, criteria2,…). Using sumifs gives you some functionality you can’t have in a standard vlookup, because it allows for repeated lookup values in the lookup table.

Imagine having a table with a list of customer IDs and wanting to pull in their total sales for the year. The lookup table, however, has a record for each month. Using index or vlookup would only return the first record with the matching customer ID. Sumifs will give you the total, even if your data set isn’t sorted by customer ID.

Additionally, you can have multiple criteria, so this can be quite useful when summarizing data in a table so you don’t have to key in every cell manually.

7. Countifs

Similar to the sumifs Excel formula noted above, countifs allows you to quickly summarize data using basically the same syntax. The beauty of countifs is it also gives you a good way to deal with qualitative data. For example, if you have a massive data table and you want to know the number of female customers who live in San Jose, countifs has you covered.

8. Iferror

Once you have started using formulas, you inevitably come across a scenario where the formula is working perfectly but the result is hideous – #N/A. That means the value you’re looking for doesn’t show up in the lookup table.

To keep your table clean use the syntax =iferror(formula,””). The second argument tells Excel what to do should you formula produce and error. In this case, I put empty quotation marks so Excel will return a blank, but you could put something else there like “additional research required”.

9. &

If you find yourself wanting to combine the values int two cells, you can easily do that by using =Cell1&Cell2. If you want to put a space or other character between the two cell values, try =cell1&” “&cell2 or =cell1&”_”&cell2. This formula comes in handy when you want to make sure you have a unique value to perform a lookup. For example, first name might not be a unique key, but first and last is. If you’re combining more than two values (that are all adjacent to each other), I would suggest using the next formula instead.

10. Concatenate

Sometimes creating a unique value for lookups requires mashing together several fields. Concatenate does this quickly by doing =concatenate(highlighted array of cells). Again, you can add in a character or space to separate the fields, but this is mostly cosmetic. The upside of this over the & formula is that if you’re combining 8 cells to come up with your unique value, you don’t have to type in “&” so many times.

Want to learn more about Excel formulas?

If you’d like to see a video of any of these formulas in action or have another Excel challenge you’re trying to solve, let me know in the comments.

Update: you can now access my excel bootcamp course with videos and sample workbooks here:

ericpbutts.com/excel-bootcamp

Filed Under: Excel, Management Consulting Tagged With: accounting, consulting, data analysis, excel

About Eric Butts

I’m a management consultant, MBA and CPA who has a passion for helping others in their career pursuits. Grab my FREE cheatsheet on 12 simple habits of highly successful consultants.

Comments

  1. Emmanuel says

    April 26, 2016 at 4:55 PM

    Hi, Eric,

    Thank you for the continuing training.
    These hotkeys and knowledge of Excell
    are worth a mine either for a student and
    a consultant. Good content!
    In my position of almost a newbie about Excell,
    I’d like to see any of these keys in action.

    Thanks

    Reply
    • Eric Butts says

      April 26, 2016 at 8:37 PM

      Thanks, Emmanuel. I will look at add some more visuals then. Also, stayed tuned for some more excel tips on other functions.

      Reply
  2. Shikesho Lazarus says

    April 29, 2016 at 4:42 AM

    Dear Eric

    Very insightful indeed. I think visuals would be of great help. Cant wait for the video.

    Thanks

    Reply
    • Eric Butts says

      May 1, 2016 at 5:55 PM

      Shikesho – sounds good. Make sure you sign up for the emails so you’re notifed when they become available. Thanks for reading.

      Reply
  3. Ayisi Brefo says

    May 1, 2016 at 5:13 PM

    Great insight. I will appreciate this excell course more in videos.
    Thanks Eric.

    Reply
    • Eric Butts says

      May 1, 2016 at 5:55 PM

      Hi Ayisi – thanks for the feedback. That’s the plan since many have asked for it.

      Reply
  4. Blanca Aquino says

    May 4, 2016 at 3:13 AM

    Excellent article, I cannot way to apply some of them. How I can see some of the videos demonstrating this formulas? Thanks…

    Reply
    • Eric Butts says

      May 4, 2016 at 9:59 PM

      Hi Blanca –

      I’m setting up video as we speak but it be offered to people already on my email list before it’s made available to the public.

      Reply
    • Eric Butts says

      May 30, 2016 at 8:36 AM

      Hi Bianca – some of the videos are live now in the free members section.

      Reply
  5. Monica Rockwell says

    May 9, 2016 at 1:47 PM

    I am interested in a video showing how to use these formulas.

    Reply
    • Eric Butts says

      May 30, 2016 at 8:34 AM

      There are some videos posted in the free content library now. Shoot me an email if you have trouble accessing it.

      Reply
  6. Jessica EYA'ANE says

    May 30, 2016 at 7:15 AM

    It’s very interesting.
    Thank’s dear Eric Butts

    I want to see the video.
    I will appreciated more this on watching.

    Best Regards.

    Reply
    • Eric Butts says

      May 30, 2016 at 8:35 AM

      Hi Jessica – I have started posting videos in my free content library. Check the latest post and shoot me an email if you have any problems.

      Reply
  7. Stephen Mwema says

    May 31, 2016 at 1:11 AM

    Am delighted to learn about excell from people who have done it. Am newly from school and would like to learn more. Thanks Eric

    Reply
    • Eric Butts says

      June 1, 2016 at 3:00 PM

      Thanks, Stephen. If you haven’t already, you should check the videos in the free content library here: https://ericpbutts.com/join-library/. I’m going to continue creating video content to share there.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Featured Posts

Case Study: When Should You Protect Your People?

3 Reasons Your Management Consulting Resume Didn’t Get a Call Back For An Interview

Watch Out For The Bobblehead Manager

How to Make Sure Your Workplace Ambition Is Not Perceived As Entitlement

What I Learned From the Promotion That Got Away [Case Study]

Popular Posts

  • 5 Reasons You Should Be Proud to Ask Dumb Questions at Work
  • What Does It Mean to Have a Sense of Urgency
  • Always Stay Close to the Gatekeepers
  • Why Are There Still So Few Black CPAs?
  • What's the Difference Between FP&A and Accounting?
  • 3 Reasons Average Students Become Good Accountants
  • One Thing You Should Never Do on Linkedin
  • How to Handle Double Standards in the Workplace
  • How to Pass the CPA Exam Without Breaking the Bank
  • Masters in Accounting Isn't The Sweet Treat Some Students Think

Recent Posts

  • Do you want safety or excellence in your career?
  • A job searcher’s worst enemy
  • Are you falling for one of these career killing lies?
  • How to job search like a man
  • The one blog post that saved this consultant’s job

Stay in Touch

  • Facebook
  • Instagram
  • LinkedIn
  • RSS
  • Twitter
  • Privacy Policy
  • Terms of Use
  • Disclaimer

© 2012–2019 Eric P. Butts