Find and Replace in Excel (A Complete Guideline)

Finding and replacing data in Excel means Excel will find data first and then replace it with another one. It can be a single character or even multiple characters and strings. You can use Excel to find data only; replacement is not a mandatory thing.

In this article, you will learn how to find and replace various types of data in Excel.

For example, we have a dataset of some projects and their status. Projects with the ‘Planning’ status have started. So, we will find the word ‘Planning’ in the dataset and replace it with the word ‘In Progress’. The whole process is shown in the video below.

In this blog post, you will learn how to find and replace data in Excel-

• Using the Find and Replace tool
• Using the Go to Special feature for some special cases
• Applying the advanced options of the Find and Replace feature
• With keyboard shortcuts
• With the help of wildcards
• Using Excel functions
• With a VBA code

Here, I have used the Microsoft 365 version of Excel to make this article. You can use any version of Excel to get the Find and Replace dialog box.

We have also used some functions to find and replace data in Excel. You will find the REPLACE, SUBSTITUTE, and FIND functions in Excel versions from 2007, but the XLOOKUP function is not available in any older version than 2021.

⏷Open Find and Replace Dialog Box in Excel
⏷Find Data in Excel
    ⏵Find Text String
    ⏵Find a Cell with Specific Formula
⏷Replace Data in Excel
    ⏵Replace One Value with Another
    ⏵Replacing Number to Blank Cell
    ⏵Find and Replace Cell Color
    ⏵Replace Cell References of Formula
    ⏵Replace Data with Specific Number Format
    ⏵Replace Line Breaks
⏷Work with Advanced Find and Replace
    ⏵Find Exact Match
    ⏵Find Comment
    ⏵Find Values in Multiple Excel Files
⏷Find and Replace Data Using Wildcards
⏷Find and Replace Special Characters
    ⏵Find and Replace Asterisk
    ⏵Removing Cell Values Within Parentheses
⏷Find and Replace with Formulas
⏷Find and Replace Notes in Excel 365 with VBA

How to Open Find and Replace Dialog Box in Excel

You can open the Find and Replace dialog box in two ways.

1. Using the Find & Select menu under the Editing group of the Top ribbon.
2. Using keyboard shortcuts CTRL+F to find data and CTRL+H to replace data.

1. Using Top Ribbon

• First, go to the Home tab >>  click on the Find & Replace option.
• From the drop-down list >> select the Find option.

• Then the “Find and Replace” dialog box will appear.

This is how you can open the Find and Replace dialog box.

2. Using Keyboard Shortcuts

Alternatively, you can use keyboard shortcuts to open the Excel Find and Replace dialog box.

• For this, select any cell of the worksheet and press CTRL+F on your keyboard. The Find and Replace dialog box will appear.
• Type what you want to find in the ‘Find what’ box and select Find Next to get the result in the worksheet.

Click on the image to get a detailed view

If you want to go to the Replace option directly, press CTRL+H on your keyboard. Type what you need to find in the ‘Find what’ box. After that, insert the data in the ‘Replace what’ box to replace it with.

Note: By default, the Find and Replace feature is case-insensitive, which means it will find and replace text regardless of the case. However, you can choose the “Match case” option to make it case-sensitive.

How to Fix When Find and Replace Feature in Excel Does Not Work?

There are 6 solutions to fix the problem of Find and Replace feature not working.

1. Ensure cell selection: you must have some value with the finding criteria in your dataset.
2. Unmark Match entire cell contents if the Find and Replace feature is not working.
3. Remove the filter for the proper use of the Find and Replace feature.
4. Delete unwanted spaces in Excel if Find and Replace does not work.
5. Unprotect the sheet to use the Find and Replace tool.
6. You must repair the corrupted Excel worksheet.

How to Find Data in Excel

To find data in Excel, you can use the options of the Find & Select feature.

Now I will show you 2 examples of how to find any text and a cell with a specific formula with proper steps. Here, we will use the following dataset to explain the examples:

1. Find Text String in Active Excel Sheet

To find any specific text string in a worksheet in Excel, put the intended data in the Find What box and press the Find All button in the Find and Replace dialog box. By default, it works on the currently active worksheet. See below.

• Open the Find and Replace dialog box by pressing CTRL+F.
• Then, insert the target text (e.g. Optimization) in the Find what box >> press the Find All button.

After pressing the Find All button, you will get all the references here.

How to Select and Highlight These Texts

Now we want to select and highlight the data that we found.

• To do that, press CTRL+A to select all these cells in the Find and Replace window.
• Then, from the Home tab >> go to Font group >> from Fill color drop down, choose any preferable color to highlight these texts.

Click on the image to get a detailed view

2. Find a Cell with Specific Formula

In this section, we will find a cell that contains a specific Excel formula using the Go To Special feature. For example, from the dataset, we want to find a formula that returns the sum of the total cost.

• To do so, from Home tab >> Editing group >> Find & Select >> Go To Special.

• As a result, you will see a dialog box named Go To Special.
• Now, select Formulas >> check only the Numbers box >> press OK.

• After searching, the cursor will move to the E16 cell automatically, where the SUM formula is used.

This SUM function returns a numeric value (26,600) as output.

How to Replace Data in Excel

To replace data in a worksheet in Excel, you need to give the original data (which will be replaced) in the Find what box and then provide the target data in the Replace with box. Now, press the Replace All button in the Find and Replace box. Then, it will replace all the found data with replaced data within the active worksheet.

Now I will show you 6 examples of how to replace different types of data with proper steps.

1. Replace One Value with Another

There is a Status column in our dataset. This column defines the current status of each project. Let’s assume there are no projects in the planning stage; all are running. So, I want to change the value “Planning” to “In Progress” in the Status column.

• Press CTRL+H from the keyboard to get the Find and Replace dialog box. This will take you to the Replace segment.
• Now, write Planning in the Find what box >> then write In Progress in the Replace with box >> press Replace All.

By pressing the Replace All button, you can find and replace multiple values at a time.

Note: The “Replace All” button replaces all occurrences of the search term at once. Alternatively, you can go through each occurrence using the “Find Next” button and decide to replace or skip each occurrence individually. it’s a good practice to use the “Find Next” button. This prevents unintended replacements.

• After that, you will get an info dialog box where Excel will notify you how many replacements happened.
• Press OK >> close the Find and Replace window >> get the result.

Note: If you want to replace only a certain number of items, then press the Find Next button until the cursor goes to the target cell >> then press the Replace button.

2. Replacing Number to Blank Cell

Suppose we didn’t fix the cost of one project, and now we want to change that cost ($1500) into a blank cell.

• So, press CTRL+H >> write 1500 in Find what box >> keep the Replace with box blank.
• Press the Find Next button >> this will move your cursor to the E13 cell >> press Replace button >> get the output.

3. Find and Replace Cell Color

In this case, we want to replace the cell color of cells F7 and F11.

• To do so, press CTRL+H to open the Find and Replace dialog box.
• Now, press the Options button. This will open the advanced settings of the Find and Replace tool.
• Then, from the Find what: section, click on the Format drop-down menu >> select Choose Format From Cell.

• At this time, you will get a dropper >> select the formatted cell with the dropper.

This dropper will consider the format of the F7 cell to find other cells containing a similar format.

Note: If you don’t use the dropper, then you must use the exact format to find the cell.

• Now, set the Fill color that you want to use as a replacement. So, in the Replace with: section, press the Format dropdown.

• Then, you will see the Replace Format dialog box.
• Go to the Fill segment >> choose the Fill color >> press OK.

• Press Find Next >> this will move your cursor to the target cell >> press Replace. Basically, you have to keep pressing the Find Next >> Replace button until all the required replacements happen.

• Lastly, close the Find and Replace dialog box >> get the result.

Note: Similarly, you can change the text color. To do so, after opening the Replace Format >> from Font >> change the font Color.

4. Replace Cell References of Formula

Let’s assume in my dataset that the cell range of summation is E6:E15. I have added some more projects. Now, I want to change the range to E6:E17. So, to replace cell references in the formula:

• Press CTRL+H to get the Find and Replace dialog box.
• Now write the cell index (E15) in Find what box >> write the target cell index (E17) in Replace with box.
• After that, press Find Next >> Replace.

Note: You can change the function as well. Just in the Find what box, write the function name (e.g. SUM) and then in the Replace with box, write the required function name (e.g. AVERAGE).

5. Find and Replace Data with Specific Number Format

Here, I want to change the currency format. There are Dollar symbols in the Cost column; now I want to make these into Euro symbols.

• Follow the procedure in Example 3 to open the Replace Format dialog box.
• Then, from the Number segment >> go to the Currency category >> choose Euro in the Symbol box >> press OK.

• After that, press the Replace All button >> press OK >> close the Find and Replace box.

• Below, you can see all the Dollar signs changed into Euro format.

6. Find and Replace Line Breaks in Excel

There are some line breaks in my dataset. I want to replace these line breaks and use single spaces.

• So, go to the Find what box >> press CTRL+J (this will provide a little dot in your box).
• Then, go to Replace with box >> press Space bar.
• Press Replace All.

• You will get an info dialog box saying how many replacements we made>> press OK >> close the Find and Replace box >> get the result.

Click on the image to get a detailed view

Below, you can see the final output.

How to Work with Advanced Find and Replace in Excel

To find data within a worksheet or multiple worksheets or even multiple workbooks, you can use the advanced settings of the Find and Replace tool.

To use the advanced options, open the Find and Replace box >> press the Options button. Here you can find exact matches, comments, and notes with these advanced options. Let’s see the following examples for more.

1. Find Exact Match Within Entire Workbook – Case Sensitive

Here, we want to find the project named ‘Sales Pipeline Analysis’.

• Press CTRL+F >> go to Find what box >> write Sales Pipeline Analysis.
• After that, press Options >> check the Match entire cell contents.
• Select Workbook in Within box >> press Find All.
• As a result, you will get the cell location of Sales Pipeline Analysis.

Note: This is case-sensitive. If you write only Sales Pipeline and check the Match entire cell contents box, then you will get a warning from Microsoft Excel saying we couldn’t find what you were looking for. The “Match entire cell contents” option ensures that only complete words are replaced, not partial matches within larger words.

2. Find Comments Within Active Sheet

Here, we have some comments about the projects. Now we want to find out all the comments as ‘Good’ within the active worksheet.

To find a comment within the active worksheet,

Press CTRL+F >> click on Options >> write Good in the Find what box >> go to the Look in section >> from the drop-down menu, choose Comments >> press Find All.

3. Find Values in Multiple Excel Files

To find values in multiple Excel files, follow the steps below:

• Open all the necessary workbooks >> go to any of the workbooks.
• From the Home tab >> under Editing group >> Find & Select >> Find >> write the data in Find what box >> press Find All.
• This will give all the target data for the active worksheet.
• Now, don’t close the Find and Replace dialog box.
• Go to another sheet of another workbook >> press Find All. This will give all the target data for that worksheet.
• Similarly, by hovering over different workbooks, you can find the same data on these sheets.

Read More: Find and Replace Values in Multiple Excel Files

How to Find and Replace Data Using Wildcards in Excel

You can use some wildcards, like asterisk and question marks, to find and replace data in Excel. When you know only some characters as the finding criteria, then you can use these wildcards.

Let’s assume we want to find all the project names having ‘Su’ as initial characters.

• So, press CTRL+F >> Write ‘Su*’ in the Find what box >> press Find All.

This will find all the cell values having ‘Su’ in any position (C10, C14 both). The cell value of C10 is Quality Assurance and Testing, and the cell value of C14 is Supply Chain Optimization.

• To find the project name having ‘Su’ exactly at the first position, then from the Options tab >> check the Match entire cell contents box.
• As a result, you will only get the cell value of C14 as output.

Now, if you don’t know a single character of a particular word or term, you can use a question mark to find that entire word/term. For example, we know the ID as A_3023001. But we don’t know the 2nd character of this ID. In this case, we should use the question mark in place of the unknown character.

• So, write A?3023001 in the Find what box >> get all the related IDs (AB3023001, AC3023001, AD3023001).

Note: If you don’t know two characters of any term, then use double question marks in these places. Basically, use question marks in place of unknown characters.

Read More: Find and Replace Values Using Wildcards

How to Find and Replace Special Characters in Excel

You can replace special characters like asterisks, parentheses, and tabs using the Find and Replace tool. Below, I’m showing what you should write in the Find what box to replace all of them with space or blank.

1. Find and Replace Asterisk (*) in Excel

We have an asterisk symbol in the Status column. Now, we want to find them and then remove these asterisk marks. To do so:

• Press Ctrl+H >>In the Find what box >> write ~* (use the equivalent symbol first then use an asterisk).
• Then, in the Replace with box >> press Space Bar for once >> press Replace All.

This will replace all the used asterisks in the dataset with a single space.

• After that, press OK >> close the Find and Replace box.
• See the output in the Status column.

Similarly, you can find and remove the Tabs. Just use Tab to Find what box >> keep the Replace with box blank >> press Replace All.

Read More: Find and Replace Asterisk (*) Character

2. Removing Cell Values Within Parentheses

Here, we have shortcodes within parentheses in the Project ID column.

We want to remove all these parentheses.

• So, Press Ctrl+H >> write (*) in the Find what box >> keep the Replace with box empty.
• Then, press the Replace All button.

After the replacements, you will see there are no shortcodes in the Project ID column.

Note: If you are making a lot of changes using find and replace, it’s a good idea to create a backup of your Excel file first. This ensures that you can revert back to the original data if something goes wrong during the replacement process.

How to Find and Replace with Formulas in Excel

We can use the Excel REPLACE, and SUBSTITUTE functions for the replacement of data. On the other hand, we can use the FIND, LOOKUP, VLOOKUP, HLOOKUP, and SEARCH, XLOOKUP functions for looking up values.

1. Using Excel Functions for Replacing Multiple Characters

In this section, we will see how to use Excel functions to replace multiple characters.

1.1 Using REPLACE Function to Replace Particular Characters

With the REPLACE function, you can replace some specific characters of a string with other characters. The REPLACE function is available from Excel 2007.

In our dataset, we have some values in column B. Now we are going to replace the particular characters “30” with “C4”.

• Use the following formula in cell C6.

• And press ENTER after this.

You will notice that ‘30’ are now replaced with ‘C4’.

• Now, drag the Fill Handle icon to cell C15 to get the result for other cells.

Read More: Find and Replace Using Formula

1.2 Using SUBSTITUTE Function to Replace Particular Word

You can use the SUBSTITUTE function to replace a particular word in a string. The SUBSTITUTE function is available from Excel 2007.

In our dataset, we will change a particular word from Notebook to Laptop.

• Go to the C6 cell >> enter the following formula:

Here, B6 is the text where the replacement will occur. 1 denotes that the replacement for the entire cell will happen once.

2. Using FIND Function to Find the Position of Specific Characters

Now, we will use the FIND function to find the position of a specific character “P” within a certain cell.

The FIND function is available from Excel 2007.

• In the D6 cell >> write down the following formula:

You will notice the character’s position is now shown in cell D6. ‘P’ is found in the C6 cell at the 4th position for the first time.

• Similarly, I have used the FIND function for other cells.

Note: FIND function is case-sensitive. So, you must also be careful about sentence cases. However, you can choose multiple characters to find their position.

3. Using XLOOKUP Function to Find and Replace Multiple Words from an Excel List

Now, we will find and replace multiple words from an Excel list using the XLOOKUP function. This function is used to look for a value in a range and then return a given value.

You can use this function only in the Excel 365 version.

In the following dataset, B6:C15 is the range of the Excel list. We will find values using this range as a reference.

• In cell F6, insert the following formula:

• After that, press ENTER.
• Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

In the XLOOKUP function, we inserted cell E6 as lookup_value, cell range B6:B15 as lookup_array, cell range C6:C15 as return_array, and cell E6 as if_not_found.

Click on the image to get a detailed view

How to Find and Replace Notes in Excel 365 with VBA

Here, we will use Excel VBA to find and replace notes in a worksheet. It is not possible to replace notes with the Find and Replace tool. We can only find notes using that tool.

Now, we have notes in the worksheet about the projects. Which means that we have to rework some of them. Now, let’s assume the authority did their job and wants to replace these notes.

• From the Developer tab >> go to Visual Basic >> Insert >> Module. If you don’t see the Developer tab, then you need to enable the Developer tab.
• Now, write the following code in the Module.

Sub ReplaceComments()
Dim ws As Worksheet
Dim cell As Range
Dim comm As comment
Dim searchText As String
Dim replaceText As String
Set ws = ThisWorkbook.Worksheets("Replacing Comment VBA")
searchText = "Rework"
replaceText = "Reworked-OK"
For Each cell In ws.UsedRange.Cells
If Not cell.comment Is Nothing Then
Set comm = cell.comment
If InStr(1, comm.Text, searchText, vbTextCompare) > 0 Then
comm.Text Text:=Replace(comm.Text, searchText, replaceText, , , vbTextCompare)
End If
End If
Next cell
Set comm = Nothing
Set cell = Nothing
Set ws = Nothing
MsgBox "Comments replaced successfully.", vbInformation
End Sub

Click on the image to get a detailed view

• Press the F5 key to run the code.
• After pressing the Run command, we can see that all the note text that was ‘Rework’ has now become ‘Reworked-OK’.

Download Practice Workbook

In this article, we have learned how to find and replace data in Excel using the Find and Replace tool. Also, we have shown the usage of Excel functions like REPLACE, SUBSTITUE, FIND, and XLOOKUP to do so; then how to use wildcards in the Find and Replace tool, apply keyboard shortcuts, and use advanced settings of the Find and Replace tool. We also used VBA code to find and replace Notes in Excel 365.

Find and Replace in Excel: Knowledge Hub

<< Go Back to Learn Excel