How to Display Formulas in Excel
To display formulas in Excel instead of their results, press Ctrl + ** (the grave accent key near the top-left of your keyboard) to toggle them on and off, or click **Show Formulas** in the **Formula Auditing** group on the **Formulas** tab. On a Mac, the same **Ctrl + shortcut works (or use Cmd + `).
As an expert in Microsoft Excel, I am often asked about different features available within the software. One of the most common questions is how to display formulas in Excel. Whether you’re a seasoned Excel user or just starting out, this is an essential tool to make your life easier when working with spreadsheets. In this post, I will take you through the step-by-step process of displaying formulas in Excel, so you can quickly and easily find errors and better understand how data is calculated.
Why Displaying Formulas in Excel is Important
Before getting into how to display formulas in Excel, it’s important to understand why it’s such a useful tool. By default, Excel displays the results of calculations, rather than the formulas used to arrive at those results. While this is helpful in many scenarios, it can be frustrating when trying to troubleshoot issues or verify calculations. By showing the formulas, you can easily see the underlying mathematical equations used to arrive at each result. (When you want to go back to the calculated values, you can hide the formulas just as easily.)
How to Display Formulas in Excel
Step 1: Select the Cells Containing Formulas
The first step to displaying formulas in Excel is to select the cells containing the formulas you want to show. This can be done one of two ways:
- Manually selecting the cells with your mouse or keyboard.
- Using the keyboard shortcut “Ctrl + A” to select all cells in the worksheet.
Step 2: Open the Formulas Tab
With your desired cells selected, navigate to the Formulas tab located on the Excel ribbon.
Step 3: Click on the “Show Formulas” Button
In the Formulas tab, locate the “Formula Auditing” group. Here, you will find a button labeled “Show Formulas”. Clicking this button will instantly change the display of the selected cells from showing results to showing formulas. Note that Show Formulas is a worksheet-wide toggle — it reveals every formula on the active sheet at once, not only the cells you selected.
Step 4: Toggle Back to Normal View
Once you’re done viewing the formulas, simply repeat the same steps above, and click the “Show Formulas” button again. This will toggle the view back to the default display of showing results instead of formulas.
Displaying formulas in Excel is a simple tool that can greatly enhance your productivity and accuracy when working with spreadsheets. With the step-by-step instructions provided above, you can easily switch between showing results and showing formulas whenever it’s needed. Hopefully, this post has helped to streamline your workflow and make your experience with Excel a little bit easier!
Benefits of Displaying Formulas
Aside from troubleshooting and verifying calculations, there are several other benefits to displaying formulas in Excel. One of the most helpful is being able to easily copy and paste formulas from one cell to another. When a formula is copied in Excel, the cell references are also copied. This means that pasting the formula into a new cell will automatically adjust the formula to reference the correct cells. By displaying formulas, you can easily verify that the cell references have been updated correctly, and avoid costly errors.
How to Print Formulas in Excel
If you need to print out formulas in Excel, simply displaying them on the screen won’t be enough. Fortunately, it’s still a straightforward process:
Step 1: Open the Page Setup Dialog Box
Navigate to the Page Layout tab on the Excel ribbon, and click on the arrow in the bottom-right corner of the Page Setup group. This will open the Page Setup dialog box.
Step 2: Select the Sheet Tab
Once in the Page Setup dialog box, click on the Sheet tab.
Step 3: Check the “Formulas” Box
In the “Print” section of the Sheet tab, locate the box labeled “Formulas”. Check this box to tell Excel you want to print the formulas instead of the results.
Step 4: Print Your Worksheet
With the “Formulas” box checked, you’re ready to print your worksheet. Simply navigate to the File tab and select “Print”. Your formulas will now be printed instead of the results.
The Shortcut Way to Display Formulas in Excel
If you find yourself frequently switching between displaying formulas and results in Excel, there’s a quick and easy way to toggle between these views. Simply use the keyboard shortcut “Ctrl + `” (that’s Ctrl plus the grave accent key usually located near the top left of your keyboard). This will instantly switch between the default view of showing results and the “Show Formulas” view.
By now, you should have a good understanding of how to display formulas in Excel, as well as several tips and tricks to make your experience even smoother. Whether you’re troubleshooting, copying formulas, or printing out worksheets, displaying formulas is an essential tool for any Excel user. Armed with the above tips, you’ll be able to easily switch between results and formulas, print your worksheets, and use keyboard shortcuts to save time and boost your productivity!
How to Display Formulas in Excel on a Mac
The process is nearly identical in Excel for Mac. The keyboard shortcut Ctrl + ** (grave accent) toggles formulas on and off, exactly as it does on Windows — and on most Mac layouts **Cmd + works too. If you prefer the ribbon, go to the Formulas tab, find the Formula Auditing group, and click Show Formulas. The grave accent key (`) sits to the left of the number 1 on a standard Apple keyboard, sharing a key with the tilde (~).
Show a Single Formula with FORMULATEXT
The Show Formulas toggle reveals every formula on the sheet, but sometimes you just want one formula displayed as text alongside its result. The FORMULATEXT function does exactly that: it returns the formula in a referenced cell as a text string. For example, =FORMULATEXT(B2) displays the formula stored in cell B2 without changing how B2 itself behaves. This is handy for documentation, audits, or building a side-by-side column that shows each calculation next to its output. If you simply want to read a single formula, you can also click the cell and look at the formula bar at the top of the window.
Why Are My Formulas Showing as Text?
A common surprise is typing a formula and seeing the literal text (like =A1+A2) appear in the cell instead of a calculated result. There are three usual causes:
- Show Formulas is turned on. Press Ctrl + ` to toggle it back off — this affects the whole worksheet, so it is the most frequent culprit.
- The cell is formatted as Text. If the cell’s number format is set to Text, Excel treats your entry as a string. Change the format to General (Home tab → Number group), then re-enter the formula by selecting the cell and pressing F2, followed by Enter.
- A leading space or missing equals sign. A formula must begin with
=and have no space in front of it, or Excel reads it as plain text.
Conversely, if you want a formula to stay visible as text deliberately, that is a different goal — see how to hide formulas or lock a formula so others can see results without editing your calculations.
Frequently Asked Questions
Here are some common questions that users ask when learning how to display formulas in Microsoft Excel:
Can I just tap a cell in Microsoft Excel to display the formula?
Yes, you can double-click any cell containing a formula to display the formula in the formula bar above the worksheet. Alternatively, you can use the keyboard shortcut “Ctrl + `”.
How can I print a portion of my Excel worksheet that displays the formulas used?
When you print your worksheet, Excel will, by default, print only the values calculated by the formulas, and not the formulas themselves. To print formulas, go to the “Page Setup” dialog and check the “Formulas” option. This will allow you to print the formulas in the worksheet.
Can you display formulas instead of the calculated values for all cells at once?
Yes, you can use the keyboard shortcut “Ctrl + `” (that’s Ctrl plus the grave accent key usually located near the top left of your keyboard) to toggle between displaying the formulas and the resulting values for all cells at once.
Is there a way to show both the formula and result in a single cell?
Yes, you can choose to display the formula and result in the same cell by first selecting the cell containing the formula. Then, instead of clicking “Show Formulas” in the “Formula Auditing” group, click “Display the Result and Formula in the Cell” instead. The formula will then appear in the cell with the result displayed normally.
Why can’t I see the formulas or results when I use the “Show Formulas” command?
There may be a few reasons why the formulas or results are not displaying after using the “Show Formulas” command, including having the worksheet or specific cells locked, or the presence of hidden rows or columns. Make sure to check for these issues before seeking further troubleshooting.
What is the keyboard shortcut to show formulas on a Mac?
The shortcut is the same as on Windows: press Ctrl + ** (the grave accent key, to the left of the 1 key) to toggle formulas on and off. On most Mac keyboards **Cmd + also works. You can also use the Show Formulas button in the Formula Auditing group on the Formulas tab.
