Find Values in Cells

Find Values in Cells searches your spreadsheet for values, formulas, links, notes, or errors. You can then highlight, replace, format, delete rows, or copy the matches.

How to use Find Values in Cells#

1. Open the tool

Open the sidebar from Extensions > Text To Table Converter > ⚡ Launch Sidebar. On the home screen, under Sheets tools, choose Sheets Mega Tools, then choose Find Values in Cells.

You can also use Extensions > Text To Table Converter > Sheets Mega Tools to open the Sheets Mega Tools screen directly.

2. Type what to find

Enter the text in Find what.

3. Set the options
   • Exact phrase (otherwise any word): when checked, the whole cell must match your text. When unchecked, a cell matches if it contains any of the words you typed.
   • Case sensitive: off by default.

4. Choose the search scope
   • Selection
   • Current sheet (default)
   • All sheets

5. Choose what to search in
   • Values (on by default)
   • Formulas (on by default)
   • Links
   • Notes
   • Errors

6. Click Find

The results list shows the number of matches. Each row has a checkbox, the cell address (such as Sheet1!A1), and a type (Value, Link, Formula, Note, or Error). Click a row to jump to that cell.

7. Choose an action

Open the arrow next to an action to see its options, then click the action.

Actions#

• Highlight: color the selected cells. Choose Single color for all results, or Multi-color by value groups to give each distinct value its own color between a min and max color.
• Replace: type the new text in Replace selected with. The whole cell is overwritten with the replacement, not only the matched text. This includes cells that contain formulas.
• Format: set a Background color and a Text color for the selected cells.
• Delete rows: delete the rows that contain the selected matches. Under Keep occurrence choose Keep first result row (default) or Keep last result row. Create backup saves a backup sheet first. You must confirm, and the deletion cannot be undone.
• Copy: copy the selected matches to the clipboard. Under Copy format choose Cell values only, Sheet position and cell values (default), or Positions only.

Defaults#

• Search scope: Current sheet
• Search in: Values and Formulas
• Exact phrase and Case sensitive: off

Limits#

• A search scans up to 100,000 cells. A larger range stops with "This range is too large. Narrow the selection and try again."
• A search returns up to 2,000 matches. If there are more, you get a "Too many results" error. Narrow the range or the search text.
• No warning appears before a search runs.

Use Cases#

• Highlight every occurrence of a value
• Replace outdated product codes or names
• Delete rows that contain a specific error
• Search formulas that use a certain function or reference