Jul 18, 2018
Excel Formulas you use constantly on the job?
Was wondering what formulas most of you are using on the job? Thanks.
I have excel tests that I will have to do for some final round interviews so was wondering if there are any recommended websites to practice.
Private Equity Interview Course
- 2,447 questions across 203 private equity funds. Crowdsourced from over 500,000 mem.
- 9 Detailed LBO Modeling Tests and 15+ hours of video solutions.
- Trusted by over 1,000 aspiring private equity professionals just like you.
Wall Street Oasis Notifications
Please tell us a little bit more about yourself to send you the most relevant notifications
Get Notified?
Popular Content See all
- Non-target --> FT Off-Cycle IBD AnalystSo I have been around here for about a year and half now, and have to say thank you to the community. Really learned a lot and got my foot in door based on the advice on these forums. ### The Non-Target School Now for my story, I attended a large (20,000+ students) non-target school. We...
- Is investment banking full of rich kids?It seems that to get into investment banking, you must: - apply early - have been to a target school - in order to get into the target school, excel in high school, have money and you must know which schools are targets for investment banking It seems like that in order to tick off all...
- Thrown out halfway through interviewHey y'all, I am just wondering if this has like happened to anyone else before. I was interviewing at a F500 the other day, and halfway through my 6 mini interviews I was interrupted by the HR head telling me the interview was done and for me to exit the building. It was pretty...
- The Mechanics of a Mispricing There have been a few other related posts that I have commented on recently that touched on mispricings and catalysts. I wanted to take some time and write something up that brings it all together in one post. This is something that took me a while to understand and come around to when I was...
- Becoming a PrincipalFor many of us who are still younger on this page, it is an intended goal to become a principal. As I think it would be very helpful, for those who are principals (on the younger side): * How did you get to where you are now? * What route did you take career-wise? * What obstacles did...
- Learning Curve as 2nd Year Analyst I am currently working as an investment banking analyst at a BB and 6 months after starting the job, I feel the learning curve flattens very quickly. I don't learn much from making another pitch book and even in the live deal, I feel the work I do isn't particularly exciting. Since...
- AMA: Non-Target -> 3rd Year Analyst MM IBUnfortunately turning comments on a pitch deck today so I have some time in between turns to do an AMA. Thought I'd give back to the community here now that I'm wrapping up my 3rd year at a MM. Definitely wouldn't be here today if it wasn't for WSO. ###Quick background...
- An Honest reflection from my first Summer Analyst role: The Good, The Bad, The Ugly (mainly bad and ugly)I come from a target West Coast school. I say target because being on the West Coast, there are only three name-brand schools you can land a job on at an i-bank on the West Coast. Those name brands from my POV are Stanford, Berkeley, and USC. I go to one of the first two. Hats off to the Moelis...
- Why choose Sales and Trading over Investment Banking?What are the advantages to sales and trading over Investment Banking? What should your interests be to go into Sales and Trading? what types of technicals do you need to know for Sales and Trading?
- Nuances of Corporate Finance in each IndustryI want to start a thread talking about the nuances of corporate finance within each industry (Oil & Gas, Healthcare/Pharma, Aerospace & Defense, Manufacturing, Consumer Packaged Goods, etc). The impression I've gotten is that you can bounce around between industries in the first...
Leaderboard See all
| 1 |   | 97.4 |
| 2 |  | 97.2 |
| 3 |  | 97.1 |
| 4 |  | 96.9 |
| 5 |  | 96.9 |
| 6 | | 96.8 |
| 7 | | 96.4 |
| 8 |  | 96.4 |
| 9 | | 96.4 |
| 10 | | 96.3 |
Upcoming EventsSee all
- Feb14
- Feb15
- Feb20
- Feb20
- Feb21
Comments (91)
(new value/old value) - 1 for calculating growth rates lul
Just a tip - parentheses are not needed due to order of operations, which is the whole advantage of writing it this way instead of (new - old) / old.
Index(match,match) all day, every day, twice a day.
Can you explain this function and what you use it for?
Finding specific values in a table where you don't necessarily know which column row the item is in. Similar to vlookup but a bit beefed up.
As an aside, I definitely got dinged at an interview for not knowing this. Then I entered the workforce and literally never encountered a situation where I needed it until ~2 years in.
Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.
Indexmatch > vlookup
Its actually not though in terms of excel resource efdiciency
Can you expand on this? I like index match more because you don't have to worry about how the data is organized and there is no need to count the number of columns.
It comes down to the 0's and 1's whoch is outside of my wheelhouse if I'm being honest.
Probably wont matter for anything you do in banking. The short version is each time you use the formula Index, excel saves an array with a name reference, this function is done each time you hit F9 (worse for auto calc). Every index match output that is refenrenced must pull up and recalculate for every value upstream from it. You can illustrate this yourself by creating sequential sheets which pull and index match from the previous sheet. The calculation time / file size will increase exponentially. Now, once you have this calculating very slowly, rearrange the page order and see how poorly it functions. It will likely start pulling incorrect values. (Excel calculates top left to bottom right, page one through page last). Now try throwing a circular reference between your index matches and watch your computer Snow Crash like its 1988.
As a disclaimer I could be off on some of this - happy to hear where that is.
This is basically accurate, similar to what I mentioned below.
If you used vlookup instead of index match in my organisation you would probably be fired.
Index match is also more dynamic. If you add columns into a table later, or change the order of columns the formula will not break.
You could have a dynamic table even with a vlookup. Instead of entering the column # of the data set, you use: column("column you are seeking")-column("first column in data")+1. Agreed index match is better.
How do you use index match?
I use it a lot to pull one specific fund's monthly return from a sheet with a fuckload of return streams.
index(ReturnsData, match( x , ReturnsDate, 0), match ( y, ReturnsName, 0)) where x references the cell containing the relevant date, and y is the name of the fund. Drag the formula down several rows so in the end I have a time series of returns for a fund.
I've never used MATCH/MATCH, only INDEX(MATCH()). I would always merge two columns for a lookup ID - in your example, date and fund merged to create a unique ID. Then, INDEX(MATCH()).
Cheers, thanks for this.
Here (the site in general is pretty helpful) for Excel newbies:
https://exceljet.net/formula/two-way-lookup-with-i...
sorry if stupid question but why is this matching 1800 and not 2700?
if not set to exact it only takes first digit into account? what if theres a 2000 there instead of 200? would it chose that over 300?
Neither will a vlookup, as long as you create a table
Sum
Magical Vlookup& IF and Pivot tables
Pornhub.com
CapIQ tab -> productivity -> launch Seamless
CapIQ tab -> productivity -> launch Seamless
CapIQ tab -> productivity -> launch Seamless
Offset
Count
If(And
If(And(Offset(Count
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
Can you explain when/why you'd used "If(And(Offset(Count" ? creating a dynamic waterfall scenario?
It's actually not properly sequenced.
I use If(And(Count(Offset a lot to be able to change debt structures, reversion period, and to adjust TI/LCs for downtime.
Using count just eliminates the need to number your periods at the top of the pro-forma by counting the dates instead and offset lets you toggle different scenarios listed on the summary tab.
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
Side-by-side comparison of top modeling training courses + exclusive discount through WSO here.
Countif
Sumproduct (and be able to use conditional sumproduct)
seconding this
other than that, either succumb to the firm preference with LOOKUPS or become exceedingly good at writing a vlookup/hlookup on the fly
It was mind-opening when I realized 90% of reports (at the firm) were done automatically feeding through other software. I feel bad for the guys I know at Morgan Stanley who have to write reports from excel all day every day.
Another man of culture I see... sumproduct is an incredibly versatile formula when dealing with large amounts of arrays
edate and eomonth
*(insert condition here)
As it saves me writing an if statement since I'm lazy
sumif...a lot
hlookup/hlookup....a lot
offset, pretty handy
date/eomonth...a lot
hlookup and count and match together, makes an array dynamic for when you add in rows or take out rows and still calculate from the same row.
floating arrays for data are pretty cool too.
Crazy nested if formulas...a lot
Lol, this thread brings me back to the Analyst days of REFing the shit out of a formula and fucking up the entire model then frantically trying to fix it before someone notices.
Haha yep, this is why iferror has become one of my favorite formulas. If you error check everything properly (especially circular references), it becomes pretty hard to key in a letter somewhere by accident and blow up the entire analysis.
Howeva!!...
IFERROR, if used on everything in the world, can slow a sheet and book down, especially if what's inside the () is cross-sheet referencing. I try to limit my IFERROR to things I know have the possibility to error out.
Market Share curves based on 5 inputs? Yes
Sum of segmented revenue streams? Nah
Agree with you here, it really only needs to be used for more problematic calcs like interest / draws etc, because if those break the whole model usually goes.
Might be a dumb question, but how do you get super proficient in utilizing these excel formulas? I'm currently in UG and have the REFM Modeling course. Am currently reading Linneman's textbook and taking some RE Finance and Investment Analysis at UCLA Extension. I also have my Argus Cert, but when reading this thread I feel like I am lacking in Excel.
Practicing/using it everyday
Word^
To add to this, while it's a bit chicken and egg, although practice will hone your skills it's also a matter of being able to think logically and a few steps in front of yourself.
Practicing will get you there but if you're not naturally inclined towards that way of thinking (I don't know how else to describe it) it'll be just that little bit harder.
sorry if stupid question, ive seen people mention this but ive never had this issue since im always saving multiple versions of a file im working on....e.g. v1, v2, v3 i dont work directly with debt or anything but i sit next to our Liquidity team and they never have such issues due to making multiple versions each time they make a change or put something through. why does this seem to be different in banking / re?
Everyone here has pretty much covered the most common and useful formulas so I will weigh in on a couple resources for those trying to refine their Excel skills:
If you are entrepreneurial and self disciplined however the first two will give you a great basis for an analyst career.
SUMIF
INDEX(Match(Match constantly
SUMPRODUCT for cash flow roll-ups
OFFSET
IF/Then and SUMPRODUCT
There is currently a battle in my company over the use of the SUBTOTAL command. It's the nerdiest thing ever and it cracks me up.
Team subtotal.
You seem to be a gentleman and a scholar, so I think I know where you stand.
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
Seconded on SUBTOTAL
when modelling from scratch sumproduct is life.
Don't need much more.
Maybe some sum ifs and minima.
I've been using indirect a lot lately. Has been an incredibly versatile tool in organizing a lot of crazy data
SUMIF
SUMPRODUCT
IF then Nested formulas
Date functions
INDEX MATCH
If I'm pulling in cash flows for multiple months then always index match so I can just drag the formula across without changing column numbers. I'll use VLookup if I'm just doing a simple look up for one value.
Carl+Z
large, small can be helpful for me too
other than that, mostly index match, sumif, sumproduct
F2
=IFERROR(VLOOKUP(HOES,ANYWHERE,FALSE),"GET FADED")
I agree with most everything on here but I gotta be honest, if you think index is superior to v/hlookups in all instances you're doing it wrong.
I'm curious, when are the lookups better than index?
2 advantages of VLOOKUP beyond the obvious simpler syntax:
IMM is of course a better tool to use for standardized, repetitive models, but often it's too complex for a small lookup and search function.
I use VLOOKUP much more often, but like IMM for the heavy lifting modeling stuff we do.
Also, there are easy ways to get around the 'fixed' column/row reference and you can make that dynamic pretty easily, so if someone else goes in and deletes/adds rows to the table it won't cause an error.
Use INDIRECT, which eliminates your need to switch between sheets (pretty simple to memorize the array rows/columns). Granted it's an added layer of complexity but fairly simple once you understand the formula's syntax.
Agreed, have you tried this with massive files though? Our model is pretty massive as we run a lot of tables/matrices so if you add too many formulas with Index/Match/Offset/Indirect functions combined it can bog it down pretty quickly. Definitely depends on the scenario though, I would guess we are outliers with what we typically report/include in our packages.
I have. Not sure what your threshold for massive is though. A majority of my models exceed 3,200KB (>=30 tabs) which tend to be fairly slow with processing, especially when incorporating Data Tables/Matricies.
Also if you are constantly in "Page Break View" Excel's processing speed is reduced.
We typical just view it in Normal, we have macros that adjust all of the printing screen/layouts once we get to that point. Ours is between 5 or 6 meg standard. Last time I counted the tabs were like 42 but we've added since then. But we do have a lot of heavy duty macros.
That's sizeable, and I thought 30 was alot. I do the same with regards to print screen/layouts. What asset class do you focus on?
I do this with a vlookup and you're right about making it dynamic. I use vlookup match and count to return you the vlookup row numbe, but that is for line items which are not duplicated.
For waterfalls, being proficient with MAX(MIN ()) and MIN(MAX()) is helpful
Something said this but Sumproduct(-- = incredible for summary charts
F1
Datedif
Excel is primitive. Step your game up folks and get on some real data analytics software :D
How many of you Excel guru's can operate without a mouse? I've heard you become light years quicker once you learn all the keyboard shortcuts and hotkeys.
Trying to decide if I want to put in the brain damage to learn or just keep using mouse.
I can use the keyboard for almost all functions. But there are some things that are just too annoying to do without a mouse like some formatting.
What formatting is quicker with a mouse?
Shortcuts probably save more time formatting than they do w/ anything else.
I come from down in the Valley, where Mr. when you're young, they bring you up to do like your daddy done.
For me it's really niche formatting like getting particular about colors, effects, etc. Nothing you could probably have to use. And when I mean mouse I mean hotkeying to the color pallet and then flipping over to the mouse for a quick second to select an option rather than tab and arrow through everything.
Use Macabacus or other financial modelling plug-ins. You can map custom palettes and cycle through the colours you use much quicker than with a mouse.
It's pretty simple - just start slamming that alt button.
The vast majority of my work time is in Excel, and I'd say the time saved working through keyboard shortcuts rather than a mouse amounts to a 3-4 hours a week. Definitely worth learning.
Besides obvious ones like INDEX(MATCH), I use these ones a ton:
-INDIRECT
-SUM(OFFSET())
-MID()/LEN()
-CONCATENATE()
-ISNUMBER()
concatenate is too long; just use ampersands in between your concatenation values
Alt H + B + H
This bottom borders be thicc
can you give a few more like this...super helpful
Some quick shortcuts I use all the time:
Ctrl + Shift + & = puts boarder around selected area
Ctrl + Shift + - = Erases all boarders in a selected area
Ctrl + Shift + $ = Aromatically puts numbers in Currency format (Although most like to use Accounting format without "$" sign, so Ctrl + 1 = brings up format window and just use Tab key to get to accounting and put in "none" for currency symbol and 0 decimals)
Ctrl + [ = shows exactly where cells are in a selected formula by highlighting them. I like it better than tracing dependents/precedents
Alt + H + 0/9 = increase or decrease decimals. I for some reason find myself using this a lot
Instead of Ctrl + V for paste, I do a lot of Alt + H + V.....from there you have all the paste options (values, formulas, format, etc etc).
Any of the Alt + functions those save me time if I know what I'm doing. I still don't know all of them but 100% see the value in dedicating time to learn them
Good comments above. Some of my favorites:
I like commenting when I have long formulas using +N("COMMENT").
Also, recording macros and editing them a bit if you find yourself repeating something across many tabs.
I'm just an intern, so probably what I use is different. But I use a lot of vlookups, sumifs, if combined with and/or. I know that these aren't formulas, but a lot of work with the shortcut keys.
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)
Want to sign in with your social account?
Want to Vote on this Content?! No WSO Credits?
Join Us
Already a member? Login
Popular Content on WSO
Related Content on WSO