You can find occurrences of specific data or formats within a worksheet or replace specific data and formats with other data and formats.
- [Instructor] Whether you're looking for data in a list, or trying to replace data with other data, the Find and Select button located on the Home tab gives you multiple options for meeting these objectives. In this worksheet, called Find and Replace, it's in the workbook 05 Layout, we've got a list of data here, and we wanna make some changes. Sometimes you're just curious, you're trying to find something, you wanna know if it exists. We're looking in column C. I'm curious as to whether, although I see one training group here, is there another department that has the word "training" in it? Let's select column C.
Before looking for information, it's always best to narrow it down by either highlighting cells or selecting columns, and off to the right on the Home tab, Find and Select. Let's choose Find, and in this dialog box, you might see a color here, you might see a preview. You can search by format, that's a bit unusual, but let's search for content itself. If there is an existing format there, clear that. Let's focus on content here. We're looking for the word "training." Now, there are filtering techniques that could meet this need in a different way, but let's try this first, let's find the next one, of course that takes us right here, we could find all of them.
Let's choose All. There's a list here. There're 33 cells. And we can see by expanding this list here, or scrolling through it, where that exists, there's professional training group and somehow there's another group called training. Now we can go to these one by one, we might wanna change the names of these but for the moment we're simply looking. So that serves a particular purpose or need. Let's say we want to change the use of slash. Now this occurs in different cells here, for example we see it in C4, we see it in C6, also down here in C9.
We've decided that it's going to be better if we use the ampersand symbol here, with a space on either side. So now what we want to do in column C is replace that slash with a space, the ampersand symbol, and then a space. So with column C selected, Find and Select, this time we'll choose Replace. And what are we trying to replace here? Under Find what, we use the slash symbol. What do we want to replace it with? A space, that's the ampersand symbol, the and symbol above the number seven key, and a space.
And we wanna do this for all. We could do it one by one, but I think it makes sense and what we really wanna do here is for all these, we'll replace all, and keep your eye on column C, as we make that change. 73 different replacements. Lot simpler than doing it manually, of course. When you come into this Find and Replace dialog box, recognize that at certain points, you might not see it as large as this. So sometimes you'll see an Options button. If the Find and Replace box looks like this, you might want to expand it.
In the example here, we actually didn't need it expanded, but we do have the ability to expand and collapse this. Because sometimes it could be critical about matching case, upper and lower case, for example. But the feature worked fine right here. Here's another example. In column G, R means retirement, D means dental, M means medical. Every time we've got DMR here, we've decided to replace this with the word "Full." Clicking column G, Find and Select, Replace. What do we wanna replace here? Now we don't wanna keep our previous examples here anymore, so make sure we wipe those out.
Replace what? DMR with Full. Meaning the full benefits package. And here too, simply do a replace all. 169 choices. And we see those examples as well here too. So it's very easy to use this capability. Sometimes we're simply looking to find data, at other times we want to find and replace information. Various examples of how to either locate data or replace it using the Find and Select button on the Home tab.
- Navigating Excel tabs and menus
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros