Microsoft 365 Excel Intermediate (2023): Search and Replace

Name: Microsoft 365 Excel Intermediate (2023): Search and Replace
Author: Bob Akbar

Learn to navigate the search and replace functions in Excel 365

Preview the first lesson free — get full access to all 3 lessons.

Course: On-Demand

Hello, my name is Bob Akbar, and in these lessons, you will learn how to use the Find, Search, and Replace functions, as well as other methods of doing so, to properly edit data and keep a consistent method of editing and revising data. In this lesson, I will show you how to use Find and Select. Now, let's keep in mind, we use computers to increase our accuracy. And even though in this example here, we have 13 datasets, we could have 300. And trying to find everything individually is something where humans make mistakes. And by using Excel 365, we are able to eliminate this problem. So let's take a look at the two options we have. We have a search here on the top, and we have another one down here, which has Find, Replace, and Go To. The one up here is more often used as a help in Excel to look things up. And when you click on it, you can see it comes up with suggestions for different methods of doing things in Excel, rather than finding your data points. So what we're going to do instead is we're going to take a look at the Find and Select here, and we're going to go to Find. You will notice there's a Find and a Replace. We are focusing on the Find. So in the Find, we can put in a text, and there's additional search options. You will notice within the search options, we can either search within the current sheet, or we can do a selection or the whole workbook. And it also gives us a choice of directions for down or up. So let's try just a simple search. Let's do Jane. We can do Find Next and Find All. And when we do Find All, you notice how it tells us where the values are located, and it highlights where Jane actually is. So let's try now our search options. You notice we are currently in cell 7. And what if we just want to go and find the ones that are below that? Say we are looking for sales that are past this date. So we'll hit Next, Find Next, and you will notice it will go in the down direction as we asked it to do. So it went below our date, and this is where Jane was found after that. So what if we want to find if Jane had any sales prior or before this date? Then we would pick the up, click Find Next, and there it is. It highlights the one that is in the up direction, or as we have our data listed, the previous or before that time for the sales. Now, another option that you will see is it says Match Case. And this is where it comes in handy. If we simply put ED, it is not going to look for the entire cell content by default. And let's see what happens if we say Find All of them. Ted has an ED in his name. Ed has an ED. So they all show up as matches. This is why we want to do match the entire cell content when we do our searches. So if we are looking for Ted, there it is. It finds exact matches. So if we had an employee that was named Teddy, if we take off our match entire cell content, look at that. Teddy and Ted both show up. And this is why it's important for us to match the entire cell content. If we're just looking for Ted, we match the entire cell content. Then we do Find All. And there it is. Only Ted shows up in our searches. Now, the Match Case is another option. What if we had an employee named Ed? If we simply put in Ed, and we say Find All, you notice Eddie, Ted, and Ed all show up as matches in the Find. The way that we solve this problem is we do Match Case and Match Entire Cell Content. And then it says it cannot find Eddie. So as we take a look at here, and we do match the entire cell content. And the same problem. Now, when we run into this problem, we have to see why that's happening. And if we go into this cell, you will notice the extra space. I know it sounds like a small, small detail, but it does make a difference in your search. Now we get rid of the space. And look at that. It matched the entire cell content. The space, whether you put it here or in the name, it will make a difference when you're doing an exact match. So that is definitely something to keep in mind. So now that we have this figured out, what if we want to do anybody who has greater than a certain amount of sales, but we only want to search this area? And if we only want to search this area, this is where once you have highlighted the section, the selection option is now available. So now we are only searching the selection area, which is highlighted here. And now we can search for Ed. And there it is. Ted and Eddie, as we discussed, both will pop up. If we put match the entire cell, it is going to tell us there is no Ed listed in this section. And that is exactly what we are looking for. There is no Ed listed in this section. Now, what if we want to search for something by date? We can do the selection or we can simply go back to our search, do a find. And what if we want something that is in March? You notice I put a question mark, which is a wild card. And within the range we were looking for, anything that only had one digit showed up because we put one question mark. If we change that to two, then it only brings up the ones with two digits. And finally, if we put a wild card, which is an asterisk, now it doesn't matter how many digits there are. It selects all of the ones that have a three for the month, anything, one or two digits, and 2023. Stay tuned for the next lesson where we will talk about search and replace. Thanks for watching.

Intermediate Provider : Bob Akbar 3 Lessons · 22m in Arabic, German, English, Spanish, French, Portuguese, Chinese

Course Description

When conducting searches, you may be trying to locate certain values or strings containing those values. You may be looking to find and replace one item. Another possibility is that you may be looking to find and/or replace multiple instances of the same string. Excel provides tools for this in an easy-to-use interface. You can work with more common items such as dates or more case-specific items like product code that identifies a certain characteristic utilized in the data.

Excel 365 provides partial matches if you need to find all mentions of the string being searched. You can specify which area to search, allowing for flexibility. You can also search based on scope such as workbook, worksheet, or selection. This gives the user great control over the use of these features.

Excel provides a great tool for users to find and replace items in their text or data. By being able to use the next option, searching through long workbooks is simplified. Excel also uses wild cards and match options such as case sensitive and exact matches.

What You'll Learn

Understand Excel search syntax for locating values and strings
Describe how to use Excel's search and replace to find and replace single or multiple instances of a string
Recognize different Excel search options, including specifying the area and scope (workbook, worksheet, or selection)
Apply wild cards and match options such as case-sensitive and exact matches
Use Find and Select, including additional Find and Select methods, to navigate long workbooks

Key Takeaways

Excel provides find-and-replace tools in an easy-to-use interface for locating values or strings and replacing one or multiple instances of the same string.
Searches can target common items like dates or case-specific items like product codes, and Excel can return partial matches to find all mentions of a searched string.
Users can specify which area to search and choose a scope such as workbook, worksheet, or selection, giving control over the search.
Excel supports wild cards and match options such as case-sensitive and exact matches.
The next option simplifies searching through long workbooks.

Frequently Asked Questions

What does this course cover?

It covers using Microsoft 365 Excel's search and replace features, including Find and Select, Search and Replace, and Additional Find and Select Methods. You learn Excel search syntax, search and replace, and different search options such as specifying the area and scope (workbook, worksheet, or selection), wild cards, and match options like case-sensitive and exact matches.

What will I be able to do after taking this course?

You will understand Excel search syntax, describe Excel search and replace, and recognize different Excel search options.

Can Excel find every mention of a value, not just exact matches?

Yes. Excel provides partial matches to find all mentions of the string being searched, and it also supports match options such as case-sensitive and exact matches.

Which skills are associated with this course?

The course is associated with Microsoft Excel, Excel Macros, Excel Services, Excel Solver, Microsoft FAST Search, and Microsoft Office Specialist (MOS) - Excel.

What lessons are included?

The course includes three lessons: Find and Select; Search and Replace; and Additional Find and Select Methods.