As a real estate professional, Excel is a part of everyday life. Despite daily use, it’s amazing the little tricks people haven’t picked up. Often times someone will be looking over my shoulder as we examine a model or some data analysis and ask, “Wait, how’d you do that?” Or, “I’ve never seen that function, what does that do?”
Below I’ve outlined some of Excel shortcuts, functions, and tools I find most helpful from a commercial real estate perspective, with some emphasis toward financial modeling. While this list is nowhere near exhaustive, I’ve included a multitude of functions and shortcuts aimed at improving speed and efficiency.
You’ll notice that in some cases, I’m simply pointing out a concept while not going into a full tutorial (if you’d like more detail try this). I’ve also included a couple new things I just learned that were part of the most recent updates. Some of this gets fairly nuanced, but hopefully there are a few tips or tricks included here that will be helpful to you in the future.
Navigation
Having a basic understanding of navigational shortcuts will save you a lot of time. Not having to use your mouse allows you to move much quicker.
Ctrl + Home: Moves you to cell A1.
Ctrl + End: Moves you to the last cell on a worksheet.
Ctrl + Page Up or Ctrl + Page Down: Switches between tabs in a workbook.
Ctrl + Up, Down, Left or Right: Skips to the next cell containing data.
Ctrl + Tab: Switches between Excel workbooks.
Alt + Tab: Switches between all open programs (this one was essential before most everyone had two screens)
Speed
I’m going to assume that most people know the basic formatting shortcuts for bold, underline, and italics (Ctrl + B, U, or I). You should definitely know undo and redo (Ctrl + Z and Ctrl + Y); these can be helpful when toggling back and forth to see the changes a certain assumption makes. Cut, copy, and paste are common ones as well (Ctrl + X, C, or V). Here are some more:
Paste Special: I find myself using “paste special” values and formats a lot. Often times “paste link” or “transpose” (rotates vertical data to horizontal or vice versa) can be helpful too. After copying something, hit Ctrl + Alt + V to bring up the Paste Special Menu. After that hit V for Values, T for Formats, L for Link, or E for Transpose.
Formatting: There are a bunch of shortcuts to auto format data. For example, Ctrl +Shift + $ applies the Currency format and Ctrl +Shift + % applies the Percentage format. Other shortcuts for Time, Date, and Number can be found here. Also, sometimes you’ll want to format a number of items at one time, such as an entire column of data. Using Ctrl + Shift + Up, Down, Left, or Right will allow you to select all cells directionally until you reach the last cell containing data and format them all at once.
Data Tables: When you’re working a model with data tables, things can really slow down. Go to File > Options > Formulas and select “Automatic except for data tables” under Calculation Options. If you are making a bunch of changes, just hit F9 at the and all of the data tables will run.
Formulas: There a few useful shortcuts for auto generating formulas. Alt + will auto sum the number directly above that cell. Ctrl + D will copy a formula down and Ctrl + R will copy the formula to the left of the active cell. I also find F2 very helpful when editing formulas; it allows you to edit the active cell. Use F2 and then Home to jump into a cell and immediately move to the beginning of the formula.
General
Though this area seems well traveled, it’s worth noting that a lot can be done with If functions. Less utilized, but very helpful are functions for Countif, Sumif, and Averageif, which allow you to run Count, Sum, or Average functions based on an If function. Again, I’m not going to provide a full tutorial on use here, but rather point out some valuable tools.
For cash flow modeling, using the Offset function is crucial. Offset allows you to reference data based on the specified number of columns over and rows down from a particular starting cell. It works very well when you have inputs, like a particular month in the deal, to recall data. This function can also combined with the Sum function to create quarterly or annual rollups of monthly cash flows (i.e. function begins “sum(offset(“ and for the width entry use 3 or 12).
When displaying sales or rental comparables, using a Scatter Graph is key. This visual truly captures the square footage difference between units and can be useful when projecting rental rates for new product. Sometimes I’ll use the Forecast function to find exact points along a trend line. For example, if my subject is 1,387 square feet, I can see what the rental rate would be if the amount fell directly on the linear average of all comparables.
Pivot Tables are also useful. These come in handy when analyzing rent rolls in multifamily properties. They make sorting through them efficient and allow you to easily identify the most recently rented units to determine market rents.
When translating data into useful displays, Conditional Formatting is another component I use often. Overlaying this formatting over a sensitivity table (using the Data Table function), say, to show returns over a certain amount when analyzing cap rates or rental amounts, can be valuable.
Finally, it’s a good idea to learn to display values in text using formulas. This can be helpful when you want to create auto populated footnotes in models. For example:
Formula: =”¹ Capitalized Interest is interest paid until stabilization in month “&L84&”.”
Displayed Text: Capitalized Interest is interest paid until stabilization in month 31.
New Features
In the latest updates to Excel, a couple of new features were created that I didn’t even know about until recently. Most people these days work with a dual-monitor setup, which works well when working on two different models because you can put one on each screen. But what do you do you want a different tab within the same workbook on each screen?
Go to the View tab at the top and select New Window. This will open a completely new window for the tab you are currently on. This allows you to manipulate one tab and see results immediately on the other; super useful when working in complex models.
The second new feature I came across lately is called Sparklines. This feature, found under the Insert tab with options for Line, Column, and Win/Loss, allow you to create quick mini graphs within a single cell. Might be useful for showing the disbursement of funds over time or any other helpful quick visual to a series of data.
