It often happens that data needs to be changed within a spreadsheet. Usually this is due to examples such as price increases, name changes and similar occurrences. This is an absolute terror if you are planning on finding them individually and manually entering new data into each of them. It is much easier to simply use the Find and Replace feature that Excel offers. This will allow you find the data you need and replace every repetition in one easy manoeuvre.
This Excel tutorial is going to teach you exactly how to search for data in your spreadsheet and replace it with something else. The first step is to access your Find and Replace dialog box. Press Ctrl and F at the same time on your keyboard; or go to the Editing box in the Home tab of your Excel toolbar. Click on Find & Select, then choose Replace…
This will command Excel to open the Find and Replace dialog box. Make sure that you are in the Replace tab:
If you click on Options, you will have more fields to use in your search. They will help refine the variables and give more accurate results. Below we list the different options and explain how to use the Find and Replace dialog box:
-
Find What
Type in the text or value you wish to find. This is the information that is going to be found and subsequently replaced with other data.
-
Replace With
Type the new text or value in this field. The results of your search will be replaced with the information you enter here.
-
Format
If you wish to include a specific format in your search or add a format to your replacement; then click on this option. You will be asked if you want to copy a specific format by clicking on a cell that contains it, or you can select Format and choose your requirements from the tabs in the Find Format dialog box.
-
Within
This option allows you the choice of searching in your current worksheet or in the entire workbook.
-
Search
You are able to search first by rows and then by columns, or vice versa.
-
Look In
This enables you to search formulas, values or comments as well.
-
Match Case
Check this box if you only want case-specific results to appear.
-
Match Entire Cell Contents
If you require the search results to match your criteria, then checking this box will only list exact matches for you.
Once you have refined your search by choosing specific options, you can instruct Excel to replace the old data with the new information you desire. Before you do this, be sure that there are no errors in your instructions and you do not end up replacing the wrong data.
-
Replace All
This time-saving command will instantly replace all the results with your new data and format.
-
Replace
Selecting this option will only replace one search result at a time.
-
Find All
This choice will list all the search results that you can check through before performing final replacement.
-
Find Next
You will be taken to the results one by one and replace each of them individually.
Now that you are able to quickly and easily replace hundreds of formats, values and texts; you will not waste a moment when data changes loom. Manual search and individual entry is a complete waste of time and will never be considered again.
This article was written after enthusiastic encouragement from our Excel training course students in Miami and the surrounding South Florida area. There are many features that can be used to make spreadsheet creation and maintenance a pleasure; please watch our Excel video tutorials to discover how they Excel can be used to help you.
