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.
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!
good idea! I look forward to getting these.
Thank you! This is an awesome tip!!
Wow, this is great. I knew there was a way to do this and haven’t had the time to figure it out.
good to know – esp. for troubleshooting!
Good stuff. Thanks.
Glad you all found it helpful!
Youre so right. Im there with you. Your weblog is definitely worth a read if anybody comes throughout it. Im lucky I did because now Ive obtained a whole new view of this. I didnt realise that this issue was so important and so universal. You definitely put it in perspective for me. XR2011K6