REPS 101 - Online FAQ

Excel Tip: A Quick Way to See Where You Have Formulas and Where You Typed Something Manually

Posted by Taylor Reporting Solutions, LLC on March 9, 2011 at 10:40 AM

When you're looking at a spreadsheet that uses both formulas and manually entered numbers, it may be confusing to quickly get a feel for which cells contain which type of information. At some point, someone may have typed a number over a formula, but just by looking at the spreadsheet, you'd have no idea that your formulas aren't working.

For example, can you tell at a glance where someone overwrote the formulas in the spreadsheet below?

It's not easy to do quickly. You could click in each cell that you thought should contain a formula, but that might take a while, expecially if you have a large set of data.

Here's the super-easy, super-fast way to find out. While you're looking at the worksheet, on your keyboard, type "Control" + "~" (that little wavy symbol is called a tilde and it's located above your "Tab" key). This shortcut will show you the actual contents of each cell, which for many cells, will be the actual formula (not the result of the formula, which is what you usually see). This is what your spreadsheet will look like after hitting "Control" + "~":

You can now easily see which cells contain formulas and which contain manually entered data -- I added red brackets to make it obvious. In the sheet above, you can see that formulas in April and August have been deleted and you know exactly what cells to fix. To return to the previous view (without the formulas) just hit "Control" + "~" again. You can toggle back and forth from one view to the next whenever you want to without affecting anything. The content of the cells won't change when you hit "Control" + "~", only the view changes. It's easy to go back and forth.

Give it a shot on a worksheet that you're currently using. Who knows, maybe you'll find some errors that you didn't even know about!

If you would like more information about this shortcut, or if you have any other questions about Excel, email me at or tweet me @HelpMeKate.

Categories: None

Post a Comment


Oops, you forgot something.


The words you entered did not match the given text. Please try again.

Already a member? Sign In