1/1/13

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

Comments (28)

Best Response
12/30/12

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

The WSO Advantage - Management Consulting

Consulting Case Interview Guide

Real Cases Revealed from Top Firms.

GMAT Prep Promo

$333 Off GMAT Training.

Resume Help from Consulting Pros

Land More MBB Interviews.

Find Your Consulting Mentor

Realistic Mock Case Interviews.

12/30/12

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

12/30/12

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.

12/30/12

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.

12/30/12

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.

12/30/12

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).

12/31/12

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
1/1/13

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
1/1/13

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.

GMAT Prep Promo

$333 Off GMAT Training.

Resume Help from Consulting Pros

Land More MBB Interviews.

Find Your Consulting Mentor

Realistic Mock Case Interviews.

1/1/13

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.

1/1/13

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 :)

1/2/13

Ozgrid provides great primers on using the above functions and more

1/3/13

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.

1/4/13

Thanks for the tips. Any similar advice for PowerPoint?

In reply to mashed potatoes
1/8/13

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

1/8/13

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

1/9/13

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
1/12/13

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
1/12/13

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
1/13/13

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
1/18/13

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

In reply to chimpout
1/18/13

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
1/19/13

trading?

the largest financial models i've seen are pushing 20, so 200MB is scary. this is in standard valuation work, so the most quant we ever get is using some simulation add-ins that don't really add to the workbook size.

with that much data, is excel really the right tool?

In reply to chimpout
1/19/13

chimpout:
trading?

the largest financial models i've seen are pushing 20, so 200MB is scary. this is in standard valuation work, so the most quant we ever get is using some simulation add-ins that don't really add to the workbook size.

with that much data, is excel really the right tool?

Not for trading.

We do a lot multi-variate non-linear regressions, but I wouldn't consider them 'heavy' quant models (though a mathematician, econ PhD, and about three engineers are the ones guiding most of my models). I think R, Matlab, Minitab, Stata, etc. would be better in some cases. Everyone is just so much more comfortable with excel and VBA that we resist using multiple tools.

We do the occasional MCS, but it's not demanded highly from the higher up guys.

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

1/19/13

Alt+Tab

I hate victims who respect their executioners

In reply to pplstuff
1/19/13

pplstuff:
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.

My dismissal of indirect(offset) based on having learnt it, used it, realised its limitations too late in the model/project lifecycle, and then watched consultants working for me go through the same frustrating process.

Three reasons I discourage its use.

1. Volatile functions, as you mention

2. If you change the structure of the underlying sheet (e.g. insert a column/row) you need to update all your indirect/offset formulae (unless you've set them up dynamically, in which case, why bother using them)

3. Nightmare to audit for managers: too often I find mistakes in models junior consultants have built for me which are a complete faff to resolve because indirect(offset) is opaque. You can't trace precedents, you can't immediately reference the source cells.

I'm specialised in financial services - I do some pretty large, complicated Excel work (risk modelling, valuation etc.) but am still yet to find a justifiable reason for not using simple sum/sumifs 75% of the time, with the occasional index(match) for mapping, and getpivot for quick translation of database structures into output tables.

In reply to pplstuff
1/19/13

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

  • Facebook
  • Google Plus
  • LinkeIn
  • Twitter
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
1/19/13

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

What's Your Opinion? Comment below:

Login or register to get credit (collect bananas).
All anonymous comments are unpublished until reviewed. No links or promotional material will be allowed. Most comments are published within 24 hours.
WallStreet Prep Master Financial Modeling