The Find And Replace feature in Excel is a very useful, but often overlooked tool in Excel. Obviously it can help you find information in your worksheet. But it can also help you quickly update information scattered across a worksheet by making replacements in the text, numbers, formulas, or even formatting on your spreadsheet. The easiest way to use this pair of related features is to type Ctrl+F for Find or Ctrl+H for Replace (I can’t explain why H). Or if you prefer to use the mouse the Find & Select button is at the far right end of the Home ribbon.
I’m going to focus on “Replace” instead of “Find” because the two work the same way with the obvious exception that Find only locates things and does not replace them with something else.
At its most basic, you enter the text to be located (the text you want to replace) in the Find What field and the text you want to use in its place in the Replace With field (or leave Replace With blank to simply remove text you searched for). If you want to replace every occurrence, click the Replace All button. If you want to review each instance match before replacing it, click the Find Next button. Then click Replace to or click Find Next again to move on without replacing that occurrence.
By default, the entire tab of the workbook will be searched. To limit your search to a specific area, simply highlight those cells first.
Excel looks at your formulas too, not just at text or numbers. So if you have several formulas that reference cell A5 and now you want them to reference cell C10, just enter A5 in Find What and C10 in Replace With and you can easily update all of you formulas at once.
Instead of looking for an exact string of text, you can also use wildcards when searching. Use a ? as a wildcard to a single character or a * for multiple characters. For example, searching for “c?t” would find both cat and cut. But a search for “c*t” would find court as well as cat and cut.
When you click the Options button in the Replace window you will see several ways you can refine your search.
- Format lets you add formatting requirements to either your “Find What” or “Replace With” choices. You can even find or replace just formatting without specifying any text.
- Within tells Excel to search just the current worksheet (tab) or the entire workbook. If you set it to “Sheet” you can limit it further by highlighting a group of cells. Then it will only search the selected cells.
- Search controls the order in which Excel looks for your text. Choices are by columns or by rows.
- Look In has choices for Formulas, Values, or Comments on the Find tab, but Formulas is the only choice on the Replace tab.
- Match Case turns on the option to match capitalization, not just letters.
- Match entire cell contents changes your search from replacing a match anywhere within a cell to replacing only when the cell’s entire contents matches your search text.
Next time you need to make changes in a spreadsheet, think about using Find And Replace before you waste time manually searching through your spreadsheet and typing each update.
This post is an update of a tip originally publish in March 2012.
