keepcalculator

06 Numbers and Powers

Toggle Formulas On and Off: How It Works

Card No. 06·8641 · Updated · Standard formula, shown below

Average pace 5.00 min/km
Average speed
12.00 km/h
Result
Finish time
50 min
You entered
Distance
10 km
You entered
You enter
Finish time, Distance
You get
Average pace, Average speed
Rounding
2 dp

Yes, you can toggle formulas on and off in spreadsheet software like Microsoft Excel and Google Sheets. This is done with a keyboard shortcut or a menu command that switches the display between the calculated results and the underlying formulas, or turns automatic calculation on and off. This article explains both toggles, how to use them, and why they matter.

Formula

The calculator above computes your average pace and speed for a run or ride. The formulas are:

  • Pace = Finish time (min) ÷ Distance (km)
  • Speed = Distance (km) ÷ (Finish time (min) ÷ 60)

Where:

  • t = finish time in minutes
  • d = distance in kilometers
  • Pace is in minutes per kilometer
  • Speed is in kilometers per hour

Worked Example

Using the default values (50 minutes, 10 km):

  1. Pace = 50 ÷ 10 = 5.00 min/km
  2. Speed = 10 ÷ (50 ÷ 60) = 10 ÷ 0.8333 = 12.00 km/h

The calculator displays 5.00 min/km and 12.00 km/h.

What Does “Toggle Formulas� Mean?

In spreadsheets, a formula is an expression that calculates a value, like =A1+B1. Normally, you see the result (e.g., 15). Toggling formulas means switching the view to show the formula text itself instead of the result. This is useful for auditing, debugging, or teaching. The toggle is a display setting—it does not change the data or the calculations.

How to Toggle Formulas in Excel and Google Sheets

The exact method depends on your software:

  • Microsoft Excel (Windows): Press Ctrl + ` (grave accent, usually above Tab). Alternatively, go to the Formulas tab and click Show Formulas in the Formula Auditing group.
  • Microsoft Excel (Mac): Press Control + ` (or Cmd + ` on some versions). The menu path is the same.
  • Google Sheets: There is no direct keyboard shortcut. Use the menu: View → Show → Formulas. Or press Ctrl + ~ (tilde) on Windows, Cmd + ~ on Mac.
  • LibreOffice Calc: Use View → Show Formula or the shortcut Ctrl + F1.

When you toggle on, every cell that contains a formula shows its text (e.g., =SUM(A1:A10)). Toggle off to return to results.

Toggle Automatic Calculation vs. Show Formulas

There is a second, related toggle: automatic vs. manual calculation. This controls when formulas recalculate. When set to manual, formulas only update when you press F9 (Excel) or Cmd + R (Mac). This is useful for large workbooks that slow down with every edit. To change it in Excel: Formulas → Calculation Options → Manual. In Google Sheets: File → Settings → Calculation → On change and every minute or On change.

These are different toggles: “Show Formulas� changes what you see; “Manual Calculation� changes when formulas run.

Why Toggle Formulas On?

People toggle formulas on for several reasons:

  • Auditing: Check that formulas reference the correct cells, especially in complex models.
  • Debugging: See why a result looks wrong—maybe a formula is missing a range or has a typo.
  • Teaching: Show students the structure behind a spreadsheet.
  • Printing: Print a formula view for documentation.

For example, if cell C1 shows 100 but you expected 150, toggling formulas might reveal =A1+B1 when it should be =A1*B1.

Common Mistakes and Misconceptions

One common mistake is confusing the “Show Formulas� toggle with “Manual Calculation.� They are separate features. Another is forgetting that toggling formulas on does not change the values—it only changes the display. Also, when you toggle formulas on, columns may widen because formulas are longer than results; that is normal.

Another misconception: some users think toggling formulas off deletes them. It does not. The formulas remain in the cells; only the display changes.

Limitations of the Toggle

The toggle works on the entire sheet, not individual cells. You cannot show formulas in only one range. Also, in Google Sheets, the keyboard shortcut may conflict with browser shortcuts; if Ctrl + ~ does not work, use the View menu. In Excel, the grave accent key may be difficult to find on some keyboards—look above the Tab key on US layouts.

Frequently Asked Questions

Why does my Excel show formulas instead of results?

You likely pressed Ctrl + ` accidentally. Press it again to toggle back. If that does not work, go to Formulas → Show Formulas and click it off.

Can I toggle formulas for a single cell?

No, the toggle is global. To see a single formula, click the cell and look at the formula bar. You can also use the FORMULATEXT() function in Excel to display a formula in another cell.

Does toggling formulas affect calculation speed?

No. Showing formulas is just a display change. Calculation speed is affected by the calculation mode (automatic vs. manual), not by the display toggle.

What is the shortcut for Google Sheets?

On Windows, Ctrl + ~; on Mac, Cmd + ~. If it does not work, use View → Show → Formulas.

How do I turn off manual calculation?

In Excel, go to Formulas → Calculation Options → Automatic. In Google Sheets, File → Settings → Calculation → On change and every minute.

Will toggling formulas on change my data?

No. It only changes the view. Your data and formulas remain exactly as they were.

Related

Formula used
in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →