Find and Replace Multiple Values at Once

When you have big data in a Workbook or Worksheet, and you observe that there are some mistakes and the user wishes to remove all values at once which are wrong fields. To do this, we use “Find and Replace” in Excel.

In this article, you are going to learn how to use Find and Replace certain values, cell references, characters, Format, etc in Excel.

Key Topics which will you cover here are:

About Find and Replace

Find: To search any word, wildcard, symbol, formulas in data not only the active sheet but also in other sheets. The shortcut to finding in Excel is Ctrl+F

Replace: To substitute any values, symbols, formulas, etc. in the data of the worksheet which is found. The shortcut to replace in Excel is Ctrl+H

As you click the Options button, Excel provides many options, in case you require more than simply find and replace, check settings: (Image Below)

  1. Match Case: If we click Match Case, the text which you type in Find must match exactly i.e. case sensitive.
  2. Match Entire Cell Contents: If this is selected, a match occurs if the cell contains only the characters that have an exact match and nothing else. For searching wildcard characters, an exact match is not required.
  3. Look In: It has three options: Values, Formulas, and Comments. The Formulas option only observes at the text that makes up the formula or the contents of the cell if there is no formula. The Values option looks at the cell value and the results, not the text, of the formula.
  4. Format: if there is a need to change the format (color) click the cell, Range to replace another Format. This drop-down has three options Format from the cell, Format, and clears the Format.
  5. Sheet: this has two options Find and Replace in a sheet or Workbook.
  6. Replace, Replace All, Find Next, Find All: If you want to replace only one cell which you select, Click on Replace, and if you want to change all similar value click on Replace All. The same is for Find Next and Find All, to find one value than other value press Find next and to show all values together click Find All.
Find and Replace Dialog box with options.
Find and Replace Dialog box with options.

Where is Find and Replace in Excel?

    • Open an Excel workbook.
    • Under the Home tab
    • In editing, group click on Find and Select
    • Select Find and Replace option
    • A dialogue box appears
    • Enter your Query

Search and Replace Text

When you want to remove and replace text with exact name in a worksheet. Like here by mistake you have written a wrong Name or SalesRep, so to change, we will find “Smith” and replace it with “Vinnie”.

Search and Replace Name in a Column.
Search and Replace Name in a Column.

Replace Text

    • Under the Editing group in Excel, click on Find and Select.
    • Chose option Replace
    • Find what: text (Smith)
    • Click on Find All
    • It shows the number of occurrences
    • Then click on Replace
    • Replace with: text (Vinnie) to change
    • Click OK

Note- Same way you can Find and Replace any number in Excel.

It’s not necessary we use find only to replace, sometimes we use to remove certain things in the spreadsheet. 

Use Wildcard character To Replace

Sometimes we made spelling mistakes between two characters to make it correct quickly in a range use wildcard. Here we have written “Switzerland” to replace one character we use “?” (wildcard). Learn more about How to use Wildcards in Excel?

To change one letter in a Text.
To change one letter in a Text.

Add & Remove ? character 

    • Under the Editing group, click on Find and Select
    • Chose option Replace
    • In Find what: type (z? r)
    • Click on Find All
    • It shows all occurrences
    • Then click on Replace
    • Replace with: type (zer) to change
    • Click OK

Note: Like here you have changed only one character with ? If you have to change multiple characters use * in Excel.

Replace Cell Reference in a Formula

To change the entire cells used in the formula, Suppose that you have selected the wrong reference in a Formula. if we want the total of Achieve (D3:D22) but mistakenly you select the sum of Goal (C3:C22) to change it at once.

To Substitute cell Reference in a Formula.
To Substitute cell Reference in a Formula.

Replace many cell reference at once

    • Open Sheet to Change Cell Reference & Formula
    • Under the Editing group, click on Find and Select
    • Click the Formula cell
    • Chose option Replace
    • In Find what: type cell Reference (C3:C22).
    • Then click on Replace
    • Replace with: type cell Reference (D3:D22) to change.
    • Click OK

Note: Do not forget to select Cell or Range in which you type your Formula before Find and Replace.

Find and Change Formula

In case you want to change part of the formula but cell reference remains the same. Like here we have the addition of Achieve and for this, we will change the formula to average.

To Replace Formula in a Cell
To Replace Formula in a Cell

To Edit many Formula at once

    • Open sheet Change Cell Reference & Formula
    • Under the Editing group, click on Find and Select
    • Select the cell to find
    • Chose option Replace
    • In Find what: type function =SUM()
    • Then click on Replace
    • In Replace with: type function =AVERAGE() to change
    • Click OK

Note: Go through Examples based on the condition of SUM and AVERAGE function.

Find and Substitute Breaks

Sometimes you have data in a column add with break maybe you have copied from another file with a Break or Wrap and looking to substitute it with space so that all values show in a cell we change to break with space.

Remove Breaks in cells
Remove Breaks in cells

To Remove Break in cells

    • Under the Editing group, click on Find and Select.
    • Select the cell to find.
    • Chose option Replace
    • In Find what: type ctrl+j
    • Then click on Replace
    • Replace with: leave one space.
    • Click OK

Note: Please review page how breaks applied in Print.

    • To apply Breaks in a cell click on Alt+Enter

Replace or Find cells with a Format

To change the preview of format i.e. color of the background cell, we use Find and Replace in Excel. So, here the user highlights all Quantity which is less than 20 with Green color but to add a new color to change the Preview in the cells.

Replace colour Format in Excel.
Replace colour Format in Excel.

Find and Replace Format

    • Open Worksheet format
    • Under the Editing group, click on Find and Select
    • Select the Range
    • Chose option Replace
    • Click on Options
    • In Find what: Click on a format select the color
    • Then click on Replace.
    • In Replace with: Click on format choose a color to replace.
    • Click OK

Remember: Clear the format when work is done.

Note: Learn Count Cells with Find and Replace without Vba .

Please check how the format is done in Conditional Formatting detailed for coloring or specific format to Excel cells.