How to Find and Replace in Excel: Simplify Your Spreadsheet Tasks
Working on a large spreadsheet? Things can get a bit confusing and overwhelming, especially if you have to edit some values in their respective cells. Finding the target text strings/numbers and manually replacing them can be time-consuming, which is why Excel has the Find and Replace feature.
This feature helps you find the target values based on your search and then automatically replaces them with another input from you. Here we’ll show you how this feature works and how you can harness it to simplify your spreadsheet work.
How Does Find and Replace in Excel Work?
If the Find and Replace feature is still not clear to you, the following scenario will make it more intelligible:
Suppose I fill a certain number of cells with the word FLAT and later get an editorial instruction to change it to APARTMENT. In that case, I’ll have to find each cell housing the word FLAT and manually replace it with APARTMENT.
However, with the Find and Replace feature in Excel, we can speed this up by simply using a dialog box to automatically find all cells with the word FLAT and replace them with APARTMENT.
Now, let’s use Find and Replace in Excel with that out of the way.
Different Methods for Using Find and Replace in Excel
Find and Replace Cell Values
You can use this method to find and replace anything in a column. To use it,
- Press Ctrl + H to launch the Find and Replace dialog box. Alternatively, go to Home > Editing > Find & Select > Replace
- In the dialog box, input your search keyword in the Find What box and what you want to replace it with in the Replace With box.
- Next, click Replace to make the changes one step at a time or Replace All to do so at once.
You should get a dialog box confirming the change with the text “All Done.”
Find and Replace Column Formats
Want to replace your format with another? You can do just that with this method in the Find and Replace dialog box. Use the following steps:
- Launch the dialog box as instructed earlier
- Click the Options button
- Next, navigate to and select the Format… drop-down
- Select Choose Format From Cell in the drop-down menu
- Next, choose the cell that contains your target format
- Select the format from the options
- Now, click the Format drop-down at the bottom and pick the format you want to replace your previous format with
- Click OK
- Finally, click Replace or Replace All based on your preference.
You should see the new format in your columns at this point.
Find and Replace Similar Values
This method is called the Wildcards and will come in handy if you want to find multiple/similar characters, such as column and columnist. Simply add the asterisk symbol (*) in front of your search keyword when you get to Find What in the dialog box. Excel will bring up all relevant texts as results.
Find and Delete
I guess this one is pretty self-explanatory. You find values in a cell and then replace them with nothing, the same as deleting them. To do this,
- Open the Find and Replace dialog box
- Fill the Find What box
- Leave the Replace With box empty
- Click Replace or Replace All to save the changes.
Final Notes
There you have it! The four methods with which you can find and replace in Excel. Now you can edit cells, change formats, and delete values easily. Should you need any further assistance, use the comments section below. I’ll be available to help. If you found this article handy and would be interested in other ways to ease your tasks on Excel, you should learn such tricks as how to strikethrough in Excel and how to find duplicates in Excel.
