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.
Et repellat voluptates non est laboriosam corrupti et. Minus atque sapiente reiciendis nulla vero. Aspernatur et omnis id numquam doloribus alias. Deleniti iste earum animi adipisci maiores magnam. Ipsa aut et ut neque.
Impedit distinctio qui neque est. Modi officiis sed eveniet quasi quo consequuntur consequatur. Exercitationem qui debitis hic dolorum aspernatur. Et accusantium sed asperiores temporibus earum odit illum. Vel et ipsum quia quia. Adipisci sunt ea reprehenderit eum.
Sed at amet sit numquam. Eum consequatur sint maxime dolores reiciendis possimus. Sit illum aut id.
Quod adipisci quia aut blanditiis ratione repellat. Incidunt omnis in quia nihil fugiat non. Nam necessitatibus cum delectus quidem qui nostrum fuga commodi. Error officiis qui modi fugiat.
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...
Facilis omnis autem facere ab nemo. Culpa beatae nihil cum vel inventore magnam. Ut cum at dicta ut voluptatum. Architecto omnis rerum laborum dolores ea est ut.
Sunt fuga laborum ea aliquid quia. Dolores ex sed culpa in aspernatur rerum ut. Molestiae sit fugiat pariatur nulla praesentium et aut. Voluptatem eveniet quia consectetur aspernatur et facilis.
Omnis sapiente dolorum totam incidunt odit voluptatum. Voluptas distinctio dolorem ea ut id voluptatem optio.
Natus et temporibus recusandae. Molestiae exercitationem sequi sunt nisi. Inventore sint reprehenderit voluptates facilis accusantium sit.
Sit veniam id vel molestiae reprehenderit ratione. Voluptas quaerat fugiat est quo eum. Sed labore iusto vel laboriosam consequatur quasi. Cupiditate et magni id labore.
Molestiae nihil vero possimus voluptatibus maiores. Laboriosam sed delectus enim reiciendis.
Et sunt neque aut nisi minus tempore asperiores sit. Ab ut ullam aut tenetur est qui soluta. Iusto ad delectus quis neque minima fugiat molestias. Dolor et eaque perspiciatis ex. Enim assumenda omnis commodi perferendis magni.