Skip to content
Back to all articles

How to show all formulas in Google Sheets at once: 5 simple ways

By Yurii Vasyliev7 min read
An open pocket watch revealing the gears that make it run, standing for seeing the formulas behind a spreadsheet

Learning how to show all formulas in Google Sheets at once takes about ten seconds. You flip one setting, and every cell swaps its result for the formula behind it. That makes it easy to audit a sheet you inherited, spot a typed-in number, or check a column before you share it. This guide covers five ways to do it, plus the opposite problem: a cell that shows a formula when it should show an answer.

You will see why this matters the first time you open someone else's workbook. A total that looks wrong might hide a typed number, a range that stops one row short, or a formula pasted over with a value. Seeing every formula side by side makes those problems visible. It also helps you learn, because you can read how a finished sheet works.

How to show all formulas in Google Sheets at once

The first two methods switch the whole sheet at once. The other three help when you want one formula, a formula as text, or a clearer view of what a formula points to. Pick whichever matches the job in front of you.

1. Use the View menu

Open the View menu, point to Show, and click Formulas. Every cell that holds a formula now displays the formula text instead of its result. Cells with plain numbers or words look the same as before. To switch back, repeat the same three clicks. Formulas are longer than most results, so widen a column if the text gets cut off.

2. Press the keyboard shortcut

Hold Ctrl and press the backtick key, which shares a key with the tilde (~). Both spreadsheet.dev and LiveFlow list this shortcut as the toggle, and LiveFlow says it works on Windows and Mac. Press it once to show formulas and again to hide them. Because it is a toggle, it also rescues you when you flip the view by accident and forget which setting did it.

3. Open a single formula with F2 or Shift + Enter

When you only care about one cell, select it and press F2. Ben Collins notes in his list of Google Sheets formula tips that Shift + Enter does the same job. Either key puts the cell into edit mode with the formula showing. Press Escape to leave without saving a change, or press Enter to keep an edit. That Escape habit matters, since a stray click while editing can insert a new cell reference into your formula.

4. Show a formula as text with FORMULATEXT

Sometimes you want the formula visible in the sheet permanently, for example on a notes tab that explains how a report works. The FORMULATEXT function does that. Point it at a cell that holds a formula, and it returns that formula as text in a different cell.

=FORMULATEXT(B2)

If the cell you point at holds no formula, FORMULATEXT returns an error, so use it only on formula cells. The text also updates when you edit the original formula, which keeps your notes in step with the sheet.

5. Read the colored ranges

Open any formula for editing and look at the sheet itself. Ben Collins also points out that Google Sheets gives each unique range in a formula its own color and marks the matching cells in the same color. That is the quickest way to spot a range that stops one row too soon. The function helper pane adds yellow highlighting on the argument you are in, so you always know which part of a long formula you are editing.

Magnifying glass over a row of spools, one different, showing how to show all formulas in Google Sheets at once and spot the odd cell
Scanning a column for the one entry that breaks the pattern.

Why a cell shows the formula instead of the answer

This is the reverse problem, and it trips up plenty of people. If one cell shows =SUM(A1:A10) as plain text, the Show formulas view is usually not the cause, because that setting affects every formula on the sheet. When only one cell or a few cells behave this way, look at how the cell is formatted and how the entry starts.

What you seeLikely causeFix
Every formula cell shows formula textThe Show formulas view is onOpen View, then Show, and clear the Formulas check, or press the shortcut again
One cell shows formula textThe cell is formatted as plain textSet Format, then Number, then Automatic. Select the cell, press F2, then press Enter
The entry starts with an apostrophe or a spaceSheets reads it as textDelete the extra character so the cell begins with an equals sign
The entry has no equals signIt is not a formula yetType = at the start

The plain text case catches people who format a column as text before typing in it. The cell keeps that format, so new entries stay as text. Changing the format alone does not always convert what you already typed, which is why re-entering the formula with F2 and Enter matters. CFI's guide to basic Excel formulas notes that every formula begins with an equal sign, and Google Sheets follows the same rule.

Showing formulas in Excel

Excel offers the same toggle in a different place. According to TechRepublic's guide to protecting Excel formulas, you go to the Formulas tab and click Show Formulas in the Formula Auditing group. Click it again to return to results. The shortcut is Ctrl plus the backtick key, just like in Sheets. Pressing F2 opens in-cell editing, and the formula bar shows the formula for the selected cell.

Sheet protection changes what people can see. TechRepublic says protecting a sheet does not disable Show Formulas, but cells marked Hidden keep their formulas out of the formula bar. The same article warns that protection is not a security feature, because anyone can copy data from a protected sheet into another workbook. Treat it as a guard against accidental edits.

What to look for once the formulas are visible

Scan one column at a time. A healthy column repeats the same formula pattern on every row, and only the row numbers change. A cell that breaks the pattern, such as a typed number in the middle, is the first place to check. Next, look for constants inside formulas, like a tax rate typed straight in, because those are easy to forget when the rate changes.

In Excel, our guide to trace dependents in Excel shows how to follow the arrows from a cell to the formulas that use it. For a refresher on the functions you may find, the Google Sheets cheat sheet formulas post explains common ones in plain terms. If a formula still looks like a puzzle, describe the result you want to our formula creator and compare its answer with what you found.

Finish by toggling the view back, so the next person sees results first. The routine is short: show formulas, scan one column, hide them again. It takes under a minute and often surfaces the odd typed number.

Action steps

  1. Toggle the view: Open View, then Show, then Formulas, or press Ctrl plus the backtick key, to see every formula on the sheet.
  2. Scan column by column: Look for cells that break the repeated pattern, such as a typed number in a column of formulas.
  3. Inspect one cell: Select a cell and press F2 to read its formula, then press Escape to leave without changing it.
  4. Document key formulas: Use FORMULATEXT on a notes tab to keep important formulas visible as text.
  5. Fix text-formatted cells: Set the format to Automatic, press F2, then Enter, and confirm the entry starts with an equals sign.
  6. Switch back: Turn Show formulas off so the next person sees results first.

Frequently asked questions

Does showing formulas change my data?

No. The setting only changes how cells display, switching them from values to formulas. Your formulas and results stay the same, and you can switch back at any time.

What is the shortcut to show formulas in Google Sheets?

Press Ctrl and the backtick key together, the key that shares space with the tilde. LiveFlow lists this shortcut for both Windows and Mac. Press it again to hide the formulas.

Why does one cell show a formula instead of the result?

The cell is usually formatted as plain text, or the entry starts with a space or an apostrophe instead of an equals sign. Set the format to Automatic, then select the cell, press F2, and press Enter.

How can I see the formula from one cell inside another cell?

Use the FORMULATEXT function, for example =FORMULATEXT(B2). It returns the formula in B2 as text and returns an error if B2 holds no formula.

Share this article