Find and replace

The find bar searches the current sheet and can replace text in every cell that matches.

Open the find bar

Use any of these:

• Press ⌘F (Ctrl+F on Windows and Linux).
• Choose Home › Editing › Find › Find and replace ⌘F.
• Choose Find and replace… from the More menu (three dots) in the title bar.

The find bar opens between the formula bar and the grid, with the cursor in the Find box.

Find

1. Type what you are looking for in Find.

The search runs as you type. The first match is selected, the number of matches appears at the right of the bar (for example 7 found), and the status bar says how many matches there are. If nothing matches, the status bar says No cell contains followed by your text.

2. Move between matches:
   • Press Enter in the Find box, or click the down arrow (Next match), to go to the next match.
   • Click the up arrow (Previous match) to go back.

The status bar shows where you are — for example Match 3 of 7 for “north”. Moving past the last match wraps round to the first.

What the search looks at

• Matches are found anywhere inside a cell: nor finds North and Northern.
• Upper and lower case are treated the same.
• Each cell’s stored value is searched, and so is its formula. A search for SUM finds every cell whose formula uses SUM.
• The stored value is not the same as what the cell displays. A number formatted as £1,200.00 is stored as 1200, so search for 1200.
• Only the current sheet is searched. Matches are visited row by row, from the top.

Replace

1. Type the text to find in Find.
2. Type the new text in Replace with.
3. Click Replace all.

Every occurrence of the text in every matching cell on the current sheet is replaced. The status bar says Replaced followed by the number of cells, or Nothing to replace.

• Upper and lower case are treated the same when finding the text.
• Cells that hold formulas are never changed by Replace all, even when their formula contains the text.
• A cell whose new text reads as a number becomes a number. For example, replacing n/a with 0 in a cell that held n/a leaves the number 0.
• Replace all is one undo step. Press ⌘Z (Ctrl+Z) to put everything back.

Close the find bar

Click the × at the right of the bar (Close find), or press Esc while the grid has focus.

Find by kind of cell

To select every formula, constant, blank, error, note, validated cell or conditionally formatted cell, use Go to Special. See Selecting and navigating.