Excel Tips and Tricks in CRE?
I'm curious what formulas/functions you commonly use, and if you have any uncommon or not widely known Excel tips that you use for work. Looking to brush up on my skills particularly in applications geared towards CRE.
[{"id": "fqbydox", "text": "Irr\nXnpv\nFv\nPv\n\n\nIf\nIferror\nAnd\nOr\nNot \n\nGet really good at nesting if statements. Seriously get out paper and write down you logic path with error catches, returns for blanks etc. Wrap the whole thing in an iferror.\n\nMost of your IS conditionals i.e. ISNA\n\nIndex &amp; Match\nHlookup and vlookup are for chumps\n\nSumif\nSumifs\n\nSumproduct\n\nCount\nCounta\n\nCountif\nCountifs\n\nLeft\nRight\nMid\nLen\nSearch\n\nLearn to lock with expansion. Idea here is as you copy the formula across the range expands.\nSo imagine I'm in cell b1 and want to sum the numbers in column a, but as a running total so b1 = a1, but b2 = sum(a1,a2)... $a$1:a1\n\n\nThe real trick is learning how to use all the above together at the same time and abuse the shit out of arrays\n\nI.e. Learning to use sumif with an index Match and multiple matches across rows and columns\n\nPivot tables\n\nPower query if you're up for it\n\nTranspose\nOffset\n\nUse helper columns\n\nAlt+a, g, g to group. Never hide columns or rows.\n\nAlt+w, v, g do it on every sheet\n\nCtrl+F10\n\nAlt+h, o, i\nAlt+h, o, h\nAlt+h, o, w\n\nCtl+a, ctl+t to select your data and make a table\n\nCtl+g to go to a cell\n\n\nLittle bit of vba\nMainly loops and for statements\nThis just helps with automating complex formulas with out resorting to giant array formulas \n\nPractice never using your mouse. If you find you need to use it, Google how to do whatever you're trying to do with the keyboard and do that instead.", "replies": [{"id": "fqc2fe5", "text": "This guy Excels", "replies": [{"id": "fqc8lok", "text": "Thanks baronti, if I had metal to share I would!\n\nBelieve it or not I knew next to nothing not to long ago. Landed a job out of college for a RE Dev and learned most everything I mentioned on the job!\n\nEdit: Not sure of the context the OP was looking for by \"Excel for CRE\", but if you're just looking to put together a rent roll or t12 for an OM package most of what I shared isn't nessissary.\n\nBut if your looking to build custom models, run custom data gathering and analysis. Or just build cool shit in Excel. Then yes most of that is needed. \nYou will have the skills, or at least be past the learning curve with an excellent foundation, to do just about anything you want in excel.", "replies": [], "depth": 3}], "depth": 2}, {"id": "fqc9izn", "text": "This guy fucks", "replies": [{"id": "fqch4nx", "text": "Lmao", "replies": [], "depth": 3}], "depth": 2}, {"id": "fqcbdpg", "text": "All of this and please, for the love of Elon, don\u2019t use named cells!\n\nIndex/match is a game changer, just watch your row references.\n\nAnd F7 is spellcheck.\n\nIf you\u2019re preparing memos in excel, please hit f7.", "replies": [{"id": "fqcr71g", "text": "I always forget to hit f7... And I need it!", "replies": [], "depth": 3}, {"id": "fqdldrn", "text": "Thanks for that! Can't believe I've gone this far without spell check, would have saved me a bunch of time reviewing lol.", "replies": [], "depth": 3}], "depth": 2}, {"id": "fqclz6n", "text": "U da man", "replies": [], "depth": 2}, {"id": "fqdl9po", "text": "This is awesome, thank you for taking the time to share!", "replies": [], "depth": 2}, {"id": "fqgke66", "text": "Love this.  One thing I'd add is that I think Shift+Alt+Right Arrow/Left Arrow is better than Alt+A+G+G for grouping. I used to use Alt+A+G+G for a while til a coworker pointed that one out\n\nI started working for a major LifeCo, and no one in my office knew about Alt+W+V+G and I got so mad seeing excel sheet upon excel sheet of white filled cells.  I'm glad you're sharing good practice", "replies": [{"id": "fqgofwm", "text": "Never came across Shift+Alt+Right Arrow thanks for the tip!", "replies": [], "depth": 3}], "depth": 2}, {"id": "fqio951", "text": "Also XIRR, better for monthly cash flows", "replies": [], "depth": 2}], "depth": 1}, {"id": "fqc3nao", "text": "Email me at info@kapelrealestate.com and I will send you my excel underwriting CRE template for free", "replies": [], "depth": 1}, {"id": "fqcedg2", "text": "Check out the blogs at [Top Shelf Models](http://tsmfinancialmodels.com) They have a bunch of blog topics/videos on Excel modeling for CRE", "replies": [], "depth": 1}, {"id": "fqebafb", "text": "Alt+F11 .. create a macro, run macro, profit", "replies": [], "depth": 1}]