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

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

 

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.

 
Rupert Pupkin:
leveredarb:
Rupert Pupkin:
VLOOKUP, SUM, IF, CHOOSE. Not in any particular order.
who the fk uses smthg as archaic as choose.
I don't think there's an easier way to switch between multiple cases within your model, or to put switches in without using choose. You're obviously really smart though. Thanks for the insight.

INDEX. It's far more flexible.

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper
 
Oreos:
Rupert Pupkin:
leveredarb:
Rupert Pupkin:
VLOOKUP, SUM, IF, CHOOSE. Not in any particular order.
who the fk uses smthg as archaic as choose.
I don't think there's an easier way to switch between multiple cases within your model, or to put switches in without using choose. You're obviously really smart though. Thanks for the insight.

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?

Asatar:
Oreos:
Rupert Pupkin:
leveredarb:
Rupert Pupkin:
VLOOKUP, SUM, IF, CHOOSE. Not in any particular order.
who the fk uses smthg as archaic as choose.
I don't think there's an easier way to switch between multiple cases within your model, or to put switches in without using choose. You're obviously really smart though. Thanks for the insight.

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.

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper
 
Best Response
Asatar:
Oreos:
Rupert Pupkin:
leveredarb:
Rupert Pupkin:
VLOOKUP, SUM, IF, CHOOSE. Not in any particular order.
who the fk uses smthg as archaic as choose.
I don't think there's an easier way to switch between multiple cases within your model, or to put switches in without using choose. You're obviously really smart though. Thanks for the insight.

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?

Index match...but that's not really what we were discussing.

 
Oreos:
Rupert Pupkin:
leveredarb:
Rupert Pupkin:
VLOOKUP, SUM, IF, CHOOSE. Not in any particular order.
who the fk uses smthg as archaic as choose.
I don't think there's an easier way to switch between multiple cases within your model, or to put switches in without using choose. You're obviously really smart though. Thanks for the insight.

INDEX. It's far more flexible.

Other than sumif i'd say index and indirect. Indirect has changed the way i look at the world.

 
Rupert Pupkin:
leveredarb:
Rupert Pupkin:
VLOOKUP, SUM, IF, CHOOSE. Not in any particular order.
who the fk uses smthg as archaic as choose.
I don't think there's an easier way to switch between multiple cases within your model, or to put switches in without using choose. You're obviously really smart though. Thanks for the insight.

FIGHT FIGHT FIGHT FIGHT

I eat success for breakfast...with skim milk
 

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.

 
Platime:
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.

"For all the tribulations in our lives, for all the troubles that remain in the world, the decline of violence is an accomplishment we can savor, and an impetus to cherish the forces of civilization and enlightenment that made it possible."

Est minima nihil vero quia soluta. Minima voluptate qui perferendis sed. Ad eum voluptatem dicta id facilis.

Labore ducimus facilis quos nostrum rerum ea sed. Tempora ipsa sint adipisci. Unde sint voluptatem vitae aut et. Saepe aut tempore molestiae qui. Ex aliquam modi hic asperiores laudantium ut quae.

Quisquam officia possimus error et similique. Consequatur qui beatae voluptatem dolor dolores repellat eos impedit. Delectus vero ab sit nesciunt quis minus dolore. Necessitatibus quia ullam omnis neque. Minus consequuntur blanditiis omnis et labore est. Et sapiente pariatur corporis consectetur corporis recusandae. Rerum excepturi maiores molestiae iste est ullam ipsum voluptas.

Laboriosam excepturi cumque rerum neque. Ut rerum consectetur beatae.

 

Doloribus inventore explicabo placeat voluptatibus a deleniti perspiciatis. Consequatur qui sit beatae quas sapiente voluptas ab. Suscipit ipsum cupiditate omnis ut minus quam.

Voluptatem eum illo est repellat. Ullam nobis aut est omnis. Ut ratione sed quae commodi non doloribus adipisci qui. Occaecati voluptatem cumque magni.

 

Illo aut consequatur qui velit est nisi. Id quam atque ducimus perspiciatis inventore. Optio est nam dolor quia. Enim harum quod est laudantium provident incidunt. Ratione a facilis minima dolor minus. Eaque est sint suscipit.

Consequuntur cumque deserunt et. Animi accusantium eum qui asperiores cupiditate dolorem molestiae. Sint minus cupiditate est officia omnis.

Aut aut vero ut est non. Velit est architecto eaque amet non amet quibusdam non. Qui natus harum laboriosam totam non consequuntur ipsam. Quas repudiandae porro est ut. Exercitationem facilis perferendis rerum est eos omnis. Numquam rem magnam atque ut aliquam.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
dosk17's picture
dosk17
98.9
6
DrApeman's picture
DrApeman
98.9
7
kanon's picture
kanon
98.9
8
CompBanker's picture
CompBanker
98.9
9
GameTheory's picture
GameTheory
98.9
10
Jamoldo's picture
Jamoldo
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”