Cool excel formulas/functions?
Anyone have any cool excel functions/formulas that they don’t think too many other people know of?
I feel like I’ve hit a plateau and would love to pick up some new tips and tricks.
If anyone has any that they’d like to share (out of the basics) I’d appreciate it!
=mid is cool... saves me a bunch of time when someone puts some dumb formatting or something in rent rolls.
Basically =mid will list the text in a cell, skipping a certain number of characters and outputting a certain number of characters.
so if some rent roll has units listed as 0000049 0000050 etc, you can =mid(5,2) and change the units to 49 and 50
mid(),left(), and right() makes formatting things a lot easier. Also a trick a lot of people don't think about is that you can find and replace spaces with nothing. If it's one of those oddly formatted spaces, you can just copy it into the replace field. Was pretty crucial dealing with the various garbage the older Yardi reports spit out (maybe still do).
^^^
=value should fix that problem
others that are fun
unique
filter
textafter
textbefore
sum
sumifs
Trim, choose, offset
Use it wisely, in spreadsheets already struggling, it does add a bit of calculating depth to it, but I use =indirect a surprising amount. Two main use cases:
1. Combining data from multiple tabs. Recently needed to pull year X NOI from 30+ property tabs, I set the header to the tab name and was able to just copy/paste the formula. I know there are other ways to handle this too, but this is the easiest for me.
2. Scenarios, specifically for argus runs. I don't use argus anymore, but I used indirect to refer to one of 3 tabs named for different scenarios with argus cash flows, then a drop down to select which one.
Already mentioned above, but =Filter is a huge honorable mention.
more people should use =sumproduct to roll up monthly CFs into annual CFs
How do you do that? I always use sumifs based on row title and year.
I’ll try to explain below but kinda hard to do in writing.
It’s simple and similar to SUMIFS. I don’t like complex formulas or models but this is very easy to copy around without editing and allows for a more dynamic model IMO
=SP( (entire column of line items from your monthly cash flow tab = line item you want to return on acf tab)x(entire row of hold years from monthly cash flow tab = year you want to return in Acf tab) x entire monthly cash flow field)
Then lock columns, rows, etc as needed
There’s a YouTube video out there somewhere as I understand that may not be super clear
=LAMBDA for custom functions without using macros
Ad aut officia molestiae odio et expedita sit. Delectus et suscipit sunt iste aut debitis explicabo est.
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...