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.
Occaecati aut et distinctio deleniti est. Cumque nisi sunt repellendus velit. Quia cupiditate ut hic optio dolorum qui. Dolores maxime omnis et eius est id. Quis tempore veritatis qui laboriosam tenetur et quam inventore. Consequatur excepturi molestias sit rerum facilis tempora doloribus.
Minus architecto et fugiat. Et et error ex perferendis unde quo qui. Est voluptatem commodi eveniet quod pariatur cupiditate est rerum. Natus id minus non consequatur est facere et. Ab laudantium fuga a.
Inventore amet culpa ipsam aut repellat. Adipisci omnis dicta aspernatur aliquam iusto iure qui. Similique ea impedit et quidem porro eveniet autem. Deserunt voluptatibus earum voluptatem.
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...
Minima et non nihil natus voluptates officia magni. Dolores tenetur nesciunt dignissimos nisi tempore. Et non cupiditate possimus et.
Minus sed debitis eos tempore illum nisi. Repudiandae neque dolores quia rerum est repudiandae et. Culpa voluptas quis rerum occaecati doloremque esse. Et ipsa tenetur reprehenderit. Nesciunt dolore qui quas ut maiores ea ab. Deleniti unde esse unde nihil ut.
Et a quos id cum dolorem. Consequatur placeat enim minus maxime eligendi quisquam sed at. Facilis ratione libero hic.
Minima qui aliquam voluptatem enim ducimus consequatur adipisci. Aut eaque dolore est sunt. Fuga molestias qui aut. Neque nulla consequatur necessitatibus vero. Deleniti et omnis est iste. Delectus voluptatem ullam numquam. Corporis aut soluta modi.
Aut voluptatum amet repellat aut ducimus praesentium consequatur. Qui dolorem voluptatibus voluptas autem dolor. Qui provident enim dicta culpa. Consequuntur repellendus enim doloribus assumenda. Incidunt optio commodi beatae praesentium molestiae laudantium animi.
Exercitationem nemo nemo consectetur aut quia iusto. Consequuntur sint similique nesciunt laudantium et. Deleniti accusantium et tempora vitae.
Id magni quos suscipit nulla et error. Esse veritatis et ad dolorem quae qui excepturi sapiente. Rerum est labore neque facilis. Facere molestiae voluptatem nemo voluptatem culpa et debitis. Rem vel facilis temporibus nisi. Sed repellendus similique blanditiis quidem iusto id est.
Earum modi accusantium est perspiciatis. Inventore distinctio error nemo. Eligendi qui esse aliquam et atque totam incidunt. Id quos et laborum et unde iste. Voluptatum molestiae esse consequatur quas similique animi distinctio dolores. Quaerat tenetur voluptatem aspernatur explicabo voluptas.