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:
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.
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
- Most Important Excel Short Cuts In IB
- What Are The 3 Most Important Skills Needed To Be An Investment Banker?
- Programming/Technical Skills For Finance: SQL And Python
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.
pivot table and knowing that f9 refreshes all cells should do for a consultant.
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.
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.
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.
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).
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 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
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.
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 :)
Ozgrid provides great primers on using the above functions and more
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.
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.
Thanks for the tips. Any similar advice for PowerPoint?
Best way to boost excel skills? (Originally Posted: 10/06/2006)
Anybody have advice as far as classes, books or another way of learning excel?
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'm working through this book: Financial Modeling Using Excel and VBA (Wiley Finance) It goes from basics to writing macros
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.
Ya its a pretty good book the only thing its missing is Comps.
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
Learning Excel for Consulting (Originally Posted: 04/12/2010)
Is it worth my time to spend a chunk of time this summer trying to master Excel? It's not like I've never used it before, but I'd like to be able to hit the ground running.
Also, if it's worth my time, what version should I be using, and what source? I don't want to do a full-on, banker-tailored training if I don't need to learn in that much depth.
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.
Can someone guide as to what would be a good source for learning Excel for modeling? For either banking or consulting?
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?
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
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).
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.
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.
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.
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.
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).
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.
Fuck, my bad.
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!
Excel Shortcuts - Best Overview ever (Originally Posted: 07/05/2010)
Hey there, of course I know this thread: http://www.wallstreetoasis.com/forums/keyboard-shortcuts-im-a-huge-nerd
However, I am looking for a consolidated list of the most useful shortcuts for Excel 2003 that I can print out easily. What do you guys think is the best one with the shortcuts you really need on a daily basis?
Thanks a lot in advance. :)
Thank a lot, I really appreciate it!
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.....
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.
Thanks a lot.
If someone else has a good list, please share. :)
Wall Street Prep excel shortcut PDFs:
2003: http://www.WallStreetPrep.com/extrafiles/finmodeling/downloads/xlshortc… 2007: http://www.WallStreetPrep.com/extrafiles/finmodeling/downloads/xlshortc…
Alt+Tab
Excel Practice (Originally Posted: 02/05/2012)
I'm starting at a consulting firm pretty soon and would like to try and improve at excel beforehand. Has anyone got some ideas on models I could try to build/potential data-sets to play with? From previous posts on here and elsewhere formulae, functions and tools such as Data Validation, What-If Analysis, Groups/Filters and Pivot Tables are apparently useful. Does anyone have any ideas on some practice modelling I could do to learn the basics of these parts of excel (and any others that might be useful)?
Thanks.
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/equity.html
perhaps you can f*ck around with those spreadsheets a bit!?
Thanks for sharing!!
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
Thanks for sharing!!
Thanks for sharing!!
Excel skills (Originally Posted: 07/18/2012)
Wondering what basic excel skills needed as an entry level consultant. Specifically, in Huron Consulting Healthcare group.
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
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
Either way, never hurts to be MOS certified for Excel.
Excel? Shortcuts, etc. (Originally Posted: 10/12/2012)
I start a short consulting internship next week and am pretty terrible using excel so I thought I'd use the weekend to prepare a little. Does anyone have any advice on what shortcuts/excel knowledge is needed on the job? Any insight would be much appreciated. Thanks.
I bookmarked this one:
http://www.wallstreetoasis.com/blog/stop-using-your-mouse
Pretty helpful in my opinion. And yeah, use the search function..
This should be helpful. PM if you have any further questions.
ctrl+shift+1
Download and use PowerPivot. Much better way to add value vs. just learning to be fast.
what are the top 3 things you use Pivot power for?
BIWS Excel course. Do it.
RE Consulting Excel Skills (Originally Posted: 03/11/2014)
Hi all,
I've been a daily reader of the site for quite some time now but have never posted until now. I was wondering what skills, particular functions/equations, or models are most likely to pop up in an excel test from a RE consulting firm for an analyst position?
Any insight would be much appreciated, including practice models or videos.
Consulting is pretty generic, what type of consulting (acquisition/due diligence, fee development, market research, corporate advisory, environmental, property tax, etc..)?
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.
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.
Excel Skills Needed for Day 1, Day 30, Day 365 (Originally Posted: 01/08/2015)
Hi WSO,
I will be starting with a top consulting firm in a few weeks. I will be in management consulting/advisory and was wondering what Excel formulas and skills do I need to brush up on.
Thanks, R
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!
As the poster above said, don't stress it. Plenty of threads on this mate.
http://www.wallstreetoasis.com/forums/most-important-excel-skills
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
Consulting? Eh... just focus on your powerpoint skills ;)
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.
Ut omnis magni qui. Rerum quis animi quia officiis est animi eos magnam. Aut voluptatem quisquam libero sit quas. Autem rerum inventore minima fugiat.
Non et quisquam veritatis. Expedita vel vitae quia voluptatem qui voluptatum. Qui asperiores perferendis est. Reprehenderit aut maxime adipisci qui molestiae itaque voluptas earum. Eius sint voluptatem saepe. At repudiandae culpa rerum magni magni.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Dolores dicta distinctio nihil itaque voluptatem quae. Adipisci quisquam totam officiis officiis labore. Ut mollitia harum ex reiciendis ab nisi consequatur.
Iure doloremque qui totam sint incidunt. Quia recusandae cupiditate ut laboriosam excepturi. Nisi quia corrupti corporis dolorum quibusdam natus aspernatur minima. Aspernatur ipsa debitis deserunt voluptate nesciunt aut. Vitae architecto architecto omnis omnis. Laboriosam aut vitae aut maxime veritatis. Veniam pariatur deserunt quidem et voluptatem in. Ab dolores consequuntur veniam neque quae quis minima.
Assumenda voluptas ipsa velit quia ad qui sint natus. Veritatis maxime aliquam necessitatibus assumenda voluptas a necessitatibus impedit. Laboriosam tempore inventore veritatis vel saepe eaque dolorem. Ad consequuntur ullam consequatur fugiat. Et distinctio consectetur qui omnis. Ut molestiae quo debitis aut voluptas.
Optio illo sed corporis nam. Dolorem ut nulla sint porro. Ut dolores in voluptas libero. Molestiae pariatur odit qui culpa pariatur consequatur.
Assumenda officiis repellat ab ut. In dolor in sed deserunt cupiditate. Temporibus et voluptatem sunt ut repudiandae. Iure non a natus consequatur quasi sapiente debitis.
Impedit aut mollitia tenetur expedita tempora tenetur autem. Illo sapiente dolores laudantium. Dolorem voluptates similique totam quae nam quae aut. Reprehenderit eum ducimus est velit. Autem voluptas est deserunt earum illum. Tempora corporis reiciendis sint consectetur.
Illum doloribus maiores sed consequatur velit. Soluta suscipit molestiae odio est. Dolorem nobis qui numquam et dolorem. Quos velit maiores odit iure sequi ad.
Consequatur in neque distinctio similique impedit. Deleniti praesentium consequatur animi libero vel.