Excel formulae serve as the foundation for data analysis, allowing users to easily conduct complex computations. However, these formulas are frequently hidden under cell values, making it difficult to understand the logic behind calculations. This comprehensive article “How to Show Formulas in Excel: Unveiling Secrets 2024” delves into the fundamental approaches for displaying formulas in Excel. Whether you’re a newbie looking for clarity or an experienced user enhancing your workflow, discovering these formulae will boost your productivity and knowledge of your data.
Join us as we explore the many methods for revealing formulas in Excel, ranging from easy shortcuts to complex strategies. By the end of this tutorial, you’ll have mastered the technique of exposing formulae, allowing you to fully utilize Excel’s data analysis and manipulation capabilities.
Why we use Show Formulas Feature?
In Excel, the “Show Formulas” feature is used primarily for two main purposes:
- Checking Formulas: It allows you to quickly check and review all the formulas in a worksheet without having to click on individual cells. This is especially useful when you have complex formulas or many formulas spread across different cells. By toggling “Show Formulas,” you can ensure that all formulas are correctly entered and are referencing the intended cells and ranges.
- Debugging: When you encounter errors in your calculations or formulas are not yielding the expected results, “Show Formulas” helps you to debug and identify issues more efficiently. Instead of manually inspecting each cell’s formula, you can toggle this feature to see all formulas at once. This can reveal mistakes such as incorrect cell references, syntax errors, or logical errors in your formulas.
How to show formulas in Excel
Showing formulas in Excel can be very useful for debugging or auditing purposes. Here are the steps to display formulas in Excel:
Method 1:
Using the Keyboard Shortcut
- Open the Excel workbook where you want to show the formulas.
- Click on the worksheet tab at the bottom that contains the cells with the formulas you want to display.
- On your keyboard, press the Ctrl key and the ~ key (the tilde key, located usually on the top-left corner of the keyboard, just below the Esc key).
This shortcut will toggle the display between showing the formulas and showing the formula results.
Method 2:
Using the Ribbon
- Open the Excel workbook where you want to show the formulas.
- Click on the worksheet tab at the bottom that contains the cells with the formulas you want to display.
- Click on the Formulas tab in the Ribbon at the top of the Excel window.
- In the Formula Auditing group, click on the Show Formulas button.
This button will toggle the display between showing the formulas and showing the formula results.
Learn More:
How to Lock Cells in Excel FLASH Fill in Excel How to Freeze a Row in Excel How to password protect in Excel
Method 3:
Using Excel Options
- Open the Excel workbook where you want to show the formulas.
- Click on the File tab in the Ribbon at the top of the Excel window.
- Click on Options at the bottom of the left-hand menu to open the Excel Options dialog box.
- In the Excel Options dialog box, click on Advanced in the left-hand menu.
- Show Formulas in Cells Instead of Their Calculated Results: Scroll down to the Display options for this worksheet section. Check the box that says Show formulas in cells instead of their calculated results.
- Click OK to apply the changes and close the Excel Options dialog box.
Frequently Asked Questions (FAQs)
Q1: How do I toggle between showing formulas and showing results in Excel?
A1: You can toggle between showing formulas and their results by pressing Ctrl + ~ on your keyboard. Alternatively, you can go to the Formulas tab in the Ribbon and click on Show Formulas.
Q2: Why would I want to show formulas in Excel?
A2: Showing formulas can be helpful for debugging, auditing, or reviewing your work. It allows you to see the exact formulas used in your worksheet, making it easier to identify errors or verify calculations.
Q3: Can I show formulas for specific cells only?
A3: Excel does not have a built-in feature to show formulas for specific cells only. When you use the Show Formulas option, it displays formulas for all cells in the worksheet. However, you can use a formula like =FORMULATEXT(A1) in another cell to display the formula from a specific cell.
Q4: How can I print my Excel sheet with formulas displayed?
A4: To print an Excel sheet with formulas displayed, first use the Show Formulas option (press Ctrl + ~ or click on Show Formulas in the Formulas tab). Then, go to the File menu, select Print, and proceed to print your worksheet. This will print the formulas instead of the results.
Q5: Will showing formulas affect my data or calculations?
A5: No, showing formulas is purely a display option. It does not affect the underlying data or calculations. You can toggle back to showing results without any impact on your worksheet.
Q6: How do I resize columns to fit long formulas?
A6: To resize columns to fit long formulas, double-click the boundary on the right side of the column heading. This will auto-adjust the column width to fit the longest formula or content in that column.
Q7: Can I use the Show Formulas feature in Excel Online?
A7: As of now, the Show Formulas feature is available in the desktop version of Excel. In Excel Online, you can use the FORMULATEXT function to display formulas in other cells.
Q8: What should I do if Ctrl + ~ does not work?
A8: If Ctrl + ~ does not work, ensure that your keyboard layout is set correctly and that no other application is overriding the shortcut. You can also try using the Show Formulas button in the Formulas tab of the Ribbon.
Q9: How can I show formulas in a protected worksheet?
A9: To show formulas in a protected worksheet, you first need to unprotect the sheet. Go to the Review tab and click Unprotect Sheet. Then, use the Show Formulas option. Once done, you can protect the sheet again if needed.
Q10: Is there a way to show formulas in multiple worksheets at once?
A10: To show formulas in multiple worksheets at once, you need to group the worksheets. Hold down the Ctrl key and click on the tabs of the sheets you want to group. Then, use the Show Formulas option. This will display formulas in all grouped sheets. Remember to ungroup the sheets afterward.
