5 Ways Show Formulas

Introduction to Showing Formulas in Excel

When working with Excel, it’s often necessary to display formulas instead of their calculated results. This can be particularly useful for troubleshooting, auditing, or simply understanding how a worksheet is set up. There are several methods to show formulas in Excel, each with its own advantages and use cases. In this article, we’ll explore five ways to display formulas, making it easier to work with and understand your Excel worksheets.

Method 1: Using the Formula Bar

The most straightforward way to view a formula is by using the Formula Bar. Located above the worksheet grid, the Formula Bar displays the formula of the currently selected cell. To view a formula: - Select the cell containing the formula you want to see. - Look at the Formula Bar; it will display the formula instead of the result.

This method is quick and simple but only shows one formula at a time. For a broader view or to display all formulas in a worksheet, other methods are more suitable.

Method 2: Pressing Ctrl + (Backtick)</h2> A convenient keyboard shortcut to display all formulas in a worksheet is by pressing <b>Ctrl + (backtick). This toggle switch shows formulas instead of their calculated values. To return to the normal view, simply press Ctrl + ` again. This method is useful for a quick overview of all formulas in your worksheet but doesn’t offer a way to edit formulas in this view without switching back to the normal view.

Method 3: Using the “Show Formulas” Option

Excel provides a specific option to display formulas, which can be found in the Formula Auditing group of the Formulas tab. To use this feature: - Go to the Formulas tab on the Ribbon. - Click on Formula Auditing. - Select Show Formulas from the dropdown menu.

This option not only displays formulas but also adjusts the column widths to ensure that the formulas are fully visible, which can be very helpful for long formulas. To revert to the normal display, click on Show Formulas again.

Method 4: Using the “Formulas” Option in Excel Options

For a more permanent display of formulas or to customize how formulas are displayed, you can adjust the Excel Options. To do this: - Go to File > Options. - Select Formulas from the left menu. - Under Working with formulas, check the box next to Display formulas in cells instead of their calculated results.

This method allows for a more customized approach to displaying formulas but affects all worksheets and workbooks until changed back.

Method 5: Using VBA Macro

For advanced users, creating a VBA macro can provide a customized button or shortcut to toggle the display of formulas on and off. This involves: - Pressing Alt + F11 to open the VBA Editor. - Inserting a new module and writing a macro that toggles the DisplayFormulas property of the Application object.

This approach offers flexibility and can be integrated into a custom toolbar or ribbon for frequent use.

📝 Note: When displaying formulas, especially using methods that affect the entire worksheet or application, be mindful of potential performance impacts on large or complex worksheets.

In summary, Excel offers multiple ways to display formulas, ranging from simple and temporary views to more permanent and customized displays. The choice of method depends on the specific needs of the user, whether it’s for troubleshooting, auditing, or educational purposes. By understanding and utilizing these methods, users can work more effectively with Excel and gain deeper insights into how their worksheets are structured and calculated.