The Find And Replace feature in Excel is a very useful, but often overlooked tool in Excel. It can help you quickly update information scattered across a worksheet by making replacements in the text, numbers, formulas, or even formatting on your spreadsheet. In Excel 2007 and 2010 you get to Replace by clicking the Find & Select button is at the far right end of the Home ribbon. In Excel 2003 or older, Replace is in the Edit menu. Or, in any version of Excel, just press ctrl+H on your keyboard.
At its most basic, you enter the text you want to have replaced in the Find What field and the text you want to use in its place in the Replace With field. If you want to replace every occurance, 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 occurance.
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 Wtih 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 several choices 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.