Excel used in Consulting

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!

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.

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

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

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.

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  

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

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.