This effortless Microsoft Excel tutorial shall explain how to find and replace in Excel with different situations and use cases.

Often you want to locate a value or text string on your Excel worksheet for data analysis and visualization purposes. Also, you may want to look for a specific item in an Excel workbook whether that’s a company stock inventory, pricing booklet, or academic syllabus.

If the Excel worksheet contains thousands of rows and columns filled with numerical and text strings, finding a single value in it becomes impossible to manually scan each row and column. Here comes the Find and Replace tool of Microsoft Excel.

Stick around if you’re new to Excel and want to master your skill of locating a value in your spreadsheet and replacing that with an updated value. If you’ve already achieved Excel mastery, you shall discover here secret ways to make great use of the Excel Find and Replace tool. Join me and follow along until the end as I show multiple ways of finding and replacing data in Excel.

How to Find and Replace in Windows

To find a value in your Excel desktop app in Windows and replace that with the latest data, navigate to your worksheet and click on the Find & Select drop-down in the Editing block of the Excel Home tab.

The Find & Select context menu shall open. There, click on the Find option to bring up the Find and Replace dialog.

Now, input the required data in the following fields of Find and Replace to look up the required value or text string:

• Find what: Here goes the look-up value or string.
• Within: You can change it to Sheet if you want to look up a value locally or switch to Workbook if you wish to find data globally.
• Search: Click this drop-down field to choose the search field By Rows or By Columns.
• Look in: You need to choose where to find the target value, whether in Formulas, Notes, Value, etc.
• Match case: Checkmark this checkbox if you wish to find a text string in lower case or upper case.
• Match entire cell contents: Checkmark this checkbox if you want Excel to only find and replace the specified text if it exactly matches the entire content of the cell.

So you’ve successfully configured the Find and Replace dialog box according to the value or text string you’re searching for. Now, you need to learn how to visualize the look up values in the tool as a list.

If you wish to visualize all the cells containing the search item, you can click the Find All button.

Below the Find All button, you shall see a list of looked-up content along with hyperlinks to those elements in the worksheet or workbook.

Suppose, you only want to cycle through the occurrences of the searched item in the worksheet or workbook. In that case, you need to click the Find Next button multiple times to locate the lookup values one by one in the source dataset.

Once you’ve located the values you need to replace with updated data, click on the Replace tab of the Find and Replace tool.

You shall now see the Replace with field. There, supply the updated value and click Replace All to update all of the cells listed in Find and Replace.

If you wish to carefully replace the values, click the Replace button. In this mode, Excel shall replace one cell at a time so you can also audit the outcome of the data updating in your worksheet.

📒 Read More: Bulk Find And Replace In Power Query

Various Find and Replace Shortcuts

There are various hotkeys for Excel Find and Replace tool to make your work easier. Find below the keyboard shortcuts:

• Ctrl + F: By using this keyboard combination, you can bring up the Find and Replace tool quickly on your worksheet.
• Ctrl + H: Alternatively, you can use this hotkey to launch Find and Replace with the Replace with field.
• F5: This keyboard shortcut launches the Go To dialog box from which you can activate the Go To Special tool.

Find and Replace in Mac

The Find and Replace tool (Command + Shift + H) in Excel for Mac is available in the Editing commands block as it appears in the Excel for Windows app.

However, there’s a special search tool in Excel for Mac, named Search in Sheet. You can find it in the top right corner of the application.

Open your Excel workbook on Mac and go to a worksheet where you want to perform find and replace. Now, click on the Search in Sheet field and type the value you want to look up.

Hit Return to select the first occurrence of the looked-up value. If you press Return again, Excel shall cycle through all the occurrences of the keyword.

If you click the drop-down arrow next to the magnification icon in the Search in Sheet tool, you’ll get a context menu. Here, you can choose Search in Sheet to look up a value in the active worksheet or select Search in Workbook to look up the value in the entire Excel file.

If you click the Advance Search option, the Find and Replace tool shall open. From this dialog, you can swap between the Find and Replace tabs.

Find and Replace on the Web

The full functional Find and Replace feature isn’t available in Excel for the web app. However, there’s a basic Find and Replace tool.

Open your source worksheet in the Excel web app from your Microsoft 364 portal. Now, press Ctrl + F to launch the Find and Replace tool.

Here, you can switch between the Find and Replace tabs to use either of the features to look up a value and update that with a different value.

To extend the features of this tool, you can click the Search options drop-down to find the following options to fine-tune the look-up process:

• Within: To search within Sheet or Workbook.
• Direction: To look up a value in the Up or Down direction

The web app lacks critical find-and-replace features like cell format-based search, row and column-wise search, and custom Look-in fields, like Values, Formulas, etc.

Find and Replace in Android

You can find almost all the features of Excel desktop Find and Replace in the Android app.

Open the Excel app on the Android device and launch a workbook from the Excel backstage.

Find and tap on the magnification icon on the menu bar above the worksheet. This shall enable the Find field.

If you search for any value or text string in the Find field and tap the magnification icon on the keyboard, the Excel app looks up the value in the formulas and values of the worksheet.

To cycle through all the occurrences of the looked-up value, click the left arrow and right arrow in the top right corner of the app.

Now, if you’d like to replace the look-up value, tap the gear icon to the left of the Find field. This should pop open the Find Settings from the bottom of the app.

You can now switch to Find and Replace to look up a value and replace that with the updated value one by one. Alternatively, you can click on Find and Replace All to replace all outdated lookup values with the latest value at once.

When you choose Find and Replace or Find and Replace All, the Replace field shall show up just below the Find field on the Android Excel app.

To replace the old value with the new one, either tap the checkmark key on the Android keyboard or tap the Replace All button in the top right corner.

Just so that you know, you can also find Find within: Sheet / Workbook and Match: Case / Entire Cell Contents options in the Find settings on the Excel Android application.

If you’re using the Excel for iOS app on an iPhone or iPad, you can find and replace values or text strings the same way as you can on an Android device. Just follow the steps mentioned earlier.

How to Use Find and Replace Wildcards

You can use many wildcards in Find and Replace to represent one or more characters in a search string. Find below the most common ones:

• ? (Question Mark): Represents a single character. For example, searching for te?t will match test, text, etc.

• *(Asterisk): Represents zero or more characters. For example, searching for app* will match apple, application, etc.

• ~ (Tilde): Used as an Escape character to search for literal instances of wildcard characters. For example, searching for ~? will find the question mark character itself.

How to Use Find and Replace for Cell Formatting

Suppose, you want to replace a cell formatting style, you can use the Find and Replace tool to do so quickly and accurately.

For this purpose, you’ll be using the Format search feature of Find and Replace. This feature isn’t available in Excel for Mac, Excel Android, and Excel iOS apps.

Go to the source worksheet and press Ctrl + H to launch the Find and Replace tool.

In Find and Replace, click on the Format drop-down arrow and select the Choose Format From Cell option for the Find what field.

The Excel cursor shall change into a color picker icon. Use this cursor to click on the cell format you want to replace in the source worksheet.

The cell style shall show up in the Preview button.

Now, to replace the dated cell formatting with a new one, click on the Format button of the Replace with field and create a cell formatting using the elements of the Format Cells dialog.

Click Replace All to instantly modify all existing cell styles selected in the Find what field to the new cell formatting created in the Replace with field.

How to Find and Replace Comments in Excel

Suppose, you’re working on an Excel worksheet collaboratively with colleagues. There are various comments in cells of the datasets that you need to resolve. Of all those comments, the suggestions related to design are the most important.

You can scan through all the comments manually to find out all the comments mentioning the term design or use a semi-automatic process involving the Find and Replace tool.

Go to the source worksheet and press Ctrl + F to bring up the Find and Replace dialog box.

In the Find what field, enter the keyword you’re looking for in the comments. In this tutorial, it’s Design.

Click on the Look in drop-down menu and choose Comments.

Click the Find All button to list all the comments below the Find and Replace tool mentioning the keyword Design.

Find and Replace Using Excel VBA

If you want to automate the process of finding a value or text string in a large dataset and replacing that with an updated input, you can use the following Excel VBA script:

Sub FindAndReplace()

    Dim FindString As String
    Dim ReplaceString As String
    Dim LookInFormulas As Boolean
    Dim Scope As String
    Dim FindAndReplace As Boolean
    Dim ws As Worksheet

    FindAndReplace = MsgBox("Do you want to Find and Replace? Click 'Yes' for Find and Replace, 'No' for Find only.", vbYesNo + vbQuestion) = vbYes
    FindString = InputBox("Enter the search term (value or text string):")
    Scope = InputBox("Enter the scope of search: 'Worksheet' for current worksheet, 'Workbook' for entire workbook:")
    LookInFormulas = MsgBox("Do you want to look in formulas? Click 'Yes' for formulas, 'No' for values.", vbYesNo + vbQuestion) = vbYes

    If FindAndReplace Then
        ReplaceString = InputBox("Enter the replacement term (value or text string):")
    End If

    If Scope = "Workbook" Then
        For Each ws In ThisWorkbook.Worksheets
            If LookInFormulas Then
                ws.Cells.Replace What:=FindString, Replacement:=ReplaceString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Else
                ws.UsedRange.Value = ws.UsedRange.Value
                ws.Cells.Replace What:=FindString, Replacement:=ReplaceString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            End If
        Next ws
    Else
        If LookInFormulas Then
            ActiveSheet.Cells.Replace What:=FindString, Replacement:=ReplaceString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Else
            ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
            ActiveSheet.Cells.Replace What:=FindString, Replacement:=ReplaceString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        End If
    End If

    MsgBox "Operation completed.", vbInformation

End Sub

To use this script to create a VBA macro, you can follow the instructions mentioned in this article:

📒 Read More: How To Use The VBA Code You Find Online

After creating the VBA macro, press Alt + F8 to bring up the Macro dialog. There, select the FindAndReplace macro and hit Run to execute the VBA script.

As soon as you run the script, Excel shall prompt you for data inputs in easy-to-understand language.

You must comply with those prompts by supplying enough data so Excel can automatically find the look-up value and replace that with an updated value.

Conclusions

Now you know how to find and replace in Excel in different ways in various scenarios. I hope you’ve tried and tested the methods on your own worksheet to remember the methods for future requirements.

If the article helped or you’d like to share feedback comment below. Also, comment if you know a better method for Excel Find and Replace.