Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Under the Find & Select button, you'll see a Replace command. what this does is it the replaces the information that you find in your spreadsheet with other information. Why would you want to use this? Well, you would use it if you know that there's an error in your spreadsheet or if you've changed a particular Item Category to include more information, and you want to be able to do this very quickly. Let's see how this works. Open up StoreA worksheet to follow along. I'm going to click the Replace command, and you'll see that the dialog box that comes up is very similar to the Find dialog box. So at any point in time, you can find information and choose to replace it and flip between these two commands very, very quickly.
The item that I want to find in the spreadsheet here is the WW Pastry reference because that was put in incorrectly. And what I'd like to replace it with is actually the whole word, WholeWheat Pastry. This often happens when you have more than one person inputting data into an Excel spreadsheet. You don't get consistency with some of the labels that you're using. Some people use abbreviations for things. So to get some consistency, especially when you're doing your analysis, you want to be able to replace information very quickly.
You can do that by clicking the Find Next, so it's going to find this WW Pastry, and then clicking on Replace. And see what happens if you take a look at your spreadsheet? Just below here, you'll notice that it's changed from WW Pastry to WholeWheat, so it takes the original information that it found, and replaces it with the new information you're identifying. And I only have one instance of this in my spreadsheet, so we can't repeat this. But you could go through each individual instance of this and click Find Next and Replace.
Now a fast way to work with is by using the Replace All button. What this would do, is it would, as you clicked on it, go through the whole spreadsheet and replace all instances of WW Pastry with WholeWheat Pastry. It also gives you a count at the end of it of how many replacements it's done. Notice you have an Options button here, just as you had in the Find environment, and you have the same options that you can work with. You can set in Formats for what you were looking for. You can also identify if you're going to look at the whole sheet or the workbook.
If you're going to search by rows or by columns, and if you're going to look in formulas. You can identify whether you're going to be case-sensitive by matching the case and matching the entire contents of all the cells that you're looking at. The Find & Replace command is probably one of the most powerful commands that you're going to learn to use in the Excel spreadsheet, and it saves you hours and hours of tedious adjustments of your data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64698 Viewers
80 Video lessons · 124318 Viewers
52 Video lessons · 60253 Viewers
59 Video lessons · 46084 Viewers