How to Show Formula in Excel

If you frequently use Microsoft Excel, then the chances are that you have come across instances where you may need to view a formula to understand how a cell is performing a particular calculation. However, sometimes the formula may be hidden, and you may not be able to see the underlying formula that results in the calculated value. Fortunately, Excel has a straightforward solution to this challenge. This blog post will guide you through the steps to show formulas in Excel, making it easier to check, understand, and troubleshoot your spreadsheets.

Step 1: Open the Excel Worksheet

The first step to showing the formula in Excel is to open the worksheet that you want to work with. If you already have the workbook open, navigate to the worksheet with the formula you want to see and select it.

Step 2: Press Ctrl + ` to show Formulas in the Worksheet

To view the formulas in the worksheet, press the “Ctrl + ` ” keys on your keyboard. The ` is often located in the top left corner of US keyboards and is known as the grave accent symbol. When you press these keys, Excel will display all the formulas in the cells, and all the functions in the formula bar will also be visible.

Step 3: Press Ctrl + ` again to hide the Formulas

If you want to hide the formulas again to return to the values, press the “Ctrl + `” keys on your keyboard again. Doing so will hide the formulas, and you will see the values displayed in the cells as you did earlier.

Alternative Step – Use the ‘Show Formulas’ Button

Excel also gives users the option to use the ‘Show Formulas’ button to display the worksheet’s formulas. To access it, go to the ‘Formulas’ tab of the ribbon menu. In the ‘Formula Auditing’ section, click on the ‘Show Formulas’ button. This will instantly display all the formulas in the worksheet. If you want to hide the formulas, click on the ‘Show Formulas’ button again, and they will disappear.

Conclusion

Knowing how to display formulas in Excel comes in handy when working with complex spreadsheets. With these simple steps, you can display and hide formulas in a matter of seconds. Try out this feature today to make troubleshooting and auditing your Excel files a breeze!

Why Should You Show Formulas in Excel?

There are several reasons why you may want to show formulas in Excel. For one, it can be challenging to debug an Excel formula when you do not see exactly how a value is being calculated. When you show formulas, you can identify errors in your formulas quickly. Similarly, if you are sharing your workbook with others, you may want them to see the formulas so they can understand how the calculations work. Additionally, when dealing with large, complex spreadsheets, it can be difficult to remember where each formula is located, and showing formulas can help pinpoint them.

How to Show Formulas in Excel for Printing

While viewing formulas in Excel is handy for auditing and editing, sometimes, you may need to print the worksheet with the formula’s visible. To do this, go to the ‘Page Layout’ tab on the ribbon. In the ‘Page Setup’ section, click on the ‘Sheet Options’ button. Under the ‘Print’ section of the ‘Sheet Options’ dialog box, check the box labeled ‘Formulas.’ Click OK, and Excel will now print the worksheet with all the formulas visible.

How to Show Formulas in Excel for Specific Cells

If you do not want to show all the formulas in the worksheet, you can display formulas for specific cells only. To do this, select the cell that you want to display the formula for. Double-click the cell to move the cursor into the formula bar. Now press the ‘F2’ button to edit the formula. Once you are in the formula editor, press ‘Ctrl + A’ to select the entire formula, then ‘Ctrl + C’ to copy it. Finally, hit the ‘Esc’ button to exit the formula editor. Paste the formula into another cell or the same cell, and voila! The formula is now visible.

Conclusion

Showing formulas in Excel can save you time and effort. Whether you need to debug formulas or share them with others, these simple steps will help you display formulas quickly and easily. Try them out on your next Excel spreadsheet to make troubleshooting a breeze.

FAQs

Here are some frequently asked questions and answers related to showing formulas in Microsoft Excel:

How do I show formulas in Excel that are hidden by a filter?

If your formulas are hidden by a filter in Excel, you can’t use the keyboard shortcut (Ctrl + `) to display them. Instead, go to the ‘Data’ tab and click on the ‘Clear’ button under the ‘Sort & Filter’ section. This will remove the filter and display all the formulas again.

What happens if I accidentally modify an important formula while viewing it?

If you modify a formula accidentally, you can undo your changes by pressing ‘Ctrl + Z.’ Alternatively, you can also restore the formula to its original state by pressing ‘Esc’ as soon as you make the mistake.

Is there a way to quickly switch between formula and value view?

A fast way to switch between the formula and value view in Excel is by pressing ‘Ctrl + `’ twice. This shortcut will toggle between displaying the result of the formula and showing the formula itself.

How can I show formulas in Excel on Mac computers?

On Mac computers, the keyboard shortcut to show formulas in Excel is ‘Command + `’ (grave accent symbol). If you want to hide the formulas again, use the same keyboard shortcut.

Can I print formulas in Excel without showing them on the worksheet?

Yes, if you want to print formulas in Excel without showing them on the worksheet, you can use the ‘Print options’ dialog box. Go to the ‘File’ tab and click on ‘Print.’ In the ‘Settings’ section, under ‘Print Active Sheets,’ select ‘Print Formulas’ and click on ‘Print.’