How to quickly toggle between formula and value with a shortcut in Excel?
Working with Excel worksheets often involves managing and reviewing numerous formulas, especially when analyzing data or troubleshooting calculations. In such cases, you may find it necessary to quickly check the formulas behind the results—maybe to verify the logic, debug an error, or audit calculations for accuracy. Normally, you would have to click each cell individually and examine its content in the formula bar, which is time-consuming and inefficient for large data ranges. Fortunately, Excel provides convenient keyboard shortcuts that allow you to instantly switch the worksheet display between showing calculated values and the underlying formulas, greatly streamlining formula audits and checks.
 |  |  |
Toggle between formula and value with shortcuts
Alternative: Show formulas via Ribbon command
Alternative: Use a formula to display another cell’s formula
Toggle between formula and value with shortcuts
The shortcut for toggling the view between formulas and their calculated values is extremely useful for quickly scanning entire worksheets without the need to select cells one at a time. This feature can make formula auditing, error correction, and instructional demonstrations much more efficient in daily Excel work.
In the Windows system, press
Ctrl + `
to switch between displaying formula results and the actual formulas in all cells on the current worksheet. The ` symbol is known as the grave accent and is typically located just above the Tab key on most keyboards. If you cannot locate this key, check your keyboard layout or refer to your device manufacturer’s documentation.
In the Mac system, press
^ + `
to achieve the same effect—immediately toggle all cells between showing formulas and their calculated values. On a Mac keyboard, the ^ refers to the Control key. Be sure to use the grave accent key and not an apostrophe. If your Mac has a different physical layout or language setting, you may need to try alternative key combinations or adjust your system preferences.
After toggling the display, you can view, compare, or troubleshoot formulas without changing any of the actual worksheet data. Press the shortcut again to return to value view. If you notice formulas not being shown or the shortcut not working as expected, check that you are on an active worksheet, and ensure that NumLock or other keyboard modifiers are not interfering with key commands.
Common use cases: This shortcut is suitable for rapidly auditing formulas before sharing spreadsheets, teaching Excel concepts, or identifying calculation errors across large datasets. However, it only affects the display; underlying formulas and values remain unchanged.
Potential limitations: The shortcut toggles formula display for the entire worksheet and cannot be limited to specific cell ranges. If you have multiple worksheets, you will need to repeat the shortcut for each sheet as needed.
Tips: If the shortcut does not respond, double-check your keyboard settings, language input modes, and ensure that macros or add-ins are not overriding default behaviors. For users who frequently switch between formula and value view, it's advisable to avoid editing cell contents while formulas are displayed to prevent accidental changes.
Troubleshooting: If formulas still do not show, make sure that formula cells do not begin with a single quote ('), which makes Excel treat them as text. To re-enable formula display, press the shortcut again. If you encounter persistent issues, consider restarting Excel or checking for updates.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Alternative: Show formulas via Ribbon command
As an alternative for users who prefer using mouse navigation or when keyboard shortcuts are unavailable, you can also display all formulas in a worksheet through Excel’s Ribbon interface. This method can help users unfamiliar with keyboard shortcuts or using devices with non-standard keyboards.
To do this, go to the Formulas tab on the Ribbon, and click Show Formulas in the Formula Auditing group. The worksheet will immediately display all cell formulas instead of their resulting values. Click the Show Formulas button again to return to value view. Please note, this command works globally for the entire active sheet and has the same effect as the shortcut key.
This approach is practical in collaborative settings or when presenting to others, as it clearly displays formula logic without risk of accidental worksheet edits.
Alternative: Use a formula to display another cell’s formula
In some scenarios, you may want to view the formula used in a specific cell without toggling the entire worksheet. Excel provides a function called FORMULATEXT to achieve this. This is especially useful when documenting your worksheet, creating an audit trail, or troubleshooting a particular calculation.
1. Enter the following formula in the cell where you want to display a cell’s formula (for example, in cell B1):
=FORMULATEXT(A1)2. Press "Enter" to confirm. The formula in cell A1 will be shown as text in cell B1. If you want to review formulas from multiple cells, drag the formula down or across to copy it to other cells. Please ensure that the referenced cell contains a formula; otherwise, you will see a #N/A error.
Remarks: If the cell being referenced is not a formula cell or if the referenced formula is too long, the result may show an error. Ensure all cells referenced by FORMULATEXT are formula cells for accurate display.
Relative Articles:
- How to use shortcut keys to insert or delete rows/columns/tabs in Excel?
- Alternative: Show formulas via Ribbon command
- Alternative: Use a formula to display another cell’s formula
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in