How to find and replace across a whole workbook
Find and Replace in Grid by Zaweba searches one sheet or every sheet, in formulas, values or notes, with Find All to list each match before you change it.
Open Find and Replace and set it to search the workbook rather than the sheet. In Grid by Zaweba, Ctrl+F opens Find and Ctrl+H opens Replace, and the search can cover the current sheet or every sheet, looking in formulas, values or notes. Find All lists every match so you can see what will change before you replace anything.
Finding something step by step
Press Ctrl+F and type what you're looking for. Choose whether to search the sheet or the whole workbook, and whether to look in formulas, values or notes. Find Next steps through the matches one by one, selecting each cell in turn and switching sheets as needed. Find All shows the full list, which is often quicker for getting a sense of how many there are and where they sit.
Choosing where to look
The choice between formulas and values matters more than it seems. Searching formulas finds what's typed in the cell, including references and function names. Searching values finds what the cell shows. If a cell's formula is =A1*1.2 and it shows 120, searching values finds 120 and searching formulas finds A1.
Searching notes looks inside the pop-up notes attached to cells, which is handy in a workbook where comments explain unusual figures.
Replacing across every sheet
Press Ctrl+H, type what to find and what to replace it with, and set the scope to the workbook. Use Find All first to check the list of matches. If every one should change, Replace All does it in one go. If only some should, step through with Find Next and Replace one at a time.
Typical jobs are renaming a product across every month's sheet, correcting a misspelt name, or changing a reference in many formulas at once, such as pointing them at a different column.
Avoiding nasty surprises
Be specific. Replacing 10 with 12 in values changes every cell containing 10, including 100, 2010 and 10.5, unless you ask for whole-cell matches. Replacing a short word in formulas may change function names too. Find All shows you exactly what would be touched.
Remember undo. A Replace All is one action you can step back from, and undo in Grid keeps 100 steps, so a mistake can be put right straight away.
For changes to names used in formulas, Name Manager is often better than Replace. Rename a defined name there and every formula using it follows.
Go To and Go To Special
Finding cells by what they are, rather than what they contain, is a different job. Go To (Ctrl+G or F5) jumps to a cell address or a name. Go To Special selects cells of a particular type, such as every formula, every constant or every blank in the selection. Select all the blanks in a column and you can fill them in one go. Select all the formulas and you can colour them, so nobody types over one by mistake.
Other ways to track things down
Trace precedents and dependents draw arrows showing which cells feed a formula and which formulas use a cell. Show Formulas displays every formula instead of its result, which makes it easy to scan a sheet for an odd one out. And AutoFilter's search box narrows a long list to matching rows without changing anything.
Find and Replace across the whole workbook is the safe way to make one change in many places. With Find All to preview and undo to fall back on, Grid by Zaweba makes it a careful job rather than a risky one.
