60-Second Skills #1: X-Ray Vision

by Bruce Kirsch on August 31, 2012

Here at REFM, we want you to benefit from the wisdom we have gained in spending waaaaaay too much time analyzing real estate transactions in general and in running real estate Excel models in particular. Thus the birth of 60-Second Skills, an ongoing series we will publish that teaches you useful skills, tips and tricks for real estate and spreadsheet analysis in no more than 60 seconds (give or take).

Here’s Skill #1 – Excel X-Ray Vision

Problem: You want to simultaneously view the contents of all of the cells in the current Excel worksheet (tab). “Contents” means that if what is in the cell is an input value (e.g., Cell A8 below), it will appear as a value, and if it is a value produced by a formula (all cells with dollar amounts below), you will see the formula itself, not the resulting value.

This is what you see as the user of the model.

REFM 60-Second Skills Solution: Hold down the Control (Ctrl) key, and while keeping it pressed down, hit the “tilda” key (the ~ squiggly sign to the left of the number 1 on your keyboard)… Cool, huh? (You will note that to accommodate the width of the formulas, Excel automatically resizes the columns, which can be annoying and cause you to have to reduce the magnification/zoom of the tab to see everything you want to see… Also, to get back to “normal” view, simply repeat the steps described above.)

But Wait, There’s More: When in X-Ray Vision mode, you can move your cursor around in the cells the same way you would normally, but you will notice that when you move it over a cell that contains a formula, the formula itself will light up and color-code the constituent elements, and those formula elements that are on the current tab will light up in the corresponding colors. This is a great way to audit formulas, or if nothing else, pass the time while waiting for the next Groupon to drop into your Inbox.

Do you have any 60-Second Skills that you would like to share? Send them over!

This is what drives the model.

Posted in:

Previous post:

Next post: