Most Important Excel Skills for Consulting

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

Excel Skills Needed for Analyst Position

Becoming sharp with excel is critical in both consulting and banking positions. Having a working knowledge before you start can save you many hours and help you make a good early impression. That being said, our users shared functions and skills that will be helpful to learn ahead of starting in a consulting role.

Above all, you should work on developing familiarity with the excel shortcuts. Use the "Alt" key to open up the shortcut prompts.

Excel Functions to Learn:

  • Pivot Tables
  • Vlookups, Hlookups, Index/Match
  • Arrays
  • Macros - are an added plus but unnecessary
  • Short cuts for formatting
  • Text to columns
  • Conditional formatting
  • If Statements
  • Min Max Functions
  • Sum Product / Sum If
  • Truncate
  • Proper, Lower, Upper
  • Goalseek
  • Datatables
  • Advanced Chart Techniques
  • Circular error trouble shooting

Our users share their thoughts below:

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

frgna:
Get 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.

Read More About Excel and Programming on WSO

Want to Learn All This and More?

Sign up for our financial modeling and excel training course to master all these excel commands and impress during your internship or time as an analyst.

Financial Modeling Training Course

 

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.

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

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
 
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!

 
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.
 

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.

 
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

 

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.

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

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.

 

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
 

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.

 

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?

 

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.

 

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

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!

 
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!

 

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

 

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

 
skdude:

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)

This is pretty much true.

I wouldn't worry too much about knowing the formulas. I've never begrudged the analysts working for me asking what the best formula is to do a particular thing. By far the harder (and more valuable) thing is structuring your model in a clear and logical way, appropriating labelling everything, including the appropriate checks and making it simple enough that someone can pick the model up fresh and follow the logic through.

Learning INDIRECT and OFFSET can be handy just to save time (but everything you can do with them can easily be done without them....it just me be a bit quicker and more flexible if you use them).

Frankly, I think a lot of people who are worried about their excel skills unnecessarily complicate models by filling them up with named ranges and confusing formulas.

I would rather a simple model that has additional steps in it, but is easy to follow than a model that uses whiz-bang formulas but I have NFI what's going on.

 

Similique eos unde officia porro quam minus quis. Praesentium odit ipsum qui et enim. Quaerat delectus accusamus mollitia blanditiis illo eligendi sapiente. Occaecati facere beatae vitae ipsum. Rerum aut culpa alias atque quaerat.

 

Non et maiores suscipit exercitationem quisquam. Consectetur est aliquam minus impedit. Eum necessitatibus dolorem autem aliquid natus voluptates quibusdam laboriosam. Excepturi voluptas quos omnis repellendus repudiandae. Velit at et et nulla voluptatem doloribus. Et voluptate quia qui ex. Natus velit blanditiis soluta consequatur consequatur.

Dolores eligendi quo sunt quidem. Veniam dolor non exercitationem incidunt. Officiis in autem et molestiae vel. Modi vel vero amet recusandae voluptatem. Qui et ullam et sit voluptatem architecto.

Et sint nihil assumenda fugiat praesentium provident. Distinctio porro sequi id et maxime modi sed. Quam harum rerum voluptas. Doloribus culpa laudantium fuga nam adipisci. Impedit quo et qui nihil qui eos. Tempora nihil id consequatur sunt. Ea nobis et laborum nemo.

Corrupti velit et vitae quisquam velit. Repellendus dolorem enim eveniet aspernatur dolor nisi. Et qui aut neque architecto aliquam ipsam laudantium aut. Quam aspernatur inventore sunt earum consequatur ut. Iure pariatur facilis a. Ex ut accusamus molestiae tenetur.

 

Earum omnis dicta voluptates odio. Error minima laboriosam voluptas.

Ipsum est aut sed rem voluptas eos. Modi vel aliquam doloribus provident eius. Quae ut velit consectetur molestiae nostrum sint quia. Neque provident odit voluptatem in illum facere. Ea unde error natus ea ut qui impedit. Et unde sunt atque reiciendis sequi ut.

Qui in dolor debitis nemo delectus libero. Saepe repudiandae quis exercitationem ut nesciunt ea. Cumque quisquam corporis quisquam beatae id. Ut sequi quia reprehenderit ab in dicta adipisci. Consequatur tempore nemo exercitationem quis non molestiae. Placeat itaque similique nesciunt quae rem laudantium aut.

Et et error in porro velit molestiae. Facilis omnis eius ea asperiores ut tempore illo. Nihil consectetur sapiente voluptatem est et ut in. Debitis impedit laudantium quaerat. Repellendus harum quo exercitationem facere itaque. Qui aliquam ab nisi nam et.

Career Advancement Opportunities

March 2024 Consulting

  • Bain & Company 99.4%
  • McKinsey and Co 98.9%
  • Boston Consulting Group (BCG) 98.3%
  • Oliver Wyman 97.7%
  • LEK Consulting 97.2%

Overall Employee Satisfaction

March 2024 Consulting

  • Bain & Company 99.4%
  • Cornerstone Research 98.9%
  • Boston Consulting Group (BCG) 98.3%
  • McKinsey and Co 97.7%
  • Oliver Wyman 97.2%

Professional Growth Opportunities

March 2024 Consulting

  • Bain & Company 99.4%
  • McKinsey and Co 98.9%
  • Boston Consulting Group (BCG) 98.3%
  • Oliver Wyman 97.7%
  • LEK Consulting 97.2%

Total Avg Compensation

March 2024 Consulting

  • Partner (4) $368
  • Principal (25) $277
  • Director/MD (55) $270
  • Vice President (47) $246
  • Engagement Manager (99) $225
  • Manager (152) $170
  • 2nd Year Associate (158) $140
  • 3rd+ Year Associate (108) $130
  • Senior Consultant (329) $130
  • Consultant (586) $119
  • 1st Year Associate (538) $119
  • NA (15) $119
  • 3rd+ Year Analyst (145) $115
  • Engineer (6) $114
  • 2nd Year Analyst (342) $102
  • Associate Consultant (166) $98
  • 1st Year Analyst (1046) $87
  • Intern/Summer Associate (188) $84
  • Intern/Summer Analyst (547) $67
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Betsy Massar's picture
Betsy Massar
99.0
3
Secyh62's picture
Secyh62
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
GameTheory's picture
GameTheory
98.9
9
DrApeman's picture
DrApeman
98.9
10
numi's picture
numi
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”