At some point, as a Microsoft Excel user, you will need to learn how to create formulas. These may be mathematical formulas, or formulas that contain built-in functions that find averages, products, and so on.
However, when viewing the worksheet, one does not see the formula but the result of the formula.
If you click inside a cell with a formula, the formula will display in the formula bar at the top of the window. That's great if you just want to look at one single formula or function! But what if your worksheet has many formulas that you need to inspect?
We'll show you how to view all formulas on the Excel worksheet. We'll even explain how to print the spreadsheet formulas!
There are three ways to show formulas in an Excel worksheet. The three method are shown below in order of difficulty.
Display Excel Formulas - Method #1: Keyboard Shortcut
My favorite - a keyboard shortcut! If you don't have the grave or tilde symbol on your keyboard, skip to Method #2.
Open the spreadsheet. Then hold the Ctrl key while pressing the grave (`) or tilde (˜) key. On many Windows keyboards the grave is the "capital" of the number 6, and the tilde is the "capital" of the number 1.
Pressing these two keys will toggle back in forth between displaying the formula values and the formulas themselves.
Display Excel Formulas - Method #2: the Excel Ribbon
If you don't like keyboard shortcuts, this method works quickly in Excel 2007, 2010, and newer versions:
Click the Formulas tab on the Excel 2007 ribbon.
In the "Formula Auditing" section to the right, click Show Formulas.
To hide the formulas and return to the worksheet, follow the same instructions above and click Show Formulas to un-select it.
Display Excel Formulas - Method #3: Excel Options
Click the Office button in the upper left corner and then click the Excel Options button in the lower right.
Click Advanced on the left sidebar and scroll down to Display options for this worksheet.
Click to select Show formulas in cells instead of their calculated results and click OK.
Don't forget to remove the check mark in this box when you want Excel to display the calculated results once again instead of the formulas.
Note: When the formulas are displaying on the worksheet instead of the normal worksheet values, the "show formulas" box will be checked in the Excel Options window, and the "show formulas" will be selected on the ribbon.
For many Excel tutorials, check out our main website: Microsoft Office Version and Windows Version Compatibility Chart is our newest tutorial. Another popular tutorial for newer (or rusty!) Excel users is Excel Math Basics: A Beginner's Guide.
We hope this helps. Cheers!
► Take a look at some of our other interesting posts, such as Quickly Enter Time & Date in Excel.
17 comments:
i didn't know u could do this. very good.!
Excellent. I wish I had looked this up years ago.
I was reading another post and saw this one later. This is really good to know. Thanks.
THANKS!!!!!!!!!!!!!
This is good. I've been going nuts copying and pasting formulas to a notepad doc so as to not lose them or to be able to reference them. I feel like an idiot. Well, a happy idiot now. LOL.
It is amazing what you can learn on the Internet. Microsoft should somehow tell us this on the Excel window. Yeah. - George M.
I had no idea u could do this. I'm a dodo head. Cheer back at ya.
Great post. Found my answer right away. Thanks a bunch.
Very handy. Very dandy. Still hate the ribbon...
Thanks!!!
Queen Bibi
damn ,wish i'd know this like 5 years ago duh -- LOL. thanks blogger!
THIS WILL BE VERY HELPFUL FOR SURE.
Most excellent bit of information here.
wow oh wow oh wow! I wasn't even looking for this, was reading something else and i'm amazed!!!!!!!!!!!!!!!!!!
Holy cow! THis is good to know, yeah !!!
Good news. Glad I found your post. Thanx.
This is extremely good to know, especially if you have a lot of formulas and can't remember which cells they are all in. I like the Keyboard Shortcut the best! Thank you.
Post a Comment