Consulting Career
Which Excel functions should I practise before joining consulting?
I am joining Big Four consulting in a few months. People have suggested INDEX/MATCH, VLOOKUP, IF statements and revenue models. What other functions should I practise, and is learning a list of formulas the right approach?
Ian’s Answer
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 numbers 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.