5 Ways to Display Formulas in Excel Instantly

The ability to display formulas in Excel instantly can significantly enhance productivity and understanding of your spreadsheets. Whether you're auditing a complex workbook, teaching someone Excel, or troubleshooting an issue, knowing how to show formulas can be very beneficial. Here are five ways to make Excel display formulas quickly and efficiently:

1. Show Formulas Keyboard Shortcut

One of the fastest ways to display formulas in Excel is by using a keyboard shortcut:

• On Windows, press Ctrl + </strong> (the grave accent key, next to the number 1 key).</li> <li>On macOS, press <strong>Cmd +.

This toggles Excel between showing formulas in cells and displaying their results.

How to Use This Shortcut:

1. Open your Excel workbook.
2. Press the keyboard shortcut:
• Windows: Ctrl + </li> <li>Mac: Cmd +
3. You’ll see formulas replace the calculated values in cells.
4. Press the shortcut again to revert to normal view.

💡 Note: Remember this shortcut for quick toggling between values and formulas.

2. Using the ‘Show Formulas’ Option

If you prefer a visual toggle or want to explore Excel’s settings, you can use the ‘Show Formulas’ option:

1. Go to the Formulas tab on the Ribbon.
2. Click on Show Formulas under the Formula Auditing group.

This action will reveal all formulas in your worksheet, displaying them instead of the calculated results.

Steps to Toggle Back:

• Click the same ‘Show Formulas’ button again.

💡 Note: This option provides a visual indicator for those who might not remember the keyboard shortcut.

3. Utilizing Cell Formatting

You can change the cell format to display formulas through custom cell formatting:

1. Select the cells you want to show formulas for.
2. Right-click and choose Format Cells… or press Ctrl + 1 on Windows, Cmd + 1 on macOS.
3. Under the Number tab, choose Custom from the list.
4. Type in ;;@ in the Type box and click OK.

This custom format effectively displays the cell's formula instead of its value. However, this approach doesn't affect the spreadsheet's functionality; it only changes how formulas are presented for those specific cells.

Revoking the Formatting:

• Select the cells with custom formatting.
• Go back to Format Cells, select a different format, or type General in the Type box.

4. Changing Workbook Options

Excel provides an option to alter how formulas are shown across the entire workbook:

1. Open the Excel Options window by clicking File > Options.
2. Select Advanced from the sidebar.
3. Under ‘Display options for this workbook,’ check Show formulas in cells instead of their calculated results.
4. Click OK to apply.

This setting will affect all worksheets in the open workbook, forcing Excel to display formulas instead of results.

To Return to Normal:

• Uncheck the same option in the Excel Options.

💡 Note: This method is useful for those needing to see formulas across the entire workbook without toggling each sheet.

5. VBA Macro to Toggle Formulas Display

For those comfortable with VBA (Visual Basic for Applications), you can create a macro to toggle formulas on and off:

Sub ToggleFormulas()
    Application.ScreenUpdating = False
    If Application.Calculation = xlCalculationAutomatic Then
        Application.Calculation = xlCalculationManual
        ActiveSheet.Cells.NumberFormat = “;;;@”
    Else
        Application.Calculation = xlCalculationAutomatic
        ActiveSheet.Cells.NumberFormat = “General”
    End If
    Application.ScreenUpdating = True
End Sub

How to Use the Macro:

1. Open the VBA editor by pressing Alt + F11.
2. Insert a new module and paste the code above.
3. Save your workbook as a Macro-Enabled Workbook (.xlsm).
4. Assign the macro to a button or run it manually from the Developer tab.

This macro will switch between showing formulas and returning to normal calculation mode for all cells in the active sheet.

To display formulas in Excel instantly, you have several options at your disposal, each suited for different levels of complexity and user preference. Here are some key takeaways:

• Keyboard shortcuts provide the quickest and simplest method for toggling between formula display and normal view.
• The 'Show Formulas' button offers a visual aid for those less familiar with shortcuts.
• Custom cell formatting can be used for specific cells or ranges where you want formulas to be always visible.
• Workbook settings change how Excel displays formulas across the entire document, useful for in-depth analysis.
• VBA macros offer flexibility and automation, especially for frequent use or sharing workbook settings with colleagues.

By utilizing these methods, you can effectively display formulas in Excel, streamline your workflow, enhance collaboration, and improve your understanding of complex spreadsheets.

Can I see formulas in a specific cell only?

+

Yes, by using custom cell formatting, you can display the formula of specific cells. Select the cell, go to Format Cells, choose Custom, and enter ;;@.

Will showing formulas slow down my Excel workbook?

+

Toggling formulas with shortcuts or the ‘Show Formulas’ button doesn’t significantly impact performance. However, workbook-wide settings might if formulas are complex and numerous.

How do I share a workbook with formulas visible?

+

You can either use custom cell formatting for specific cells or change the workbook options to show formulas. Saving the file with these settings will allow others to see the formulas when they open it.