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 (81)

12/30/12

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

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

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

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

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!

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.

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.

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/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/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?
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.

1/18/13

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

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.

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?

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

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

upstream? what are you modelling exactly?
1/19/13

chimpout:
upstream? what are you modelling exactly?

All things related to solar energy. Parabolic trough, linear fresnel, solar tower, photovoltaics.

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

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.

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

Thanks for the tips. Any similar advice for PowerPoint?

1/19/13

The best way to learn excel is through endless hours of practice. I remember when i first started work, it was like "what the !!!" and as time went on, I started to design my own spreadsheets, make fancy links, automate reporting etc.

Use the help file and get hands on training.

1/19/13

I'm working through this book: Financial Modeling Using Excel and VBA (Wiley Finance) It goes from basics to writing macros

1/19/13

I've got the same book and it's pretty good.

But really the best way is to just practice, and check out other people's macros and stuff to try to rebuild them.

Either you sling crack rock or you got a wicked jump shot

1/19/13

Ya its a pretty good book the only thing its missing is Comps.

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

Excel 2007 and yes, regardless of what part of finance you pursue (PE, HF, IB, Consulting) excel is a necessary skill. The more you know, and the quick you can use it, the better off you will be. I don't think you need to spend 10 hours a day for 2 months learning, but you should devote maybe 5-10 hours a week learning excel in the context of modeling.

1/19/13

Can someone guide as to what would be a good source for learning Excel for modeling? For either banking or consulting?

1/19/13

What has the conversion rate to 2007 been, Ive heard mixed opinions, some saying that their office is all 2003, some saying 2007.. As someone who has never used 2003 are the differences from 2007 massive or manageable if i have learned all the 2007 shortcuts?

Also, what changes is 2010 bringing?

1/19/13

all i know about 2010 is that it has sparklines (and microsoft, being microsoft, has tried to patent them)... consultants are going to be all over that shit

1/19/13

just googled excel 2010 sparklines, looks pretty sweet but not sure how/if it'll be incorporated into the powerpoint add-ons (copy/paste from an excel table into pp is heresy).

1/19/13

I would learn the basics of Excel and Access for consulting. Being able to create PivotTables and use the Data Analysis tools are invaluable skills, especially when you're trying to summarize large amounts of information or uncover opportunities for improvement. I'm sure whatever firm you join will train you in these areas, but it wouldn't hurt to have a basic knowledge of the features.

As far as the difference between program years, I've noticed that Excel 2007 is missing some a few of the add-ons that 2003 had. Then again, I'm using an Apple so that may be the reason why.

1/19/13

Office for Mac is absolutely unusable for any serious excel or powerpoint work. Get Paralles or VMWare and run Windows Office 2007 on your mac.

1/19/13

which books do you guys recommend? i know the basics of excel, but would like to enhance my skills. and i have to learn access.

1/19/13

This is sort of a weird question, because you gave a definitive answer to it in a recent thread.

One of the places I interned at - somewhere between a boutique and a second-tier (OW-ish) firm - required intense Excel skills, including very strong VBA. I hope you're luckier (that absolutely sucked), but I don't know if you can over-learn Excel.

My firm used 2003; apparently 2007 retained the shortcuts but they're no longer intuitive at all. Also, sparklines look fucking nasty. That's going to be sick.

One of those lights, slightly brighter than the rest, will be my wingtip passing over.

1/19/13

i dont think i did...not sure what you saw. but is there a book you guys recommend i read or like some kind of online tutorial thing?

1/19/13
thedude86:

i dont think i did...not sure what you saw. but is there a book you guys recommend i read or like some kind of online tutorial thing?

I was talking about petergibbons (I was answering his question). I have Excel 2003 Power Programming with VBA, but I only recommend it if you're actually going to be doing intense VBA programing (i.e. building a complex GUI, creating automatically refreshing tables/charts across multiple sheets, connecting to Access or Oracle, etc.). If you're just going to be writing a couple of macros, you can google how to do them when the need arises. There are some books focused on formulas and functions that you could look at (though I don't know how good they are) if you're not going to be doing much VBA but want to get the data analysis stuff down.

I consider my Excel skills to be almost banker-level (though if banker-level includes VBA expertise, maybe not), but I learned it by doing. I'd say some kind of hands-on tutorial is better to pick up the skills and a book is fine if need to look up how to perform some concrete task (though google is a decent stand-in for that).

One of those lights, slightly brighter than the rest, will be my wingtip passing over.

1/19/13

Yeah, I started this thread in April (when I was in undergrad, before I started). I'm now 6 months in, and know what I should've learned back in April.

Life, liberty and the pursuit of Starwood Points

1/19/13

Fuck, my bad.

One of those lights, slightly brighter than the rest, will be my wingtip passing over.

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!

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!

1/19/13

There was a post like this not too long ago.

Someone had a link to a PDF of TTS excel shortcut---- However, I think it was 2007 excel short cuts.

Try to see if you can find it.....

1/19/13

Just make your own list.

Most places have proprietary shortcuts anyways from a hodgepodge of programs and proprietary macros that won't be on any TTS list.

1/19/13

Thanks a lot.

If someone else has a good list, please share. :)

1/19/13

Wall Street Prep excel shortcut PDFs:

2003: http://www.WallStreetPrep.com/extrafiles/finmodeling/downloads/xlshortcuts03.pdf
2007: http://www.WallStreetPrep.com/extrafiles/finmodeling/downloads/xlshortcuts07.pdf

1/19/13

Alt+Tab

I hate victims who respect their executioners

1/19/13

not sure if it's what you're looking for, but NYU Stern has a Valuation course online:
http://pages.stern.nyu.edu/~adamodar/New_Home_Page...

perhaps you can f*ck around with those spreadsheets a bit!?

1/19/13

I'm in the same boat as you, so whatever you find that is useful please fill me in.

Here is a collection of spreadsheets that you might be able to play around with:
http://www.exinfm.com/free_spreadsheets.html

1/19/13

No idea but I'd think if you don't suck at excel already you can just learn them on the job. I can't imagine an interviewer is going to ask you excel questions

1/19/13

Yes surely, Interviewer will not ask excel related questions from you and basis excel skill are not very difficult or technical, so don't worry. :p

1/19/13

Either way, never hurts to be MOS certified for Excel.

1/19/13

I bookmarked this one:
http://www.wallstreetoasis.com/blog/stop-using-you...

Pretty helpful in my opinion. And yeah, use the search function..

1/19/13
above_and_beyond:

I bookmarked this one:
http://www.wallstreetoasis.com/blog/stop-using-you...

Pretty helpful in my opinion. And yeah, use the search function..

This should be helpful. PM if you have any further questions.

1/19/13

ctrl+shift+1

This to all my hatin' folks seeing me getting guac right now..

1/19/13

Download and use PowerPivot. Much better way to add value vs. just learning to be fast.

1/19/13

what are the top 3 things you use Pivot power for?

1/19/13

BIWS Excel course. Do it.

1/19/13

Consulting is pretty generic, what type of consulting (acquisition/due diligence, fee development, market research, corporate advisory, environmental, property tax, etc..)?

1/19/13

I would say mostly an acquisitions model. Something like, "determine IRR and cash on cash ratio" given income generating property, current income/expenses, existing debt on property and new debt.

you'd have to create a pro forma (projected income/expenses), cash flow statement (unlevered then levered), and then determine IRR.

1/19/13

If you go to the Breaking Into Wall Street website I have found that they have very good material for learning to model real estate in excel. It isn't cheap, but it is worth it.

1/19/13

Vlookup, Hlookup, Pivot Table, ifs (i.e. sumif, countif, etc.), maybe index match? Nothing too complicated here if you need to know it you will figure it out. Don't stress and congrats on the new job!

1/19/13

As the poster above said, don't stress it. Plenty of threads on this mate.
http://www.wallstreetoasis.com/forums/most-importa...

"So who lost the hundy?"

1/19/13

If you come in and tell me about how you spent your last few days/weeks/months of freedom brushing up on excel skills I'm going to laugh at you. Enjoy life man! You'll have plenty of time to learn and burn the midnight candle with excel when you start.

#1 thing to do before starting - traveling
#1 regret for people after starting - didn't travel enough

1/19/13

Consulting? Eh... just focus on your powerpoint skills ;)

1/19/13

You learn it all on the job. Complexity comes from figuring out how to structure models and use simple formulas to flow things through which you get better with time. 99% of formula use is IF statements, vlook-up, countif, sumif, index match, and basic algebra (division, multiplication etc)

1/19/13
1/19/13
1/19/13
1/19/13
1/19/13
Add a Comment
WallStreet Prep Master Financial Modeling