Accessing Find and Replace in Excel

The Find/Replace feature can be activated by selecting the Home tab, Editing (group), clicking the button labeled “Find & Select”, then clicking “Find…” or “Replace…

To quickly access Find and Replace in Excel, use keyboard shortcuts:

  • Find: Ctrl + F
  • Replace: Ctrl + H

These shortcuts help you launch these features instantly without navigating through menus.

How to Search in Excel

To find specific characters, text, numbers, or dates in Excel, you can search within a selected range, an entire worksheet, or a whole workbook.

  • Select Cells: First, choose the cells you want to search through. If you want to search the whole worksheet, just click on any cell.
  • Open the Search Box: Press Ctrl + F on your keyboard, or go to the Home tab, click on Editing, then Find & Select, and select Find.
  • Start Searching: Type what you’re searching for in the ‘Find what’ box. Click Find Next to move through the findings one at a time, or click Find All to see a list of all matches.
how to search in excel

Enhance your search with Wildcards

Wildcards are special symbols that help you find words or phrases even if you’re not sure of the exact characters. Here’s how you can use them:

  • ? (Question mark): Use this when you’re unsure about one letter in the word. ‘ha?e’ finds both ‘hate’ and ‘have’.
  • * (Asterisk): Use this when you’re unsure of several letters. For example, use ‘smi*’ to search for ‘smile’, ‘smirk’, or ‘smitten’.
  • ~ (Tilde): Use this before any wildcard character (like ? or *) if you want to find the actual symbol in your text. For example, ‘do~?’ finds ‘do?’.

These wildcards can make your search more flexible, especially when you’re not sure of the exact text you need to find.

Assuming you are already familiar with the basic operational use of the Find and Replace feature, let’s dive a bit deeper into the lesser known options of the tool.

Additional Search Options

To access more search capabilities, click the “Options >>” button. Here’s what you can adjust:

Within:

  • Active Sheet: Search only the sheet you are currently viewing. Ideal for focusing on a single dataset.
  • Workbook: Searches all sheets in your file. Useful for comprehensive reviews across multiple datasets.
How to Search in Excel

Search Direction:

  • Rows: Best for vertical data arrangements.
  • Columns: Useful for horizontal data layouts.

Look In:

  • Formulas: Finds text in formulas, like searching for “SUM” to see where sums are calculated.
  • Values: Searches displayed results, such as finding all cells showing a value of “100.”
  • Comments: Locates text in cell comments, helpful for tracking specific notes

Match Options:

  • Match case: Ensures the case of letters matches exactly, distinguishing between “Apple” and “apple.”
  • Match entire cell contents: Finds cells that exactly match your search, excluding cells with additional text.

How to Use ‘Find All’ in Excel

‘Find All’ in Excel is a powerful tool for locating all instances of your search criteria across your worksheet.

  • Initiate a Search: After entering your search term, choose the ‘Find All’ option. Excel will display a list of all cells that match your criteria.
Using the 'Find All' Feature in Excel
  • Navigate Results: Click any listed entry to move your cursor directly to that cell in the worksheet.
Using the 'Find All' Feature in Excel
  • Select Multiple Results: To select multiple specific cells from the ‘Find All’ list, hold down the Ctrl key and click on each item you want to include.
Using the 'Find All' Feature in Excel
  • Select all results: Alternatively, click on any item and then press Ctrl+A
Find and replace in Excel
  • Close the Find Dialog: Once you close the dialog box, the selected cells remain highlighted
  • Bulk Modifications: You can now make changes to all selected cells at once, such as changing the fill color.

How to Use Replace in Excel

The Replace function in Excel allows you to swap one value for another. You can do this within a selected range, a whole worksheet, or the whole workbook.

Steps to Replace Values:

  • Select Cells: Choose the range of cells where you need to replace text or numbers. To work across the whole worksheet, simply click any cell.
  • Open Replace Dialog: Use the shortcut Ctrl + H. Or, go to the Home tab, click on Editing, then Find & Select, and select Replace.
  • Enter Values:
    In the ‘Find what’ box, type the value you want to find.
    In the ‘Replace with’ box, enter the new value. To replace with nothing, leave this box blank.
  • Perform Replacement:
    Click ‘Replace’ to change one occurrence at a time.
    Click ‘Replace All’ to change all occurrences at once.
Replace in Excel
  • Undo Mistakes: If the replacement wasn’t as expected, press Ctrl + Z or click the Undo button to revert the changes.

Find and Replace Cell References

Find and Replace Formatting in Excel

You can easily update text and apply new formatting simultaneously using Find and Replace in Excel. Here’s how:

  • Start Replace: Press Ctrl + H to open the Replace tab in the Find and Replace dialog box.
  • Set Search Criteria: Enter the text you want to find in the “Find what” field and the replacement text in the “Replace with” field.
Find and Replace Formatting in Excel
  • Apply Formatting
    • Click the small down arrow next to “Format…” (located to the far right of the “Replace with” field) and select “Format…” again
  • This opens the Replace Format dialog box, which offers the same options as the Format Cells dialog box.
  • Choose the formatting options you want to apply, such as font style, color, size, number format, alignment, border, or cell color.
  • Execute Replacement: Click “Replace All” to update all found entries with the new text and formatting.
Find and Replace Formatting in Excel

How to Find and Replace Cell References in Excel

When working with complex spreadsheets, manually updating each formula can be time-consuming. With Find and Replace in Excel you can easily update cell references across your formulas.

Here’s how to use it:

  • Select the Range: Highlight the cells containing the formulas you need to update. To modify the entire worksheet, select all cells.
  • Open Find and Replace: Navigate to Home > Find & Select > Replace, or use the shortcut Ctrl + H.
  • Configure the Replacement:
    • In the Find and Replace dialog, enter A4 in the “Find what” field (the old reference).
    • Enter A15 in the “Replace with” field (the new reference).
  • Execute the Change: Click ‘Replace All’ to update all instances of the reference within the selected range.
  • Repeat for Other References: If you have more cell references to update, repeat the replacement process

Important Considerations:

  • This action replaces all occurrences of the specified reference within the formulas. If a reference appears many times in a formula, each will be replaced.
  • Specify the exact cell references to avoid mistakes.

This method provides a systematic approach to updating multiple formulas, saving significant time and reducing the potential for errors.

Find All Cells Containing Formulas in Excel

Find and Replace in Excel can search for specific characters that appear directly within the formulas themselves, such as “SUM” in =SUM(A1:A10). It cannot be used to search all cells that contain formulas. This means you cannot tell Excel to highlight every cell doing a calculation. You must specify what part of the formula to look for.

Fortunately, there is another feature in Excel that can do that: ‘Go to Special

Here’s a quick guide:

  • Select the Search Area: Highlight the cells you want to check or click any cell to search the whole sheet.
  • Open Go to Special: Click “Home,” then “Find & Select,” and choose “Go To Special.” Alternatively, press F5 and select “Special…” from the dialog box.
  • Specify What to Find: In the dialog box, select “Formulas.” You can refine your search by checking options for:
    • Numbers: To find formulas that result in numeric outcomes including dates.
    • Text: Search for formulas that return text values.
    • Logicals (TRUE/FALSE): For Boolean true/false outcomes.
    • Errors: Find cells with formulas that result in errors like #REF! or #N/A.
  • Execute the Search: Click “OK.” Excel will highlight cells with formulas.
  • If no formulas are found, you’ll see a notification.

💡 For a quick overview of all cells that contain formulas, regardless of type or result, simply choose “Formulas” from the “Go to Special” menu.

How to Replace One Cell Color with Another in Excel

Changing cell colors using Excel’s Find and Replace tool can be straightforward. Here’s how to replace, for example, all light blue cells with light green:

  • Open the Replace Dialog: Press Ctrl + H to open the Find and Replace dialog. Clear any text in the “Find what” and “Replace with” fields.
Find and Replace Formatting in Excel
  • Select Original Color:
    • Click the small down arrow next to “Format”.
    • Choose “Choose Format From Cell…” and click on a cell that has the light blue color you want to change.
Find and Replace Formatting in Excel
  • Set New Color:
    • Click the down arrow next to “Format” under “Replace with”.
    • In the Replace Format dialog, go to the Fill tab and choose a light green color or use “More Colors…” to customize.
  • Execute Replacement: Click “Replace All” to change all matching cells across the current worksheet.
Find and Replace Formatting in Excel

Troubleshooting:

Sometimes, not all cells might update as expected after using the Replace Color feature. This usually happens because the replacement is affected by other formatting settings besides color.

Here’s how to fix this:

In order to select cells based on their fill color and not any of the other cell’s visual attributes, we need to search for the color only.

An easy way to determine the original search color is to select an existing cell with the light blue fill color and select the down arrow next to the “Fill Color” button and select “More Colors…”.

In the Colors dialog box, select the “Custom” tab and record the values located in the “Red”, “Green”, and “Blue” fields.

Cancel the dialog box and return to the “Replace” dialog box (Ctrl + H).

Because the Replace feature retains the previous selections, we need to clear the format options from the “Find what” option. Using the “Find what” option, click the small down arrow to the right of the “Format” option and select “Clear Find Format”.

Find and Replace in Excel

Click the small down arrow to the right of the “Format” option and select “Format”.

Select the Fill tab and then click the “More Colors” button to access the fill color pallet.

On the “Custom” tab, enter the same “Red”, “Green”, “Blue” code number recorded from the earlier step.

Because we wish to perform this modification on all sheets in the workbook, change the “Within” option to “Workbook” and click “Replace All”.

Find and Replace in Excel

Every cell on every sheet in the workbook that possessed the original light blue fill color has been updated with the new light green fill color.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master how to use Find and Replace in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.