How to How to Show All Formulas in Cells in Excel in Excel - Practical Guide

Learn to display all formulas within cells instead of their results, a critical debugging and auditing technique. This advanced skill helps you verify calculations, identify errors, and document spreadsheet logic without clicking individual cells. Essential for large datasets and collaborative work where transparency is required.

Why This Matters

Displaying formulas enables quick auditing of complex spreadsheets, error detection, and ensures formula integrity across teams. It's essential for compliance, training, and documentation purposes in financial and data analysis roles.

Prerequisites

•Understanding of Excel formulas and cell references (relative vs. absolute)
•Familiarity with Excel ribbon interface and basic navigation
•A spreadsheet with existing formulas to test the feature

Step-by-Step Instructions

Access the File Menu

Click File > Options in the ribbon to open Excel Options dialog. This is the gateway to all display and calculation settings.

Navigate to Advanced Settings

In Excel Options, click Advanced from the left sidebar menu. Scroll down to the Display Options section for current worksheet.

Enable Formula View Checkbox

Check the box next to 'Show formulas in cells instead of their calculated values' under the Display Options section. This applies the change globally to your active worksheet.

Apply and Confirm Changes

Click OK at the bottom of the Excel Options dialog. All cells with formulas will now display their formula syntax instead of calculated results.

Verify and Toggle Back if Needed

Review your spreadsheet to confirm formulas are visible. To restore normal view, repeat steps 1-3 and uncheck the formula display option.

Alternative Methods

Using Keyboard Shortcut (Ctrl+` Grave Accent)

Press Ctrl+` (grave accent/backtick, usually left of 1 key) to instantly toggle formula view on/off for the active sheet. Fastest method for quick audits.

Formulas Tab in Ribbon (Excel 365/2019+)

Click Formulas tab > Show Formulas button in the Formula Auditing group. This ribbon method provides visual confirmation of the toggle state.

Tips & Tricks

✓Print formulas by enabling formula view before printing—useful for documentation and backup records.
✓Widen columns before enabling formula view to ensure long formulas are fully visible and readable.
✓Use formula view only on one sheet at a time; enable/disable per worksheet as needed to avoid confusion.

Pro Tips

★Combine Ctrl+` with zoom (Ctrl+Mouse wheel) to adjust readability of long formulas without changing column widths.
★Create a separate 'Formula Audit' sheet: copy formulas there, enable formula view, then use for compliance documentation.
★Use Find & Replace (Ctrl+H) while in formula view to identify and update formula patterns across the entire workbook.

Troubleshooting

Ctrl+` keyboard shortcut not working

Check keyboard layout (some locales use different grave accent key). Use File > Options > Formulas > Show Formulas instead. On Mac, try Ctrl+` or check System Preferences for key remapping.

Formulas appear but some cells show values instead

Some cells may contain static values, not formulas. Only cells with formula syntax display in formula view. Use Find & Replace to search for '=' to identify all formula cells.

Formula view enabled but columns too narrow to read formulas

Select all cells (Ctrl+A), then double-click the column border between any two column headers to auto-fit all column widths to content.

Unable to locate Show Formulas option in File > Options

Ensure you're in Advanced settings, not Formulas category. The option appears in File > Options > Advanced > Display Options section near bottom.

Related Excel Formulas

FORMULATEXT()EVALUATE()Function Auditing Tools

Frequently Asked Questions

Does showing formulas affect my data or calculations?

No, formula view is purely a display setting. Your actual data and calculations remain unchanged. Once you toggle back to normal view, everything returns to showing results.

Can I show formulas on some sheets but not others?

Yes, formula view is controlled per-sheet. Enable it on one sheet while keeping others in normal mode. Simply switch sheets and adjust the setting independently.

What's the difference between Ctrl+` and File > Options method?

Both achieve the same result. Ctrl+` is faster for quick toggling; File > Options is better if you forget the shortcut. Use whichever fits your workflow.

Will printing in formula view show all formulas clearly?

Yes, but you must auto-fit column widths first to prevent formula truncation. Preview before printing to confirm readability.

Does formula view work in Excel Online or Google Sheets?

Excel Online supports formula view via keyboard shortcut (Ctrl+`), similar to desktop. Google Sheets doesn't have a direct equivalent but shows formulas in the formula bar.