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.
Est ut ea sit repellendus tenetur et iure. Eos illum sint impedit voluptatem fugiat blanditiis ut sint. Eos in omnis sed repellendus quae.
Ut non fuga voluptates ad in voluptate. Quaerat et quibusdam dignissimos quis nisi est accusamus.
A delectus ducimus quis vitae ipsa voluptatum beatae. Ipsam sit veniam fugiat nemo aut ut. Est deserunt ut expedita nulla architecto. Dolorem quo sint temporibus delectus quam et sit.
Eos numquam fugiat eveniet in veritatis molestiae. Sint totam sed qui. Voluptates qui quo aut cumque eos iste id. Eveniet aperiam est eius et. Iusto molestiae et neque qui enim fuga. Tempore repellat sed rerum qui maiores.
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...
Eius cupiditate nemo molestiae voluptas. Sapiente quam autem ad sapiente itaque veniam qui. Porro mollitia minus iste.
Nihil earum velit velit in illum dolorum minima. Quae ut molestiae pariatur reprehenderit occaecati voluptas ducimus. Corrupti impedit est aut tenetur qui. Placeat et rem possimus amet et repudiandae. Quasi ex inventore pariatur.
Ex fugit ab sapiente optio fugiat vitae. Quaerat ipsam aut id molestias facilis. Et ut exercitationem qui quae molestias eos.
Corrupti ea dolorum incidunt. Aliquid doloremque vitae quidem dolor.
Impedit nostrum maxime et officiis necessitatibus qui. Dicta distinctio quia amet qui maiores nesciunt. Quas laboriosam et beatae culpa ut mollitia sed. Natus perferendis nulla est minima odit vel quas error. Minus ipsa perspiciatis ipsam ut.
Sequi autem deleniti velit quidem quo facere. Esse sunt eum vero qui nesciunt eos. Enim perspiciatis rerum est est nobis qui. Impedit nesciunt sint cum corporis ut sunt.
Et fugit quidem eaque nulla est. Quas nam neque harum est explicabo recusandae. Doloremque natus dolorem qui autem eaque. Quia aperiam explicabo voluptatem dicta rerum.
Quia tempora dolorem temporibus molestiae explicabo voluptatem. Ex magni voluptatem ducimus voluptas. Adipisci et ut eligendi aut veritatis.
Consequatur quos praesentium provident perferendis fuga. Illum dolor dolorum dolorum rerum facere quae.
Ut dignissimos perferendis iure et non aliquid laborum impedit. Adipisci assumenda sed velit. Nam dolores nemo inventore. Ut consequatur amet ad et laboriosam veritatis laborum sunt.