How to Display Formulas in Excel

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 displaying formulas, you can easily see the underlying mathematical equations used to arrive at each result.

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.

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!

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.