ThisCalculator

Everyday

Excel Select All Formula Cells: 3 Ways

Updated · Standard formula, shown below

Excel Select All Formula Cells: 3 Ways

To select all formula cells in Excel, use the Go To Special feature: press F5 or Ctrl+G, click Special, choose Formulas, and click OK. This selects every cell containing a formula in the current worksheet, letting you review, format, or delete them at once.

Why You Need to Select Formula Cells

Spreadsheets grow quickly. A workbook that starts with a few sums can end up with hundreds of formulas scattered across sheets. When you need to audit those formulas, protect them, or apply consistent formatting, hunting for each one manually is slow and error-prone. Selecting all formula cells at once gives you a clear view of where calculations live, which is the first step in checking for mistakes, locking cells, or copying logic to another sheet.

The Go To Special Method (Fastest)

Go To Special is built into Excel and works in every modern version, including Microsoft 365, Excel 2021, 2019, and older releases. Here’s the step-by-step:

  1. Select any single cell (or a range if you only want to search part of the sheet).
  2. Press F5 or Ctrl+G to open the Go To dialog.
  3. Click the Special button in the bottom-left corner.
  4. In the Go To Special dialog, choose Formulas.
  5. Under Formulas, you’ll see checkboxes for Numbers, Text, Logicals, and Errors. By default, all are checked. Keep them checked to select every formula, or uncheck some to narrow down (for example, only formulas that return numbers).
  6. Click OK.

Excel highlights all cells that contain formulas. If you started with a single cell, it selects formulas across the entire used range of the worksheet. If you selected a range first, it only looks within that range.

Using the Name Box for a Quick Selection

If you prefer not to use dialogs, the Name Box (the box to the left of the formula bar) can do the same thing with a typed command:

  1. Click into the Name Box.
  2. Type Formulas (exactly that word) and press Enter.

Excel treats Formulas as a special named range that refers to all formula cells on the sheet. This works because Excel has a built-in hidden name for that set. It’s a handy shortcut if you use it often, but note that it only works on the active sheet, and it selects all formulas regardless of the result type (numbers, text, etc.).

Selecting Formulas with VBA (For Repetitive Tasks)

If you need to select formula cells frequently, or you want to automate the process across multiple sheets, a small VBA macro can do the job. Press Alt+F11 to open the VBA editor, insert a new module, and paste this:

Sub SelectAllFormulas()
    On Error Resume Next
    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select
End Sub

Run the macro and it selects all formula cells on the active sheet. The On Error Resume Next line prevents an error if the sheet has no formulas at all. This method is useful if you want to combine it with other actions, like coloring the cells or exporting the list.

What to Do After Selecting Formula Cells

Once the cells are selected, you can perform any action that works on a multi-cell selection:

  • Review formulas: Press Ctrl+` (the backtick key) to toggle between showing values and showing formulas. This lets you scan the actual formulas in place.
  • Format them: Apply a fill color, border, or font style to make formula cells visually distinct. For example, you might give them a light yellow background.
  • Protect them: Open the Format Cells dialog (Ctrl+1), go to the Protection tab, and check “Locked.â€� Then protect the sheet to prevent users from editing formulas.
  • Delete them: Press Delete to clear all formulas at once. Be careful—this removes the calculations, not just the displayed values.
  • Trace precedents: Use the Trace Precedents tool to see which cells feed into these formulas.

Common Problems and How to Avoid Them

One frequent issue is that Go To Special only works on the active sheet. If you have formulas on multiple sheets, you must repeat the process on each sheet. Another problem: if you select a large range before using Go To Special, it only searches that range, which might miss formulas outside it. To select the entire sheet, click the triangle at the intersection of the row and column headers (or press Ctrl+A twice) before opening the dialog.

Also, remember that Go To Special selects all formula cells, including those that return errors (like #DIV/0!). If you only want to see error-producing formulas, uncheck “Numbers,� “Text,� and “Logicals� so only “Errors� is checked. That’s a quick way to find broken calculations.

Alternatives: Find & Select Menu and Conditional Formatting

Excel’s Ribbon has a built-in shortcut to the same dialog: go to the Home tab, click Find & Select (in the Editing group), and choose Go To Special. This is the same dialog as pressing F5.

If you want to permanently highlight formula cells without manually re-selecting them, you can use Conditional Formatting with a formula. Select the range you want to check, go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter:

=ISFORMULA(A1)

Set a format (like a fill color) and click OK. This dynamically highlights any cell that contains a formula. The ISFORMULA function is available in Excel 2013 and later. This approach doesn’t select the cells, but it makes them visually obvious, which is often what you actually want.

Frequently Asked Questions

Does Go To Special select formulas on hidden sheets?

No. It only works on the active sheet. If you have formulas on hidden sheets, you need to unhide those sheets first and then run Go To Special on each one.

Can I select formulas across multiple worksheets at once?

Not directly with Go To Special. You can group sheets (click the first sheet tab, hold Shift, click the last sheet tab), but Go To Special still only operates on the active sheet within that group. You’d need to repeat the process per sheet or use a VBA loop.

Why does my selection include cells that look empty?

Some cells may contain formulas that return an empty string ("") or a zero-length text. Go To Special counts them as formula cells because the cell contains a formula, even if the result is invisible. To see them, press Ctrl+` to show formulas.

What’s the difference between selecting formulas and selecting constants?

In the Go To Special dialog, “Constants� selects cells that contain values you typed directly (numbers, text, dates) but not formulas. “Formulas� selects cells that contain calculations. The two are mutually exclusive—a cell is either a constant or a formula.

Is there a keyboard shortcut to select formula cells directly?

There’s no single-key shortcut, but you can create one by adding the Go To Special command to the Quick Access Toolbar and then using Alt plus the number key that appears. Or you can assign a custom shortcut to a VBA macro.

Related

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