How to find where formula is coming from in Excel

Índice Show

How to print formulas in Excel
Why is Excel showing formula, not result?
You may also be interested in

You can better understand what’s going on in your workbooks by showing and highlighting all the formulas in a sheet.

By default, Excel displays the results of formulas in the worksheet instead of showing the actual formulas. However, you can choose to have Excel display the formulas so you can see how they’re put together.

Click the Formulas tab.
Click the Show Formulas button.

Formulas are displayed in the worksheet and the columns widen to accommodate the formulas, if necessary.

If you display formulas and then select a cell that contains a formula, colored lines appear around cells that are referenced by the formula.
Click the Show Formulas button again to hide the formulas.

Formulas are no longer displayed and the columns return to their original sizes.

If you don’t want to see the actual formulas, but want to know which cells contain them, highlight cells with formulas instead.

Click the Home tab.
Click the Find & Select button.
Select Formulas.

Any cells that contain a formula are highlighted; however, this doesn’t change the cell formatting. When you click any other cell in the worksheet, the highlighted cells are unselected.

In this short tutorial, you will learn an easy way to display formulas in Excel 2016, 2013, 2010 and older versions. Also, you will learn how to print formulas and why sometimes Excel shows a formula, not result, in a cell.

If you are working on a spreadsheet with a lot of formulas in it, it may become challenging to comprehend how all those formulas relate to each other. Showing formulas in Excel instead of their results can help you track the data used in each calculation and quickly check your formulas for errors.

Microsoft Excel provides a really simple and quick way to show formulas in cells, and in a moment, you will make sure of this.

Usually, 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 containing them, use one of the following methods.

1. Show Formulas option on the Excel ribbon

In your Excel worksheet, go to the Formulas tab > Formula Auditing group and click the Show Formulas button.

Microsoft Excel displays formulas in cells instead of their results right away. To get the calculated values back, click the Show Formulas button again to toggle it off.

2. 'Show formulas in cells instead of their results' option

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 section and select the option Show formulas in cells instead of their calculated results.

At first sight, this seems to be a longer way, but you may find it useful when you want to display formulas in a number of Excel sheets, within the currently open workbooks. In this case, you just select the sheet name from the dropdown list and check the Show formulas in cells… option for each sheet.

3. Excel shortcut to show formulas

The fastest way to see every formula in your Excel spreadsheet is pressing the following shortcut: Ctrl + `

The grave accent key (`) is the furthest key to the left on the row with the number keys (next to the number 1 key).

The Show Formulas shortcut toggles between displaying cell values and cell formulas. To get the formula results back, simply hit the shortcut again.

Note. Whichever of the above methods you use, Microsoft Excel will show all formulas of the current worksheet. To display formulas in other sheets and workbooks, you will need to repeat the process for each sheet individually.

If you want to view the data used in a formula's calculations, use any of the above methods to show formulas in cells, then select the cell containing the formula in question, and you will see a result similar to this:

Tip. If you click a cell with a formula, but the formula does not show up in the formula bar, then most likely that formula is hidden and the worksheet is protected. Here are the steps to unhide formulas and remove the worksheet protection.

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, just use any of the 3 methods to show formulas in cells, and then print the worksheet as you normally print your Excel files (File > Print). That's it!

Why is Excel showing formula, not result?

Did it ever happen to you that you type a formula in a cell, press the Enter key… and Excel still shows the formula instead of the result? Don't worry, your Excel is all right, and we will have that mishap fixed in a moment.

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

You may have inadvertently activated the Show Formulas mode by clicking the corresponding button on the ribbon, or pressing the CTRL+` shortcut. To get the calculated results back, just toggle off the Show Formulas button or press CTRL+` again.
You may have accidentally typed a space or single quote (') before the equal sign in the formula:

When a space or single quote precedes the equal sign, Excel treats the cell contents as text and does not evaluate any formula within that cell. To fix this, just remove the leading space or single quote.
Before entering a formula in a cell, you may have set the cell's formatting to Text. In this case, Excel also perceives the formula as a usual text string and does not calculate it.

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

This is how you show formulas in Excel. A piece of cake, isn't it? On the other hand, if you plan to share your worksheet with other users, you may want to protect your formulas from overwriting or editing, and even hide them from viewing. And it is exactly what we are going to discuss in the next article. Please stay tuned!

You may also be interested in

Have you ever inherited a worksheet that someone else set up or you created and haven’t played in for some time? If you need to update the formulas and functions, but do not know what other calculations will be affected you could quickly make a lot of work for yourself! You might also be hunting a miscalculation and need to know where the formula is grabbing data from.

Excel offers a simple way to review which cells are both dependent on and depended upon by other cells. The feature is called trace precedents and dependents.

Find Formulas, Functions and Cells Connected to a Cell

Open your workbook to a sheet that contains cells with precedents or dependents.
Select the cell in which you want to check precedents and/or dependents.
Click the Trace Precedents or Trace Dependents button in the Formula Auditing group of the Formulas tab.

Arrows leading from the selected cell extend and point to those cells that use its data in their calculations. You can now go work on the data in the dependent cells using the arrows for reference.

When you are through working with the data for that cell, click the Remove Arrows button to clear all precedent and dependent arrows from the screen.

Use trace precedents and dependents to repair formulas and functions, make updates without breaking your spreadsheet, and to quickly visualize all the data impacted by your calculations.

Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.