Video: Find and Replace

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.

Like the other applications in Microsoft Office 2007, Excel 2007 boasts upgraded features and a brand-new look. In Excel 2007 Essential Training , instructor Lorna A. Daly introduces the new version in detail. The training begins with the essentials of using the program, including how and why to use a spreadsheet, how to set up and modify worksheets, and how to import and export data. Lorna then moves on to teach more advanced features, such as working with functions and macros. Exercise files accompany the tutorials.

Find and Replace

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 spreadsheetwith other information. Why would you want to use this? Well, you would use it if you know that there's an error in your spreadsheetor if you've changed a particular Item Category to include more information, and you want to be able to do this veryquickly.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, youcan 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 havemore than one person inputting data into an Excel spreadsheet. You don't get consistency with some of the labels thatyou'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 toreplace 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'llnotice that it's changed fromWW Pastry to WholeWheat, so it takes the original information that it found, and replaces it with the new informationyou'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 youa 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 Findenvironment, 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 andmatching 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 Excelspreadsheet, and it saves you hours and hours of tedious adjustments of your data.

Q: When trying to apply the techniques from the “Relative and absolute referencing” video to a worksheet other than the exercise file included with the title, the formulas did not work for the entire worksheet. The formulas would only work when going through the worksheet row by row. What could be causing this to happen?

A: When trying to apply formulas to a whole workshee, here is a tip to try:

If you want to always refer to the same cell then use an absolute reference. For example, always pulling the value from cell A3 would be referenced as $A$3. This will never change no matter where you copy it to in the spreadsheet.

If you want to reuse the same formula, but with values in different cells, use the relative reference, A3. This way formula =A3*B3 will become =A4*B4 as you copy it down a column.

Q: In the chapter 7 video "Sorting and Grouping" at approximately 4:05, the author says to go to cell 5 on the worksheet and click on Subtotal to subtotal the grouping. My screen will not allow me to click on the Subtotal option at the top of the page. Is this an issue with my version of Excel?

A: It seems that there is an error in the instructions in this video. The video should have instructed users to do the subtotaling first, then create the table.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Already a member ?

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships.
Learn more

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.