If you're working with Microsoft Excel, you'll often find yourself in situations where you need to display formulas rather than their results. Whether it's for auditing, learning, or presentation purposes, being able to toggle between the formula view and the result view is a handy skill. Here are five easy ways to display formulas in Excel Sheets:
1. Using the Formula Tab
Excel provides a simple method to display all formulas within a workbook through the Ribbon:
- Select any cell in your worksheet.
- Go to the Formula tab in the Ribbon.
- Click on Show Formulas.
💡 Note: This toggle will show or hide formulas in all open workbooks.
2. Keyboard Shortcut
A quick keyboard combination can also toggle formulas on and off:
- Press CTRL` on your keyboard. Yes, that’s the backtick (grave accent) key, usually found under the escape key.
💡 Note: This shortcut works to toggle back to the standard display of formula results as well.
3. Formula Auditing
Excel’s Formula Auditing tools can help you understand where formulas are being used:
- Navigate to the Formulas tab.
- Under Formula Auditing, click on Trace Precedents or Trace Dependents.
- This will show arrows pointing to the cells that are referenced in or dependent on your selected cell’s formula.
4. Reveal Formulas in Cells
If you need to display a formula in an individual cell without changing the view for the entire workbook:
- Double-click the cell containing the formula, which will show the formula in the formula bar.
- Alternatively, click in the formula bar, and the formula will appear.
💡 Note: Remember, this only shows the formula for the cell currently being edited; other cells remain unchanged in display.
5. Using a Macro
For more advanced users or repeated tasks, a VBA macro can automate this process:
- Open the Visual Basic Editor by pressing ALT + F11.
- Insert a new module by right-clicking on your project file, selecting Insert, then Module.
- Enter the following macro:
Sub ToggleFormulas() If Application.DisplayFormulas = True Then Application.DisplayFormulas = False Else Application.DisplayFormulas = True End If End Sub - Run the macro by going back to Excel, selecting Developer tab, then clicking on Macros, selecting ToggleFormulas, and pressing Run.
These methods provide users with flexible options to display formulas in Excel, catering to various needs from quick overviews to in-depth analysis:
Understanding how to display formulas is crucial for Excel users. It's not only about seeing how calculations are done but also about troubleshooting, maintaining, or presenting spreadsheets. Each method has its strengths, allowing you to choose the one that best suits your current task:
Why would I need to display formulas instead of results?
+
Displaying formulas can be useful for auditing spreadsheets, understanding how calculations are performed, training others, or simply to check for errors in complex spreadsheets.
Can I display formulas in only one sheet instead of the whole workbook?
+
Excel currently does not support displaying formulas for just a single sheet; it’s an all-or-nothing toggle for the workbook.
Will using the ‘Show Formulas’ toggle affect my workbook’s data?
+
No, toggling formulas visibility will not change or affect the data within your workbook; it’s purely a display setting.
How do I return to normal display after showing formulas?
+
Just use the same method you used to show formulas: either toggle the button again, press the keyboard shortcut, or rerun your macro.
Can I customize the appearance of the formula display?
+
Excel doesn’t provide options to customize how formulas are displayed beyond simply showing or hiding them. However, you can change the cell formatting or use color coding to highlight cells containing formulas.
