Find and Replace in Excel

You might find yourself wanting to change one word, number, or phrase for another inside a spreadsheet. Of course, this could be done manually, but that would be difficult if you need to make many changes. Fortunately, Microsoft Excel has a handy feature to do the work automatically. Let’s learn how to use Find and Replace in Excel.

How to Use Find and Replace in Microsoft Excel

Consider a list of dates containing perhaps hundreds or thousands of rows. You’ve been sent the data, and you’ve spotted a problem.  The list is supposed to show dates for the current year. But instead of 2021, it says 2012. Are you now facing the daunting task of having to manually re-type and correct each year?

Fortunately, not at all! Excel has a handy feature built in, called Find & Replace. As the name suggests, it’s a two-step process. It first finds a given word, number, or phrase. Then, it allows you to input a replacement value.

Once you run it, the original value is replaced with the new. It’s especially useful in two cases: when you’re making bulk edits, and when you’re correcting values that Spell Check cannot identify. In this example, both of those conditions are true.

To get started using Find & Replace to clean up your data, begin by navigating to the Home tab on Excel’s ribbon. This is typically the starting point for many features in Excel. On the right side of the menu, in the upper right corner of your screen, locate the Find & Select dropdown.

Click here. Now, Excel’s standalone Find feature is your first option. This is essentially a search function that locates – but doesn’t replace – a given value anywhere in your spreadsheet. It’s useful as a standalone feature, but here, you’ll want to click on the second option: Replace.

Excel then launches the Replace menu. As you can see, you have several options here. Let’s work through them in order. Find What is where you’ll input the value that you want to search for and replace. Here, of course, that’s the year: 2012. Go ahead and type it into the white dialogue box.

Then, you can specify Excel’s exact search behavior. For example, you can search within the displayed sheet, or across multiple sheets to span your entire workbook. Additionally, you can determine whether to match case. This is most useful when searching for text. Plus, you can opt to find entire cells only.

Next up comes the true power of Excel’s Replace feature. Here, you’re telling the app what to do when it locates the Find value. Remember, you want any and every occurrence of 2012 to be replaced with 2021. With this in mind, type 2021 in the Replace With box at the bottom.

Once again, you have choices. You can opt to Replace, or Replace All. Let’s think about the difference there. In our example, we want to replace every last occurrence of 2012 with 2021. Replace All is the best bet here. Clicking on it will instantly replace the old value with the new. Excel performs the analysis automatically and performs the changes – not a single keystroke is required by you!

Replace gives you more control. It’s useful if you only want to replace some of the values you’ve found, but not all of them. Whichever option you choose, Excel makes it easy to make bulk replacements in even the largest datasets. This is the true power of Microsoft Excel spreadsheets. With a few quick clicks, you can perform the equivalent of many hours of tedious work almost instantly.