Finding and Replacing Data

When you work with Excel, sometimes you have to deal with very large amounts of data. As a result, finding a particular value can be very time-consuming. Fortunately, Excel comes with a powerful search tool, thanks to which you can quickly find the data you want.

Finding Data

To open the Find and Replace window, go to the HOME >> Editing >> Find & Select >> Find or use one of the keyboard shortcuts: Ctrl + F or Shift + F5.

When a new window appears, you will notice that the tool in its basic form doesn’t contain many options.

If you want to have access to more settings, click Options >> button.

Here, you will find here three drop-down menus.

Options in the Basic Find Window

Within:

You can choose whether you want to scan for data in this sheet only or in the entire workbook.

Search:

Here, you can decide whether the values should be searched by rows or columns. This means that when a Search option is set to By Columns and you click the Find Next button, Excel will start searching in the first column and then in the next.

Look in:

You can choose whether Excel searches for data in formulas, values or comments.

Advanced Settings

Click the Options button to access more settings:

  • Match case: If unchecked, variations of capitalization (e.g., “John Smith” vs. “john smith”) will be considered a match.
  • Match entire cell contents: When checked, only cells containing the exact phrase you type will be considered.
  • Format: Set additional parameters (e.g., font, background color). For instance, you can search for “John Smith” with a blue background.

Replacing Data

You will find a Replace button in HOME >> Editing >> Find & Select >> Replace. You can also use the Ctrl + H keyboard shortcut.

This tab is very similar to the Find tab. The difference is that in the Replace tab there is an additional textbox called Replace with. There, you can enter the text to which you want to replace the text inside the Find what text box. You can also select the formatting style.

If you don’t want to change the formatting style, but the format style is already set, you can remove it by clicking on the triangle on the Format button and then selecting Clear Replace Format.

If you want to change the formatting to normal, click on the triangle, then select Choose Format From Cell and click on the empty cell.

If you’re unsure about the cell format, use the small triangle next to the Format button to choose a cell as a reference.

TIP

Remember, mastering the Find and Replace functionality empowers you to efficiently manage your data, whether you’re working on financial spreadsheets, databases, or any other Excel project.

Excel 365 Update

  • Enhanced Search Options: Excel 365 offers more options for refining your search criteria. You can now use wildcards like asterisks (*) and question marks (?) to represent multiple characters or single characters, respectively. This provides greater flexibility when searching for specific patterns within your data. https://support.microsoft.com/en-au/office/find-or-replace-text-and-numbers-on-a-worksheet-0e304ca5-ecef-4808-b90f-fdb42f892e90
  • Look Inside: This feature allows you to specify whether you want to search only within values, formulas, or comments within cells. This level of control can be helpful for targeted replacements based on the data type.
  • Regular Expressions: While not directly new in Excel 365, mentioning the ability to use regular expressions for advanced pattern matching during search and replace can be valuable for experienced users.
  • Formulas for Replacements: Briefly mentioning that formulas can be used within the “Replace with” field allows for more complex replacements based on calculations or manipulations of the found data.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.