Most useful excel modeling functions
To all the current bankers...
What are the top five most important functions you use in excel while modeling... lets get a list going.
Most Important Excel Functions for Bankers
The Excel functions you use everyday will depend on your job and level of expertise with Excel. The longer you’re on the job, the easier these will become. Here are some of the most popular Excel functions for Investment Banking:
- OFFSET (returns a single cell or range of cells a certain number of rows and columns from a reference cell or range)
- INDEX (returns a value based on the intersection of an array/column and row)
- VLOOKUP (vertical lookup – retrieve data from a cell in another column)
- IF (returns a value if a condition is true)
- SUMIF (adds all numbers in a range if they meet a certain condition)
- MATCH (searches for a value in a range of cells and returns its position)
- INDEX MATCH MATCH (combines index and match functions, similar to VLOOKUP but works with columns and rows)
- ARRAY (a group of cells referenced in another formula)
- INDIRECT (use to create a reference that won’t change if you add rows or columns)
Other Excel Tips
- Alt E S (paste special)
- Alt Tab (toggle between open applications)
- Alt F4 (save and close)
- Shift Ctrl + (add row)
- Shift Spacebar (select row)
- Ctrl – (delete row)
- Learn VBA
- Pivot tables
Recommended Reading
- Excel Tools Add-ins and Macros
- Business Insider Excel Tricks
- Most Important Functions in Excel to Learn
- Most Important Excel Short Cuts in IB
Want to improve your financial modeling skills?
Check out WSO’s side-by-side comparison of financial modeling courses and get a WSO discount.
Financial Modeling Course
SUM
Well I'm not a FT Banker, am interning in mezz finance:
I'd say IF
Cmon guys... I'm thinking more along the lines of Hlookup, Vlookup, Choose, Match, etc. Anyone?
NEVER lose your BlackBerry www.conveniencesoftware.com
What kind of modeling (i.e. financial statement, DCF, LBO, etc.)? ISERROR ftw
Offset. Index is baller too.
alt+f4
Obviously INDEX is key for building your toggle/various cases and v-look up saves me a lot of time
Not sure how much modeling you do but there really arent that many complicated functions (maybe a few long IF strands) from my personal experience and ive worked with some pretty complex LBO models
Not a banker. I use SUMIF in place of VLOOKUP. Sometimes VLOOKUP just doesn't work.
index + match when any of the lookups don't cut it, index, and offset
IF and average and Average IF, and other ARRAY formulas.
Sumif and sumproduct are also sweet.
anybody who "builds" a "model" by putting a VLOOKUP statement into a spreadsheet cell is a clown. just throw the damn thing into a macro. how the fuck else will you ever debug your "model"
Or learn vba.
MIN and MAX are very useful as well!!! It saves me from those nested IF statements sometimes....
Indirect saves a ton of time when doing comps
Excel functions to learn (Originally Posted: 07/08/2011)
Hey guys,
so I'm currently a SA on the FICC desk. I went into this gig without any major excel experience and I've had a lot of help from the traders but I'd like to do some training for myself Now, what functions do you recommend I learn as I'll be doing lots of it?
Thanks.
everyone loves pivot tables (and anything simpler than that, anything from if statements to filters)
everyone loves pivot tables (and anything simpler than that, anything from if statements to filters)
VBA
you can always reference help sheets for functions since you will only be using them sparingly. The real meat is the shortcuts. Try to be fast at rearranging rows, columns, pasting special, navigating the sheet, hiding row/col, grouping, ESC/F2.
vlookups might be useful sumif, sumtoproduct are also useful
Thanks guys.
drnoeyedea - what do you mean by reference help sheets for functions? Do you mean using the help function in Excel?
Walkio pm me your email I have things that may be of use.
Most important functions in EXCEL (Originally Posted: 04/03/2012)
What are the most important or most frequently used functions in Excel?
sum average +-*/
just use your mouse
VLOOKUP, SUMIF, IF
The IF and SUM functions. How can anyone argue this?
VLOOKUP, SUM, IF, CHOOSE. Not in any particular order.
INDEX. It's far more flexible.
Genuine question - how can you substitute INDEX for VLOOKUP if the variable you're looking for is always in a different place? I was under the impression that Index relied on searching the same row every time?
For example - pulling in the percentage change between 2 dates. I would set it up to have the dates as an input then use VLOOKUPs to find share prices on each date. How would you set this up using INDEX?
This was in reference to case switches where index is simple, light and easy to edit (e.g., adding another case without altering the formula). But yea, as stated above, index match.
Index match...but that's not really what we were discussing.
Other than sumif i'd say index and indirect. Indirect has changed the way i look at the world.
FIGHT FIGHT FIGHT FIGHT
Paste Special
Without a doubt. I use Alt+E+S like a million times no matter what the document is
=RTD
come at me bro
Alt + Tab
Alt + F4 :)
index or offset, choose kinda sux compared vs the two. index is also significantly better and lighter on the model compared to vlookup and hlookup
Alt+Tab is clutch.
just do index match
Index match match ftw!
Big fan of index. My data always seems to be in the opposite format that I need it in.
^Agreed...Use Index/Match combo to essentially replace any traditional lookup
Offset match match. What does index match offer over it (not meant sarcastically, genuinely wondering)?
With an index match match you will never have to indicate how many rows to offset because the index will tell you exactly what row and column your data is in because you will match the row and column to value. This is helpful because your lookup will be dynamic so if you insert another row or column in your dataset your lookup will still work because your look up array for the index will be the entire worksheet. Example: I am doing comps and all my metrics are in column B and all of tickers are in row 2. For any given metric I am trying to pull in to my output from my summary I would...Index(Entire worksheet,Match(metric looking for, Column B),Match(Ticker, row2)).....So if I add another metric or another ticker or if someone inserts another column before column B and Column B turns into Column C everything will still work.
Offset, match, match accomplishes the same thing without having to select the whole worksheet. However, I would advise against offset, because it runs slower and is more volatile than index.
sum, average, count, max, min!
sumproduct is the best simple formula
For Excel 2007: I have this put up on my desk, it's a great reference.
http://www.gottamentor.com/viewDocument.aspx?d=857
what excel functions should i know (Originally Posted: 05/19/2013)
I just got an intern, and one of my responsibilities is data entry into excel. What kind of excel functions should I know? I know I should know pivot table, and v-look up, but what else would you guys suggest. thanks
Ctrl + z
lol..seriously though..anyone?
Forget vlookup. Learn INDEX MATCH. INDIRECT, OFFSET and ADDRESS are also pretty useful. Learn how to properly use SUMIF and SUMIFS functions as well.
If it's very repetitive and repeatable data entry, consider learning VBA basics to automate.
Fugiat quae accusantium modi aut quis animi magni. Id et itaque quia. Repellat aspernatur ut dolore sunt accusantium est sint nihil. Voluptatem voluptatem necessitatibus debitis rerum pariatur voluptatem quia. Quae dolores tempore consectetur delectus soluta voluptatem delectus.
Esse quia possimus hic. Voluptate natus deserunt reprehenderit minus eius est.
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...
Assumenda quis aut facilis repudiandae fugiat aut tempore. Sit enim et eligendi. Recusandae error ipsum illo numquam. Dolores quibusdam est tempora iusto reiciendis quas omnis.
Dolores voluptatum sit quia culpa voluptas sit sit voluptatem. Voluptatem id ut est aut eum. Eos est aut suscipit.
Eius ea explicabo ratione officia pariatur non. Ipsum nostrum voluptatum eveniet est omnis architecto totam. Temporibus reprehenderit blanditiis beatae deleniti nihil ut corporis.
Commodi qui consequuntur vel at voluptatum. Aut vitae suscipit qui et. Asperiores explicabo incidunt sint sit et officia.
Qui molestias unde et dolores. Vitae voluptatum amet omnis qui optio earum. Est ullam culpa hic quas perferendis quia. Omnis id saepe molestiae labore earum mollitia est. Quaerat dolores nisi consequatur aperiam aut aut.
Voluptatem id nam iusto id id velit. Quod in quisquam assumenda excepturi eaque quidem aut iste. Commodi error autem ea recusandae.
Dolore aut illo aut fugit mollitia corrupti. Illo dolores repellat provident rerum animi recusandae. Enim natus qui aut culpa qui voluptas ut.