How to Use Find and Replace in Excel 2013

If your purpose for finding a cell with a particular entry in Excel 2013 is so that you can change it, you can automate this process by using the Replace tab on the Find and Replace dialog box. If you click Home→Find & Select→Replace or press Ctrl+H or Alt+HFDR, Excel opens the Find and Replace dialog box with the Replace tab (rather than the Find tab) selected.

On the Replace tab, enter the text or value you want to replace in the Find What text box, and then enter the replacement text or value in the Replace With text box.

When you enter replacement text, enter it exactly how you want it to appear in the cell. In other words, if you want to replace all occurrences of Jan in the worksheet with January, enter the following in the Replace With text box:

January

Make sure that you use a capital J in the Replace With text box, even though you can enter the following in the Find What text box (providing you don’t check the Match Case check box that appears only when you choose the Options button to expand the Find and Replace dialog box options):

Jan

After specifying what to replace and what to replace it with, you can have Excel replace occurrences in the worksheet on a case-by-case basis or globally. To replace all occurrences in a single operation, click the Replace All button.

image0.jpg

Be careful with global search-and-replace operations; they can really mess up a worksheet in a hurry if you inadvertently replace values, parts of formulas, or characters in titles and headings that you hadn’t intended to change. With this in mind, always follow one rule: Never undertake a global search-and-replace operation on an unsaved worksheet.

Also, verify whether the Match Entire Cell Contents check box (displayed only when you click the Options button) is selected before you begin. You can end up with many unwanted replacements if you leave this check box unselected when you really only want to replace entire cell entries (rather than matching parts in cell entries).

If you do make a mess, immediately click the Undo button on the Quick Access toolbar or press Ctrl+Z to restore the worksheet.

To see each occurrence before you replace it, click the Find Next button or press Enter. Excel selects the next cell with the text or value you enter in the Find What text box. To have the program replace the selected text, click the Replace button. To skip this occurrence, click the Find Next button to continue the search. When you finish replacing occurrences, click the Close button.

blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.