, ,

Keyboard Shortcuts I Use Constantly When Modeling in Excel (Formatting)

The more you model real estate in Microsoft Excel, the faster and more accurate you become – and hopefully as a consequence the more valuable you become to real estate employers and investors. Part of the process of mastering real estate financial modeling in Excel, is learning to put the mouse aside and rely on Excel’s various keyboard shortcuts.

And while you’re not likely to memorize, nor even use, every keyboard shortcut available to you, there are certain shortcuts that will become second-nature. In this post, I share a few of those keyboard shortcuts that I use most often (i.e. everyday) to speed up my modeling. Specifically, this list relates to shortcuts that make formatting and reformatting cells in your model faster and easier.

Editorial side note: I hate list blog posts (i.e. listicles). I mean, from 2010 – 2015 every blog on Earth was posting about the “Ten Crazy Whatevers” that no one cared about. As a result, in 2018 I’m forced to use an ambiguous title that leaves the reader wondering: how many keyboard shortcuts is this guy going to bore me with today!? Hint: It’s 10.

“To Become One With Excel, You Must Become One With Your Keyboard” – Unknown RE Modeling Professor

1. Paste as Values – ALT then H then V then V

If you’re like me, I’m constantly working with existing Excel templates. Most often the template is one of my company’s proprietary Excel models, but other times the template is a model I created, a model provided by a partner, or one provided by a broker or borrower. Each model comes with its own unique formatting, theme style, design, and layout. Sections are usually marked by borders or background colors, and the font color varies from one cell to the next depending on the cell’s purpose (e.g. blue for input, black for output, green for reference to other tab).

Since Microsoft (and this really annoys me) decided that the default paste keystroke (i.e. CTRL+V) should paste all formatting, cell location, borders, and formulas from the origin (copy) cell, simply using CTRL+V to paste from one cell to another in Excel quickly turns your Workbook into a formatting mess. And don’t even get me started with the external link mess CTRL+V pasting from one Workbook to another creates!

So what’s the solution to the mess this creates? Paste as Values (or as Formulas)! The keyboard shortcut for Paste as Values is ALT then H then V then V;  in succession, not all at once. I probably use this keyboard shortcut every few minutes, if not more. If you use this shortcut coupled with #2 below effectively, your days of correcting formatting, redoing borders, or trying to break unwanted cross-Workbook links are over. What a time saver!

Mac Users: OPTION then H then V then V

2. Paste as Formulas – ALT then H then V then F

I arguable use the Paste as Formulas keyboard shortcut more than I use the Paste as Values shortcut – it really just depends on the project. But as with keyboard shortcut #1 above, if CTRL+V is the disease, ALT then H then V then F is the antidote. Whether you Paste as Values, or Paste as Formulas really depends on whether you want to retain the origin cell’s formula or not. Either way, these two keyboard shortcuts keep the destination cell’s formatting rather than copying over the origin cell’s formatting.

Quick rule of thumb: if you’re copy-pasting a blue font (input) to blue font cell, Paste as Values is probably what you’ll use. If you’re copy-pasting a black font (output) or green font (reference from other tab) to another black or green font cell, Paste as Formulas is your go-to shortcut.

Mac Users: OPTION then H then V then F

3. Quick Change Number Formatting – CTRL+SHIFT+#s

These keyboard shortcuts come in handy when you’re building a new model and are constantly changing cell number formatting. Excel has pre-coded a handful of default number formats used commonly in real estate modeling that can be accessed by typing CTRL+SHIFT+ a number between 1 and 5. Here’s how each works:

  • CTRL+SHIFT+1 = applies number formatting with commas and two decimals (I wish Microsoft had defaulted this keystroke to include zero decimals)
  • CTRL+SHIFT+2 = applies time formatting
  • CTRL+SHIFT+3 = applies date formatting
  • CTRL+SHIFT+4 = applies currency formatting with commas and two decimals
  • CTRL+SHIFT+5 = applies percentage formatting with zero decimals (I wish Microsoft had defaulted this keystroke to include two decimals)

Mac Users: Use CONTROL+OPTION+1 through 5

Quick tip: If you use the Excel 4 CRE add-in to extend the number of available keyboard shortcuts, you can use the CTRL+ALT+< or > shortcut to increase or decrease the number of decimals. For instance, I usually use a percentage formatting with two decimal. So to accomplish this, I hit CTRL+SHIFT+5 to change to percentage, and then hit CTRL+ALT+> twice to increase the number to two decimals.

4. Open the Format Cells Dialog Box – CTRL+1

If you need to use more custom number formatting not available with the CTRL+SHIFT+#s keystrokes, the Format Cell Dialog Box accessed using CTRL+1 is what you’re looking for. While the Home ribbon contains quicklinks to most of the options contained in the Format Cells Dialog Box, the keyboard shortcuts to use those quicklinks are clunky. In contrast, just hit CTRL+1 and all cell formatting options open up to you.

Need to change to Accounting number format with no dollar signs and 0 decimals? Simply hit CTRL+1, tab into the number format options, key down to accounting, and then tab into your accounting options. Want a custom number format that includes a “SF” at the end of a number with commas? Simply hit  CTRL+1, tab into the number format options, key down to custom formatting, tab into the custom formatting box, and then type #,##0 “SF” and hit enter.

Mac Users: Use CONTROL+1 to access the Cell Format Dialog Box

5. Bold, Italicize, and Underline – CTRL+B, CTRL+I, and CTRL+U

Lastly, it seems I’m regularly bolding, italicizing, or underlining values in my Workbook. Again, quicklinks on the Home Ribbon will accomplish the same thing, but those quicklinks are meant more for mouse users. So when the time comes to bold a title, italicize a side comment, or underline a sum row, here are your go-to keyboard shortcuts:

  • CTRL+B – bold
  • CTRL+I – italicize
  • CTRL+U – underline

Quick tip: these keyboard shortcuts aren’t specific to Excel. Most programs (Windows-based at least) use these same keystrokes to bold, italicize, and underline.

Mac Users: CONTROL+B, I, or U

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 20 years of residential and commercial real estate experience. Over his career, he has underwritten $30+ billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently President and member of the founding team at Stablewood. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.