• Sharebar

Hi guys. I am starting at MBB in three weeks from a scientific background and have thus only used excel for basic statistics and a bit of data analysis.
I would really appreciate some insight into what resources in excel you actually used on your first project. So if any recent hires could tell me what is really good to know from day one and what you can just learn as you go.

Thanks a bunch, and a happy new year to everybody :D

15% Off Wall St. Prep Financial Modeling Training Click Here

The WSO Advantage - Management Consulting

Consulting Case Interview Guide

Real Cases Revealed from Top Firms. Learn More.

GMAT Prep Promo

$333 Off GMAT Training. Learn More.

Resume Help from Consulting Pros

Land More MBB Interviews. Learn More.

Find Your Consulting Mentor

Realistic Mock Case Interviews. Learn More.

Comments (28)

  • darety's picture

    pivot table and knowing that f9 refreshes all cells should do for a consultant.

  • Dorsk's picture

    If you know how to do pivot tables and vlookups then you're probably fine. That being said, the more you know how to do and the quicker you can do it, the more you'll impress people. If you can do array formulas, macros, match/index, etc. it really will make a difference in your reviews.

  • devildog2067's picture

    Syntax is syntax. Flip through a book so you have some idea of what Excel is capable of. If you've got basic coding skills, and--most importantly--are in the habit of quickly googling to figure out how to do things you don't know how to do in whatever language you're familiar with, you'll be fine.

  • Asatar's picture

    Efficiency and using Google. You can learn all the advanced stuff through a combination of web searches and trial and error, but you should definitely have "terrifying efficiency" at performing your day-to-day stuff.

  • BTbanker's picture

    INDEX MATCH

    Also, you need to memorize important shortcuts for formatting, ie underlining (alt, h, b, o), copy formatting (f4), fill projections (ctrl + r), select all (ctrl + a), center selection (ctrl + 1, right, tab, down, center alignment).

  • frgna's picture

    First remove your F1 key, because it only gets in the way.

    Second, get a macro book that has shortcuts for all your formatting needs and practice doing all that real quick - make a really pretty formatted fake sheet and start from a blank sheet and practice getting from blank to pretty, time yourself to see when you've peaked out.

    Favorite functions/strokes and ones that are probably most useful for consulting (no particular order):

    F2
    F9
    F4 (learn how to use all absolutes properly)
    F5 then Alt+S then O, X, G, E (goes to all hard codes - then you can make them blue)
    Text to columns (CRITICAL for efficiency)
    Conditional formatting in general
    Ctrl + pageup / pagedown (always shocked how many people don't know this)
    Choose
    v/h lookups
    Using if statements in general
    Using min/max functions (sometimes much cleaner/more efficient than an if statement)
    Sumproduct/Sumif (useful for doing wtd averages)
    Truncate
    Proper
    Lower
    Upper

    That should be plenty good to impress your friends. Also just get really really fast using arrows/ctrl to bop around.

    I think even more important than shortcuts is general excel etiquette/best practices - things like never enter a number into part of a cell, never hide a column/row, etc. I can go more into that if you like.

    if you like it then you shoulda put a banana on it

  • mashed potatoes's picture

    i also come from a life sciences background, so excel was a bit foreign for me. i referred to "excel is fun" on youtube.

    i highly recommend it to everyone on this forum who are beginners/intermediate with excel. its a college level excel course that has thousands of hrs of lessons in financial analysis, statistics, shortcuts, vlook up, pivot tables, etc.

    if you have the time, just dedicate an hr a day for it and youll get good with excel.

  • In reply to frgna
    monkeyoasis's picture

    frgna:
    First remove your F1 key, because it only gets in the way.

    Second, get a macro book that has shortcuts for all your formatting needs and practice doing all that real quick - make a really pretty formatted fake sheet and start from a blank sheet and practice getting from blank to pretty, time yourself to see when you've peaked out.

    Favorite functions/strokes and ones that are probably most useful for consulting (no particular order):

    F2
    F9
    F4 (learn how to use all absolutes properly)
    F5 then Alt+S then O, X, G, E (goes to all hard codes - then you can make them blue)
    Text to columns (CRITICAL for efficiency)
    Conditional formatting in general
    Ctrl + pageup / pagedown (always shocked how many people don't know this)
    Choose
    v/h lookups
    Using if statements in general
    Using min/max functions (sometimes much cleaner/more efficient than an if statement)
    Sumproduct/Sumif (useful for doing wtd averages)
    Truncate
    Proper
    Lower
    Upper

    That should be plenty good to impress your friends. Also just get really really fast using arrows/ctrl to bop around.

    I think even more important than shortcuts is general excel etiquette/best practices - things like never enter a number into part of a cell, never hide a column/row, etc. I can go more into that if you like.

    Thanks for this!

    I'll also start at MBB this year, after having done an internship, so I'm fully aware of the importance of excel etiquette and best practices. Care to share a few more? It would be incredibly useful!

  • In reply to monkeyoasis
    pplstuff's picture

    monkeyoasis:
    frgna:
    First remove your F1 key, because it only gets in the way.

    Second, get a macro book that has shortcuts for all your formatting needs and practice doing all that real quick - make a really pretty formatted fake sheet and start from a blank sheet and practice getting from blank to pretty, time yourself to see when you've peaked out.

    Favorite functions/strokes and ones that are probably most useful for consulting (no particular order):

    F2
    F9
    F4 (learn how to use all absolutes properly)
    F5 then Alt+S then O, X, G, E (goes to all hard codes - then you can make them blue)
    Text to columns (CRITICAL for efficiency)
    Conditional formatting in general
    Ctrl + pageup / pagedown (always shocked how many people don't know this)
    Choose
    v/h lookups
    Using if statements in general
    Using min/max functions (sometimes much cleaner/more efficient than an if statement)
    Sumproduct/Sumif (useful for doing wtd averages)
    Truncate
    Proper
    Lower
    Upper

    That should be plenty good to impress your friends. Also just get really really fast using arrows/ctrl to bop around.

    I think even more important than shortcuts is general excel etiquette/best practices - things like never enter a number into part of a cell, never hide a column/row, etc. I can go more into that if you like.

    Thanks for this!

    I'll also start at MBB this year, after having done an internship, so I'm fully aware of the importance of excel etiquette and best practices. Care to share a few more? It would be incredibly useful!

    More:
    -goal seek
    -data table
    -solver
    -custom number formatting (ex: [=1] Y; [=0] N)
    -indirect
    -char(10)
    -offset
    -row, rows
    -column, columns
    -yearfrac
    -eomonth
    -value
    -datevalue
    -text (and any weird formating that does with it "mmm", "mmmm", "dd", etc)
    -trace precedents/dependents
    -data validation
    -combo boxes (both activeX and form control versions)
    -user forms
    -advanced chart techniques (waterfalls, conditional coloring of series, trend lines)
    -print area, page layout view
    -freeze panes
    -grouping
    -built-in smart art
    -remove duplicates
    -get external data
    -how to handle circular references (# of iterations)

    I can't stress enough... sumproduct, index/match, v/h lookup, sumif(s), countif(s)
    There are so many cool excel functions.

    I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.

The WSO Advantage - Management Consulting

Consulting Case Interview Guide

Real Cases Revealed from Top Firms. Learn More.

GMAT Prep Promo

$333 Off GMAT Training. Learn More.

Resume Help from Consulting Pros

Land More MBB Interviews. Learn More.

Find Your Consulting Mentor

Realistic Mock Case Interviews. Learn More.

  • The Greater Fool's picture

    If you don't have the coding background to write your own VBA, play around with recording macros. They can save you a lot of time.

  • Santini's picture

    Thanks a lot guys, I really appreciate it. It is really nice to have a feel for the most important functions and start from there. So thanks again :)

  • Karot's picture

    Ozgrid provides great primers on using the above functions and more

  • PorcineAviation's picture

    What differentiates good versus mediocre first years in Excel is not formatting but accuracy. The best stuff to know is shortcuts/functions that mean if you have 2 hrs for a task, you can spend 1 hr executing and 1 hr checking, instead of 90 mins executing but only 30 mins checking.

    Therefore, get comfortable with copy and paste special, sums, sumifs, index/match (never use vlookup or indirect/offset/address) and F4. In 3 weeks this is realistically what you can learn whilst not being a loser.

  • kobalt's picture

    Thanks for the tips. Any similar advice for PowerPoint?

  • In reply to mashed potatoes
    Santini's picture

    mashed potatoes:
    i also come from a life sciences background, so excel was a bit foreign for me. i referred to "excel is fun" on youtube.

    i highly recommend it to everyone on this forum who are beginners/intermediate with excel. its a college level excel course that has thousands of hrs of lessons in financial analysis, statistics, shortcuts, vlook up, pivot tables, etc.

    if you have the time, just dedicate an hr a day for it and youll get good with excel.

    Thanks man, he is really cool :D

  • TylerT's picture

    Proofing your model, keeping your assumptions and calculations clean, and beyond all else being able to explain you analysis.

    Chances are if you got into MBB you can find all your answers on Google....but what you can't find is a mindset that has you ensuring every aspect of the model is clear and without any bugs.

    TT

  • longn's picture

    Other than knowing formulas - make sure you know how to structure the data so that it's easy to perform the formulas (such as creating templates for your client so when they send you back the data, it's an easy plug).

    This is also crucial : Presentation and data visualization. Make sure your models are clean. People make judgments on the quality of your analysis based on how things look sometimes (looks like shit...probably shit type stuff.)

    Making data 'digestible' and easy to understand is the whole point of doing an analysis - you have to sift through piles of data to provide the client insights-.

    Good luck!

  • In reply to PorcineAviation
    chimpout's picture

    PorcineAviation:
    What differentiates good versus mediocre first years in Excel is not formatting but accuracy. The best stuff to know is shortcuts/functions that mean if you have 2 hrs for a task, you can spend 1 hr executing and 1 hr checking, instead of 90 mins executing but only 30 mins checking.

    Therefore, get comfortable with copy and paste special, sums, sumifs, index/match (never use vlookup or indirect/offset/address) and F4. In 3 weeks this is realistically what you can learn whilst not being a loser.


    why absolutely no indirect/offset?
  • In reply to longn
    Santini's picture

    longn:
    Other than knowing formulas - make sure you know how to structure the data so that it's easy to perform the formulas (such as creating templates for your client so when they send you back the data, it's an easy plug).

    This is also crucial : Presentation and data visualization. Make sure your models are clean. People make judgments on the quality of your analysis based on how things look sometimes (looks like shit...probably shit type stuff.)

    Making data 'digestible' and easy to understand is the whole point of doing an analysis - you have to sift through piles of data to provide the client insights-.

    Good luck!

    Thank a lot, I really appreciate it!

  • In reply to chimpout
    pplstuff's picture

    chimpout:
    PorcineAviation:
    What differentiates good versus mediocre first years in Excel is not formatting but accuracy. The best stuff to know is shortcuts/functions that mean if you have 2 hrs for a task, you can spend 1 hr executing and 1 hr checking, instead of 90 mins executing but only 30 mins checking.

    Therefore, get comfortable with copy and paste special, sums, sumifs, index/match (never use vlookup or indirect/offset/address) and F4. In 3 weeks this is realistically what you can learn whilst not being a loser.


    why absolutely no indirect/offset?

    Both indirect and offset are very powerful. A lot of people don't like indirect because of two reasons, 1) a lot of people don't understand it, 2) it's a volatile functions (this means it's constantly recalculating itself - not bad when you use it once, but if you have a large workbook full of volatile functions it'll increase calc time significantly. Other examples are now(), date(), etc. I rarely use indirect because it's volatile.

    Offset is another function that average excel users won't understand, hence won't use it. It's extremely useful when designing complex array functions, or converting columns/rows of data into other shapes. A recent example of using offset was to calculate debt amortization while adding new debt every month and subtracting defaulted debt + early payments. Most of the time, index/match or vlookup is better - offset is just another tool in your tool belt.

    I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.

  • In reply to pplstuff
    chimpout's picture

    lol, how big are your workbooks that you need to optimise by excluding volatile functions?

  • In reply to chimpout
    pplstuff's picture

    chimpout:
    lol, how big are your workbooks that you need to optimise by excluding volatile functions?

    90% of the time my models are 20-50MB (~3 min full re-calc times... ~15 min full re-calc with data tables). On the large side ~200MB (though, these files get this large because we have lots of historical data). These are energy models.

    I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.

  • In reply to pplstuff
    chimpout's picture

    To unlock this content for free, please login / register below.

    Sign In with Facebook Sign In with Google

    Connecting helps us build a vibrant community. We'll never share your info without your permission. Sign up with email or if you are already a member, login here Bonus: Also get 6 free financial modeling lessons for free ($200+ value) when you register!
  • In reply to chimpout
    pplstuff's picture

    I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.

  • BlackHat's picture

    I hate victims who respect their executioners

  • In reply to chimpout
    pplstuff's picture

    I'll do what I can to help ya'll. But, the game's out there, and it's play or get played.