How To Show Formulas in Excel for Clarity

Did you know you can make Excel display formulas instead of results?

Whether debugging errors, collaborating with a team, or simply learning Excel, you should know how to reveal those hidden formulas. The good news? Excel has built-in tools to make this process quick and easy.

Let's walk you through how to show formulas in Excel step by step.

Methods to Show Formulas in Excel

Microsoft Excel provides multiple options to view formulas instead of their calculated results. Let's go through each of them to find the best option for you:

1. Using the Ribbon.

The Ribbon is Excel’s primary navigation tool. It includes a built-in option to show formulas across the worksheet. Here's how to use it:

• Open your Excel workbook and navigate to the Formulas tab in the Ribbon.

  

• Locate the Formula Auditing group, where you’ll see the Show Formulas button.

  

• Click on Show Formulas, and the worksheet will immediately display the formulas used in each cell instead of their results.

  

Pro tip: Clicking the button again toggles back to the default view, showing formula results instead of formulas. You can still see the formula in the formula bar.

2. Keyboard shortcut.

If you prefer faster, keyboard-driven actions, Excel has a shortcut for toggling formula display:

• Press Ctrl + ` (the grave accent key, typically located above the Tab key on your keyboard).

• This shortcut instantly switches between showing formulas and cell values.

When to use: You should consider using this when working on large datasets and need a quick way to review your formulas without navigating menus.

3. Excel Options.

For a more permanent solution or worksheet-specific settings, use Excel Options:

• Click File in the top-left corner of Excel, then select Options from the menu.

• In the Excel Options window, go to the Advanced tab.

• Scroll down to the Display options for this worksheet section.

• Check the box labeled Show formulas in cells instead of their calculated results.

• Click OK to apply the changes.

  

Keynote: This method applies the formula display setting only to the active worksheet, leaving other worksheets unaffected.

4. FORMULATEXT function.

The FORMULATEXT function is great for displaying formulas as text in other cells:

• Select the cell where you want the formula to be displayed as text.

• Enter the formula =FORMULATEXT(cell_reference) (replace cell_reference with the address of the cell containing the formula you want to display).

  • For example, if the formula =B2 * C2 is in cell C1, entering =FORMULATEXT(D2) in another cell will display =B2 * C2 as text.

• Press Enter, and the referenced formula will appear as plain text.

  

Why Use FORMULATEXT? This function is ideal for documenting or sharing formulas with others without affecting the underlying data or calculations.

Highlighting Cells with Formulas

Need to identify all cells with formulas? Use this feature:

• Go to the Home tab and click Find & Select (in the Editing group).

• Choose Formulas.

• Excel will highlight all cells containing formulas, making them easy to locate.

How To Print Formulas in Excel

Unlike printing calculated results, having formulas in a physical format can provide a deeper understanding of the logic behind a spreadsheet or help in collaborative work. See how to print formulas in Excel:

• Display formulas using one of the methods above.

• Go to File > Print.

• Ensure the layout accommodates the expanded width of cells containing formulas.

• Print the worksheet with formulas visible.

How To Hide Formulas in Excel

Sometimes, you may have a reason to hide formulas. If you want to protect formulas from being viewed:

1. Select the cells with formulas.

   

2. Right-click and choose Format Cells.

   

3. Go to the Protection tab and check Hidden.

   

4. Protect the worksheet by going to Review > Protect Sheet.

   

5. Now, the formulas won’t be visible or editable.

How to Fix Excel Showing Formula Instead of Result

If Excel shows the formula instead of the result, it can be due to several reasons. You can ensure Excel displays the results as expected by learning to identify and fix these issues:

1. Formula display mode is enabled: You may have accidentally turned on "Show Formulas" mode, which displays all Excel formulas in the sheet instead of their results. You can toggle this off by pressing Ctrl + ~ or going to the Formulas tab and unchecking "Show Formulas."

2. Cell is formatted as text: If the cell where you entered the formula is formatted as text, Excel treats the formula as plain text and doesn’t calculate it. Change the cell format to General or Number, then re-enter the formula.

3. A space or apostrophe before the equals sign: Excel won't recognize the input as a formula if there’s a space or an apostrophe (') before the = sign. Remove these characters to fix the issue.

4. Formula errors: If the formula syntax is incorrect or incomplete, Excel might not calculate the result and could show the formula itself. Double-check your formula for accuracy.

Final Thoughts

Knowing how to show formulas in Excel is a key skill for effective spreadsheet management. Whether auditing complex calculations, teaching others, or troubleshooting errors, these methods give you full control over your formulas. Try these steps today to make the most of Excel’s formula display features!

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ

1. How do I quickly toggle between showing formulas and results in Excel?
Use the keyboard shortcut `Ctrl + `` (the grave accent key) to switch between displaying formulas and their results instantly.

2. Can I display formulas only in one specific worksheet?
Yes, you can enable formula display for a single worksheet via File > Options > Advanced, then check "Show formulas in cells instead of their calculated results."

3. How can I print my Excel sheet with formulas visible?
First, activate the formula display using one of the methods outlined above. Then, adjust your layout for wider cells and print the worksheet directly with the formulas visible.

Related Articles

Excel Conditional Formatting Based on Another Cell

How to Use PI in Excel

How to Sort by Date in Excel