When you click on a cell in Excel, the formula bar normally displays the formula you entered, such as =SUM(A1:A10). If the formula bar instead shows the calculated result, like 150, you cannot see or edit the formula directly. This problem usually happens because Excel is set to show the final value rather than the underlying formula, or because the cell formatting forces a text display. This article explains the exact causes and provides step-by-step fixes to restore the formula view.
Key Takeaways: Restoring the Formula Bar to Show Formulas
- Formulas > Show Formulas: Toggles between showing formulas and results in the formula bar and cells.
- File > Options > Advanced > Display options for this worksheet: Controls whether formulas or results appear in the formula bar.
- Ctrl + ` (grave accent): Keyboard shortcut to instantly switch between formula and result view.
Why the Formula Bar Shows Results Instead of Formulas
Excel has two display modes for formulas. In the default mode, the formula bar shows the formula you typed, and the cell shows the calculated result. When you toggle to formula view, both the formula bar and the cell display the formula text. The formula bar shows a result instead of a formula because Excel is currently in result display mode. This can happen if you accidentally pressed the Ctrl + ` shortcut, or if a setting in Excel Options was changed to hide formulas. The problem is not a corruption or error, but a simple display toggle that is easy to reverse.
Steps to Make the Formula Bar Show Formulas Again
Use the method below to switch the formula bar back to showing formulas. There are three ways to do this, and any one will work.
- Use the Show Formulas button on the Ribbon
Open your workbook. Go to the Formulas tab on the Ribbon. In the Formula Auditing group, click the Show Formulas button. When the button is highlighted, formulas appear in both the formula bar and cells. Click it again to return to result display. - Press the keyboard shortcut Ctrl + ` (grave accent)
Press Ctrl + ` on your keyboard. The grave accent key is located above the Tab key and to the left of the 1 key on most keyboards. Each press toggles between showing formulas and showing results. If the formula bar still shows a result, press the shortcut again. - Check Excel Options for the worksheet setting
Click File > Options > Advanced. Scroll down to the Display options for this worksheet section. Make sure the Show formulas in cells instead of their calculated results checkbox is checked if you want formulas visible. If it is unchecked, the formula bar will show results. This setting applies to the active worksheet only. To apply it to all worksheets, select each sheet tab before opening Options.
If the Formula Bar Still Shows Results After the Main Fix
If you followed the steps above and the formula bar still displays the result, one of the following situations is likely the cause.
Cell is formatted as Text
When a cell is formatted as Text, Excel treats the formula as a literal string. The formula bar shows the formula text, but the cell shows the formula text as well, not the result. This is the opposite problem, but it can be confusing. To fix it, select the cell, go to the Home tab, and in the Number group, change the format from Text to General. Then press F2 to enter edit mode and press Enter to recalculate.
Formula is preceded by an apostrophe
If you type an apostrophe before the equals sign, for example '=SUM(A1:A10), Excel treats the entry as text. The formula bar shows the formula text, but no calculation occurs. Remove the apostrophe from the formula bar, then press Enter.
Worksheet is protected or shared
If the worksheet is protected, you might not be able to see formulas in the formula bar. Go to Review > Unprotect Sheet. If you do not know the password, ask the workbook owner. For shared workbooks, formulas are visible by default, but the setting can be changed. Check Review > Share Workbook for any restrictions.
Show Formulas Mode vs Formula Bar Display: Comparison
| Item | Show Formulas Mode (On) | Show Formulas Mode (Off) |
|---|---|---|
| What appears in the formula bar | The formula text, e.g., =SUM(A1:A10) | The calculated result, e.g., 150 |
| What appears in the cell | The formula text | The calculated result |
| How to toggle | Formulas tab > Show Formulas, or Ctrl + ` | Same key or button toggles back |
| Applies to | Entire worksheet | Entire worksheet |
| Effect on printing | Formulas print instead of results | Results print |
You can now switch between formula view and result view using the Ribbon button, the Ctrl + ` shortcut, or the Options dialog. If a cell is formatted as Text or has a preceding apostrophe, fix those issues separately. For quick troubleshooting, press Ctrl + ` first, then check cell formatting if the problem persists. This approach resolves the display issue in seconds.