1 00:00:00,05 --> 00:00:02,09 - [Instructor] You can search for data in an Excel file 2 00:00:02,09 --> 00:00:04,08 and you can also change data. 3 00:00:04,08 --> 00:00:06,09 Let's take a look at this file. 4 00:00:06,09 --> 00:00:08,09 This one has two sheets: 5 00:00:08,09 --> 00:00:11,03 Sales Revenue that contains some products, 6 00:00:11,03 --> 00:00:12,09 product numbers, and prices; 7 00:00:12,09 --> 00:00:17,01 and also an Inventory tab that contains similar columns, 8 00:00:17,01 --> 00:00:22,04 except it also contains some inventory numbers. 9 00:00:22,04 --> 00:00:25,05 I want to search for an actual product number. 10 00:00:25,05 --> 00:00:27,06 I can select this entire column 11 00:00:27,06 --> 00:00:30,05 by clicking right on the column letter. 12 00:00:30,05 --> 00:00:32,09 From here, on the Home ribbon tab, 13 00:00:32,09 --> 00:00:34,06 I'll click Find and Select 14 00:00:34,06 --> 00:00:37,00 all the way on the top right-hand side of the screen. 15 00:00:37,00 --> 00:00:39,09 From here, I'll choose Find. 16 00:00:39,09 --> 00:00:43,01 In the Find what, I can put what I'm looking for. 17 00:00:43,01 --> 00:00:47,04 For example, maybe I'm looking for a specific model number. 18 00:00:47,04 --> 00:00:50,04 I can either click Find All if there's more than one 19 00:00:50,04 --> 00:00:52,00 or Find Next 20 00:00:52,00 --> 00:00:57,01 to take me to the next instance of that occurring. 21 00:00:57,01 --> 00:00:59,04 Here it is, and I can select the row, 22 00:00:59,04 --> 00:01:00,08 and I can see the product number 23 00:01:00,08 --> 00:01:04,04 and all the details that I was looking for. 24 00:01:04,04 --> 00:01:07,01 I don't have to search just a column. 25 00:01:07,01 --> 00:01:11,09 I can search the entire sheet or even the entire workbook. 26 00:01:11,09 --> 00:01:14,09 Let's say I want to find all the eBooks 27 00:01:14,09 --> 00:01:20,04 that's in the this product category. 28 00:01:20,04 --> 00:01:23,08 As you can see, there's quite a lot of them. 29 00:01:23,08 --> 00:01:28,04 I'll go back to Find and Select and choose Find again. 30 00:01:28,04 --> 00:01:32,08 This time, I'll type eBooks. 31 00:01:32,08 --> 00:01:34,05 I'm going to click Options 32 00:01:34,05 --> 00:01:37,00 because there's some more things that you can search on. 33 00:01:37,00 --> 00:01:39,03 If case matching is important, 34 00:01:39,03 --> 00:01:40,05 sometimes it makes a difference 35 00:01:40,05 --> 00:01:42,06 between finding things and not finding things, 36 00:01:42,06 --> 00:01:44,09 you can place a check mark here. 37 00:01:44,09 --> 00:01:46,06 You can also place a check mark 38 00:01:46,06 --> 00:01:49,05 to match the entire cell contents. 39 00:01:49,05 --> 00:01:51,03 You'll see here in this first column 40 00:01:51,03 --> 00:01:53,01 that it's called eBooks, 41 00:01:53,01 --> 00:01:56,00 but if I wanted to, I could just search for eBook, 42 00:01:56,00 --> 00:02:00,01 and without that checked, it would also return those values. 43 00:02:00,01 --> 00:02:03,07 And finally, do I want to search within the sheet? 44 00:02:03,07 --> 00:02:07,02 Because remember, this tab is called a worksheet, 45 00:02:07,02 --> 00:02:09,07 or if I click the pull down, 46 00:02:09,07 --> 00:02:12,06 I can have it search the entire workbook. 47 00:02:12,06 --> 00:02:15,02 That is, it will search all of the sheets 48 00:02:15,02 --> 00:02:19,00 that are in this workbook. 49 00:02:19,00 --> 00:02:21,03 I'll click Find Next, 50 00:02:21,03 --> 00:02:22,05 and it's going to take me 51 00:02:22,05 --> 00:02:24,06 to the next occurrence of that phrase, 52 00:02:24,06 --> 00:02:28,02 starting from where my cursor was. 53 00:02:28,02 --> 00:02:29,02 Now, if you remember, 54 00:02:29,02 --> 00:02:32,06 my cursor was here, somewhere in the middle of the column. 55 00:02:32,06 --> 00:02:36,02 So that was all of these eBooks up here that it didn't find. 56 00:02:36,02 --> 00:02:38,09 But don't worry, once it gets to the end of the search, 57 00:02:38,09 --> 00:02:40,06 it will come around again. 58 00:02:40,06 --> 00:02:43,03 So, I'm not going to completely miss those, 59 00:02:43,03 --> 00:02:44,02 but it is important 60 00:02:44,02 --> 00:02:46,08 to have your cursor where you want it to start from 61 00:02:46,08 --> 00:02:50,00 if you want to look right from the beginning of the file. 62 00:02:50,00 --> 00:02:51,06 Now, something else that's really useful 63 00:02:51,06 --> 00:02:54,08 with finding and selecting is changing values. 64 00:02:54,08 --> 00:02:56,04 Sometimes, you can take a spreadsheet 65 00:02:56,04 --> 00:03:00,00 and save it as something new and just change a few values. 66 00:03:00,00 --> 00:03:03,06 For example, maybe everything that was 36.99 67 00:03:03,06 --> 00:03:07,03 is increasing by $10 to 46.99. 68 00:03:07,03 --> 00:03:09,06 You can replace that text. 69 00:03:09,06 --> 00:03:12,02 In this case, our eBooks category 70 00:03:12,02 --> 00:03:15,01 is changing to be e-Books, 71 00:03:15,01 --> 00:03:18,01 so we can change everything at once. 72 00:03:18,01 --> 00:03:19,07 I'll click Find and Select, 73 00:03:19,07 --> 00:03:24,06 and this time, I'm going to choose Replace. 74 00:03:24,06 --> 00:03:26,01 eBooks is already in there 75 00:03:26,01 --> 00:03:28,04 because that was the last search I did, 76 00:03:28,04 --> 00:03:33,04 and I'm going to replace it with e-Books. 77 00:03:33,04 --> 00:03:36,00 I do want it to search the entire workbook 78 00:03:36,00 --> 00:03:39,03 because eBooks is in both tabs here. 79 00:03:39,03 --> 00:03:43,00 I'm going to choose Replace All. 80 00:03:43,00 --> 00:03:46,06 It's telling me that it made 37 replacements. 81 00:03:46,06 --> 00:03:48,00 I'll click OK. 82 00:03:48,00 --> 00:03:50,05 I'll click Close to close out of this dialog box. 83 00:03:50,05 --> 00:03:52,09 And now, let's take a look. 84 00:03:52,09 --> 00:03:55,07 Here's my replaced text. 85 00:03:55,07 --> 00:03:58,08 I can scroll through, and everything should be changed. 86 00:03:58,08 --> 00:04:01,03 Let's check the other tab. 87 00:04:01,03 --> 00:04:02,05 It's the same thing. 88 00:04:02,05 --> 00:04:04,07 It replaced everything. 89 00:04:04,07 --> 00:04:06,03 If eBooks occurred in this column, 90 00:04:06,03 --> 00:04:07,08 it would also be changed. 91 00:04:07,08 --> 00:04:10,07 Anywhere that it had that original search phrase, 92 00:04:10,07 --> 00:04:12,00 it's going to replace it.