Hi, this is Wayne again with a topic “Excel Find and Replace Tutorial”.
In this intermediate Excel tutorial, I want to show you how to use, Find and Replace in Microsoft, Excel to clean up your data and fix mistakes or make changes that need to be made, and the spreadsheet that I’m gon na use for this is a list of Cds for a hypothetical synth-pop CD store – and you can see here – I have a list of some excellent synth-pop that has come out in recent years. Some of this is pretty famous stuff, but a lot of this is also kind of underground and not so famous. But I just noticed a mistake in my data here: the band division, which is very similar to Depeche Mode in a lot of ways, but also over the years they’ve developed their own sound their own, take on the synth pop, but anyway, I’ve noticed that division. Here is not spelled exactly right and of course, I could fix that myself. I could just click on each cell or double click to get inside the cell and change the spelling to make it correct, but there’s a much faster way to do so, and that is by going to the Home tab home ribbon on the right side.
In the editing group I can click on, find and select, and when I do, I get a pop-up and notice that one of the options in the pop-up is replace, and so this is find and replace. Even though it’s found in the find and select button so find and replace I’ll click that and I get a pop-up and it’s a very simple plain, pop-up – find what and replace it with what so very simple, but notice that also in the lower right corner, there Is an options button and at some point, you’re gon na want to click that, because you get additional options that are pretty good. So I just clicked it now and I’ll stick with that. But if you think that looks too complicated, go ahead and just click options and go back to the simple view. Okay, so what do I want to find? I want to find the word division and I want to replace it with the word D /video us that, if I want to, I can force it to match case and also notice where it’s gon na search is gon na search within the sheet.
I click replace all and you can see what it says all done. We made three replacements. All three instances of division have been changed so that they match the real name of the band. At this point, I’m gon na click close, and that worked and I’m done and I’m happy now. I want you to know that this works not only with content the content of my spreadsheet, so changing all number 15 s to be 20 or I could change every and into an ampersand symbol so find and replace, is really good for making those kinds of changes.
But that’s not the only kind of change it can make. It can also search for formatting and change the formatting to what you want to change it to so, for example, in this spreadsheet, let’s say that I use yellow highlighting to mark CDs that are almost sold out and I need to restock the inventory okay. So these are CDs that are selling very well. I need to restock them well, once the new stock comes, I don’t need them to be yellow anymore and of course I could do this manually.
I could just highlight the row and go up here and change it to no fill, so I could certainly do that, but I’d have to do it five six times. Let’S try find and replace. So I click on, find and select. I go to replace and this time I want to get rid of the word division division get rid of that this time. I don’t really care what the content is. What the words say, what the numbers say, what I care about is the format, so I’m gon na click here on format and format and there’s all of these different kinds of formats that you can have it find. But in this case I’m just gon na go to fill and I’ll choose the yellow, highlighter color. I click on it, click okay, so it’s gon na search for that particular color and where it finds that color it will replace it with right now. Nothing we have to replace it with something, so I’m gon na go here to format. I’Ll click off to the side. Here choose format and I will choose no color click. Ok, now it should work.
Let’S give it a try. I click replace all all done. We made 55 replacements now that may not have looked like 55 replacements. It was only like 6 rows. The way they count. This is each cell, each individual cell counts as one and so because it went all the way over to the right.
It does add up to 55, so I click okay, close and my spreadsheet is back the way I want it to be so to me, I see, Find and Replace as being very effective, very useful, and I use it quite a bit if you ever have trouble Using it, let’s say you go here to format and it doesn’t let you choose a format you might want to close out and click and drag to highlight all of the columns in your spreadsheet or if you prefer, you can click and drag to highlight all of The rows in your spreadsheet so sometimes highlighting the data that you want to search through in order to find and replace sometimes highlighting. It is the key to being able to use all of these features. But in my case, as you saw, I didn’t really need to do that. I could just click in the data somewhere.
Click find and select, replace and then make my selections of what to find and what to replace it with one last thing I wanted to point out is: let’s say you would like to search for a particular color or a particular format, but you don’t know what It’S really called you, don’t know what it is and it’s hard for you to select. Let’S say the exact color you know which of these colors is the exact one. Maybe it’s this one. Maybe it’s this one.
Maybe it’s this one. If you’re uncertain about that, you may want to choose this option, choose format from cell and then you can just click on a cell. Whatever the format of that cell is that’s what it will search for and then it will replace it with whatever you choose next. I hope that you found this video to be helpful.
If you did, please click the like button below and consider connecting with me on. My social media websites, like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and watch for another video from me at least every Monday. If you’d like to learn more about some of these synth pop bands, please look in the description below where I have some links, the ones that I’m most interested in right.
Now our talk talk, which I’ve been familiar with for years, but lately I have some renewed interest in their work. They were a pretty early, synth pop and new wave band, but toward the their career. They switched styles to post-rock and I’m enjoying revisiting all of their music, so I recommend them and bloom has recently come out with a great album called ashes if you like, synth pop or future pop check that out in the description below and divisions, newest, album is Called city beats so, if you’re interested in this kind of music, I highly recommend you check out the links in the description below and if you’d like, to support my channel, please consider becoming a patron of my channel through patreon and you’ll.
Find links to my patreon account in the description below .