View Calculation Formulas in Excel

When I inherit a spreadsheet from someone I always want to take a look at it for accuracy. Reviewing the spreadsheet also allows me to understand the creator’s logic. And let’s face it, few people if any document all the formulas on a separate tab in the worksheet.

So I often want to look at the formulas in the worksheet. Now there are a few ways to view calculation formulas in worksheets (ranging from simple to more complex).

The most basic way is to examine a single calculation by selecting the cell with the formula and viewing the calculation in the formula bar.

In this example I have placed my cursor in cell D2 and the calculation formula shows as =B2*C2

There are also times where you may want to display all the calculations in your worksheet. There are three ways to do this.

The most difficult is to click on the windows button at the top left of Excel then choose Excel Options. This will bring up the Excel Options dialog box. Choose Advanced and scroll down until you get to the Display options for this worksheet.  Check Show formulas in cells instead of their calculated results. When done, click on the OK button.

Excel will now display all your calculation formulas.

To redisplay the calculation, go back in and uncheck this box.

Now this way works but is not fast. You can more easily accomplish the same thing by selecting Formulas from the Ribbon.

Ribbon > Formulas > Show Formulas

Finally the fastest way to display your calculation formulas is to use a keyboard shortcut.
Press and hold the CTRL key then press the ` key (CTRL + `) to show calculation formulas in Excel (` is found on the keyboard just below the ESC key).

So now you know how to view formulas. What you do with that knowledge is a topic for another post.

No comments:

Post a Comment