Schedule mock interviews on the Meeting Board, join the latest community discussions in our Consulting Q&A and find like-minded Case Partners to connect and practice with!

Excel used in Consulting

Big 4 big four Big Four - Strategy Excel Excel for consultants MBB mbb big4
New answer on May 31, 2024
8 Answers
1.2 k Views
Anonymous A asked on Oct 15, 2023

Hello! New associate about to join big 4 consulting in a few months. What are some common Excel functions/formulas/models that are used? For example, I asked around a few associates and they said revenue models, index matching, V-LOOKUP, and if-statements are all essentials. I'm wondering is there anything else I'm missing?

Overview of answers

Upvotes
  • Upvotes
  • Date ascending
  • Date descending
Best answer
Benjamin
Expert
Content Creator
replied on Oct 15, 2023
Ex-BCG Principal | 8+ years consulting experience in SEA | BCG top interviewer & top performer

Hi,

Great that you are showing enthusiasm and initiative to try and prepare for the job. If you ask me - most of what you need to know, you'll pick up on the job. 

That being said, sharing for me what were the most common functions:

  • Pivot tables
  • Lookups: Index Match, Index Match Match, V/H Lookup, or X lookup (in the newer versions of excel)
  • Sums: Sumproduct, Sumif
  • Goalseek
  • Conditional formatting

In addition - do note that formatting and etiquette are equally as important (e.g. don't combine hardcoded numbers with formulas).

Lastly - really all you need on the job is google and colleagues who are willing to help you → this will get you to the function/solution that you need 90% of the time.

If I were you, I'd still prioritize taking a break / spending the time before work to do what you are passionate about - trust me you won't have that kind of time once you start in consulting :)

All the best!

Was this answer helpful?
Ian
Expert
Content Creator
replied on Oct 16, 2023
#1 BCG coach | MBB | Tier 2 | Digital, Tech, Platinion | 100% personal success rate (8/8) | 95% candidate success rate

Hi there,

Not sure this is really the right approach.

It's kind of like saying “What are the most common words in Italian” to try to learn the language.

First, remember you'll learn on the job. But if you want to learn beforehand, take a course! Whether it's an online one or a true class, this is the real difference-maker.

Another option is getting a coach to work with you (for example, I have a dozen-odd data-sets + excel workbooks for exercise practice).

That said, as requested, here you are:

SUM and SUMIF: These are used to calculate the sum of a range of numbers, and SUMIF allows you to sum values based on a specific condition, making it useful for segmentation and analysis.

AVERAGE: This formula calculates the average of a range of numbers, which is often used for performance metrics.

VLOOKUP and HLOOKUP: Consultants use these functions to search for specific values in a table or dataset and retrieve related information. VLOOKUP searches vertically, while HLOOKUP searches horizontally.

INDEX and MATCH: This combination is used for more flexible lookup operations, especially when dealing with large datasets.

IF and IFERROR: Consultants use the IF function for conditional calculations, allowing them to create custom rules and logic for data analysis. IFERROR helps manage errors and display alternative values.

COUNT and COUNTIF: These are used to count the number of cells with data and cells that meet specific conditions, respectively.

AVERAGEIF and AVERAGEIFS: Consultants use these to calculate the average of a range based on specified conditions.

MAX and MIN: These functions help find the maximum and minimum values in a dataset, which is useful for identifying outliers or extremes.

PivotTables: While not a formula, PivotTables are crucial for summarizing and analyzing data from large datasets.

TEXT functions: Functions like TEXT, LEFT, RIGHT, and MID are used for text manipulation, which is often necessary when dealing with names, addresses, or other textual data.

Was this answer helpful?
Hagen
Expert
Content Creator
replied on May 31, 2024
#1 recommended coach | >95% success rate | most experience in consulting, interviewing, and coaching

Hi there,

First of all, congratulations on the offer!

I would be happy to share my thoughts on your question:

  • First of all, while it is great to see how eager you are to prepare for your career start with your future employer, I would highly advise you not to worry about it. All major consulting firms will provide you with excellent onboarding and training that will prepare you for everything you need to know.
  • Still, if you want to improve your Excel skills a bit, I would advise you to also be familiar with pivot tables, data validation, conditional formatting, SUMIFS, and COUNTIFS (even if this is non-exhaustive). While there may be company-specific Excel functions, you will still be faster in the first few months with some practice.

If you would like a more detailed discussion on how to best prepare for your upcoming role, please don't hesitate to contact me directly.

Best,

Hagen

Was this answer helpful?
Alberto
Expert
Content Creator
replied on Oct 17, 2023
Ex-McKinsey Associate Partner | +15 years in consulting | +200 McKinsey 1st & 2nd round interviews

Hi there,

Congrats on your new job! Don't be stressed about your Excel skills, you'll probably get trained on this and other consulting skills. 

Two things make sure you accelerate your learning curve once you start working:

  • Look for projects that require high analytical skills and Excel modelling (there is no better way of learning than doing)
  • Ask for coaching or 1-on-1 training sessions to colleagues with 1-2 years of experience. They will be happy to help. Your manager can be helpful here as well

Best,

Alberto

Check out my latest case based on a real MBB interview: Sierra Springs

Was this answer helpful?
Francesco
Expert
Content Creator
replied on Oct 18, 2023
#1 Coach for Sessions (4.500+) | 1.500+ 5-Star Reviews | Proven Success (➡ interviewoffers.com) | Ex BCG | 10Y+ Coaching

Hi there,

Congratulations on the offer! In terms of your question:

Q: What are some common Excel functions/formulas/models that are used? 

Some of the most common formula/tools are Vlookup, Pivot Tables, Concatenate, Sumifs, Countifs, Sumproduct and Index.

Good luck!

Francesco

Was this answer helpful?
Cristian
Expert
Content Creator
replied on Oct 16, 2023
#1 rated MBB & McKinsey Coach

Hi there!

I see you have lots of great answers below. 

Have the trust that you'll receive all the training you need when you start.

Aside from this, offset-match was an eyeopener for me. I'd also recommend that you sign up for 1-2 killer projects in the beginning, either focused on analytics or due diligence type projects and afterwards you'll find everything way easier. 

Best,
Cristian

———————————————

Practicing for interviews? Check out my latest case based on a first-round MBB interview >>> SoyTechnologies  

Was this answer helpful?
Frederic
Expert
Content Creator
replied on Oct 16, 2023
ex Jr. Partner McKinsey |Senior Interviewer| Real Feedback & Free Homework between sessions|Harvard Coach|10+ Experience

Wow, great ambition to train before even joining (not really needed though if you have general good learning ability). Here some things to consider besides formulas:

Pivot Tables: These are incredibly useful for summarizing and analyzing large sets of data. They can help you quickly extract insights and create easy-to-read reports.

Data Validation: Useful for ensuring that data entered into cells meets specific criteria. This can help maintain data consistency.

Goal Seek: This tool is handy for working backward to find the input value needed to achieve a desired outcome.

Was this answer helpful?
Raj
Expert
Content Creator
replied on Oct 16, 2023
FREE 15MIN CONSULTATION | #1 Strategy& / OW coach | >70 5* reviews |90% offers ⇨ prep-success.super.site | MENA, DE, UK

Here are some additional Excel functions and models that are commonly used in consulting:

Pivot Tables: Pivot Tables are powerful tools for data analysis and summarization. They allow you to quickly analyze and visualize large datasets.

SUMIF and COUNTIF: These functions are used to perform calculations based on specific criteria. SUMIF calculates the sum of values that meet a given condition, while COUNTIF counts the number of cells that meet a specified condition.

Data Validation: Data Validation is used to control the type and format of data entered into cells. It helps ensure data accuracy and consistency.

Financial Modeling: As a consultant, you may be involved in financial modeling tasks. This can include building financial projections, performing sensitivity analysis, and calculating key financial metrics like NPV and IRR.

Scenario Analysis: Scenario Analysis involves creating different scenarios by changing input variables to assess the impact on outcomes. It helps in decision-making and risk assessment.

Data Cleaning and Transformation: Consultants often work with large datasets that require cleaning and transformation. Functions like TRIM, PROPER, and SUBSTITUTE can be used to clean and standardize data.

Was this answer helpful?
Benjamin gave the best answer

Benjamin

Content Creator
Ex-BCG Principal | 8+ years consulting experience in SEA | BCG top interviewer & top performer
395
Meetings
7,298
Q&A Upvotes
38
Awards
5.0
34 Reviews
How likely are you to recommend us to a friend or fellow student?
0 = Not likely
10 = Very likely
You are a true consultant! Thank you for consulting us on how to make PrepLounge even better!