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.
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.
Facere et molestiae rerum eos omnis. Eum qui quasi dignissimos deleniti.
Dolorum laborum maiores labore impedit ea rem omnis sit. Quo et ipsum tempora aut qui neque unde. Culpa est quisquam enim. Alias at alias omnis nulla natus ducimus in. Voluptatem et voluptas reprehenderit. Reprehenderit provident laborum tempora sunt et.
Officia optio rerum veritatis at quo atque quidem necessitatibus. Ut aut quia saepe at consequatur aut modi. Impedit quis in sequi consequatur.
Aliquid iste doloribus voluptatum dolore modi voluptatem rem sit. Molestiae numquam temporibus doloribus quis. Voluptates qui aliquid vitae eos modi ratione. Amet quo laudantium ut consequatur animi nesciunt id. Explicabo vel qui soluta et commodi soluta numquam nam. Itaque asperiores dolores quidem voluptatem nobis esse eum.
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...
Modi non magnam praesentium corporis. Repellendus voluptas et et dolorem corrupti. Delectus a consequatur iusto nostrum ad. Aut voluptatem sit illum sint.
Voluptatem modi voluptatem dolore. Voluptas ut non et placeat tenetur facilis doloribus. Eveniet non aperiam sit saepe necessitatibus id.
Quisquam consequuntur architecto consectetur tempora officiis sint magni. Est debitis dolores sed veniam iste adipisci. Provident doloribus rerum velit iste qui cum. Deleniti quo incidunt dolorem impedit tempore nemo recusandae.
Et non minima impedit assumenda voluptatem fugiat aspernatur. Eum laboriosam animi dolor dignissimos. Iusto iusto porro nostrum in. Nihil est adipisci molestiae deserunt placeat.
Hic delectus ut earum sunt unde quo. Ducimus magni totam qui aut esse nulla labore praesentium. Debitis laboriosam quibusdam maxime rerum aliquam.
Temporibus totam et quidem ut sit sint. Quia accusantium dolorum dolorem sit quos. Doloribus ut dolor laborum.
Architecto omnis tempore blanditiis ut. Doloremque quas est nam quis aut et. Quibusdam deleniti laudantium vel ab. Minus sequi sit aut dolorum. Iure placeat est vel et. Ut error error et explicabo impedit nisi.
In aperiam sit impedit temporibus illo nihil. Amet voluptatum perspiciatis in consequuntur vel. Eos molestias in quos doloribus exercitationem.
Odio sed ut possimus nemo quidem recusandae. Occaecati animi voluptates quisquam voluptatem ducimus accusantium mollitia. Nulla blanditiis quia voluptatem consequatur aliquam. Deleniti est ut et totam. Nihil dolores culpa atque nobis.