
Occasionally, you'll have a word or number you need to find somewhere in your spreadsheet, but you're not sure where it is. Excel’s Find and Replace commands scan a worksheet for labels and values with just a few clicks of the mouse.
Find Text
The Find feature makes it very easy to find specific words and values in a worksheet.
- Click the Find & Select button on the Home tab.
- Select Find.
Press Ctrl + F.
The Find tab of the Find and Replace dialog box appears.
- Type the text you want to find in the Find what box.
- Click Find Next.
Excel jumps to the first occurrence of the word, phrase, or value that you entered.
- Click the Find Next button again to move on to other occurrences.
Click Find All to find the cell location of all instances at once.
- Click Close when you're finished.
Replace Text
Now, if you have a word that appears throughout a spreadsheet and needs to be updated, don't update it cell by cell, use Replace instead.
- Click the Find & Select button on the Home tab.
- Select Replace.
Press Ctrl + H.
The Replace tab of the Find and Replace dialog box appears.
- Type the text you want to replace in the Find what box.
- Type the replacement text in the Replace with box.
- Click the Find Next button.
The first instance is selected.
- Click any of the following:
- Replace All: Replace every occurrence of the item.
- Replace: Replace the selected item.
- Find All: Find all instances of the item.
- Find Next: Find the next instance of the item.
- Click Close when you're finished.
Advanced Find and Replace
You can use Excel’s search options to change how Excel searches the workbook.
- Click the Find & Select button.
- Select Find or Replace.
- Click the Options button.
Additional options appear, as described in following table.
- Complete the search / replace as you normally would.
- Select the desired advanced Find and Replace options.
Advanced Find and Replace Options
Find Option | Description |
---|---|
Within | Choose whether to search within just the current sheet or the entire workbook. |
Search | Search by rows (left to right, then top to bottom) or columns (top to bottom, then left to right). |
Look in | Specify which kinds of data to search in, such as formulas, values, or comments. |
Match case | Searches exactly as text is typed in the text box. |
Match entire cell contents | Searches only for cells that match the contents in the text box entirely. Parts of phrases or words are not included. |
Format | Searches for and replaces text, including character and paragraph formats, special characters, document elements, styles, and highlighting. |
FREE Quick Reference
Click to DownloadFree to distribute with our compliments; we hope you will consider our paid training.