Many users have encountered such a problem. When working with tables, it may be necessary to view cells containing formulas to calculate data. While in regular mode, you can view the calculation result of this formula. The program has two ways to view formulas: the ability to view all formulas at once. Instead of a numeric value in cells, the formulas will be displayed, and You can also set the formula to say as a string in a separate cell. In this step by step tutorial with pictures, I will show you how to display formulas in Microsoft Excel 2013.

How to display all formulas in Microsoft Excel

By default, when you enter a formula in a cell and press the Enter key, Excel immediately displays the calculated result. To show all formulas in the cells that contain them, use one of the following methods.

1. Option ” Show Formulas ” in Excel

Consider showing all formulas on Microsoft Excel 2013 worksheet. Start by going to the Formulas tab > Formula Auditing and clicking the Show Formulas button.

In this mode, instead of the values, you will be shown all entered formulas. You can similarly exit this mode by clicking the Show Formulas button of Formula Auditing.

2. Options ‘ Show formulas in cells instead of their results ‘

In Excel 2010, Excel 2013, and Excel 2016, go to File> Options. In Excel 2007, click Office Button> Excel Options.

Select Advanced   On the left pane, scroll down to the Display options for this worksheet and select the Show formulas in cells option instead of their results.

At first glance, this might seem like a long way, but you may find it useful when you want to display formulas in several Excel worksheets in currently open workbooks. In this case, you need to select the sheet name from the drop-down list and check the option Display options for this worksheet for each sheet.

Hiển thị công thức excel trong các ô thay vì kết quả được tính toán của chúng

3. Excel keyboard shortcut for displaying formulas

The fastest way to see every formula in your Excel spreadsheet is to press the following shortcut: Ctrl + ` (The accent key ( ` ) is the furthest key to the left next to the number 1 key).

The ThemiddleShow Formulas shortcut shows cell values and formulas. To get back a formula result, press the shortcut key again.

Note. Whichever method you use above, Microsoft Excel will display all formulas of the current workbook. To display the formulas in other worksheets and workbooks, you’ll need to repeat each page’s process.

If you want to see the data used in formula calculations, use any of the above methods to display the formula in cells, then select the cell containing the formula in question, and you will see The results are similar to the following:

Note. If you click on a cell with a formula, but the formula is not visible in the formula bar, the formula is most likely hidden, and the worksheet is protected.

How to print formulas in Excel

If you want to print formulas in your Excel spreadsheet, instead of printing the calculated results of those formulas, use any of the three methods to display formulas in cells. Then print the spreadsheet as you would typically print your Excel files ( File> Print).

The reason Excel displays formulas, not results

Have you ever happened to you that you enter a formula in a cell, press the Enter key, and Excel still displays the formula instead of the result? Don’t worry, your Excel is fine, and we will fix that issue immediately.

In general, Microsoft Excel can display formulas instead of calculated values for the following reasons:

  1. You may have inadvertently activated the Show Formulas mode by clicking the corresponding button or pressing the CTRL + ` shortcut. To get back the calculated result, turn off the Show Formulas button or press CTRL + `again.
  2. You may have accidentally typed a space or single quotes ( ‘) before the equal sign in the formula:

    Excel hiển thị công thức, không phải kết quả

    When an equal sign precedes space or parenthesis, Excel treats the cell contents as text and does not evaluate any formulas in the cell. To fix this, remove the leading or single quote spaces.

  3. Before entering the formula in a cell, you may have set the cell’s format to Text. In this case, Excel also treats the formula as a regular text string and does not calculate it.

    Công thức excel được nhận dang dưới dạng văn bản

To fix this error, select the cell, go to Home tab > Number and set the cell’s format to General, and while in the cell, press F2 and ENTER.

Here’s how you display formulas in Excel. A piece of cake, right? On the other hand, if you plan on sharing your spreadsheets with other users, you may want to protect your formulas from overwriting or editing them and even hide them from view.