1 00:00:00,05 --> 00:00:01,03 - [Instructor] We've spent all that time 2 00:00:01,03 --> 00:00:03,00 putting data into Excel. 3 00:00:03,00 --> 00:00:05,07 Now let's do some sorting so that we can analyze it 4 00:00:05,07 --> 00:00:07,07 and get the data out. 5 00:00:07,07 --> 00:00:10,08 I'd like to sort by size so that I can quickly see all 6 00:00:10,08 --> 00:00:12,01 of my eight ounce olive oils 7 00:00:12,01 --> 00:00:15,03 and my 64 ounces altogether in one place. 8 00:00:15,03 --> 00:00:17,06 Excel makes it easy. 9 00:00:17,06 --> 00:00:20,06 I did want to point out that over here on the right hand side, 10 00:00:20,06 --> 00:00:22,04 I have a little column of sizes. 11 00:00:22,04 --> 00:00:25,05 I don't want to sort that, so you'll need to be aware 12 00:00:25,05 --> 00:00:28,00 that if there's any data you don't want to sort, 13 00:00:28,00 --> 00:00:31,00 you either have to select everything ahead of time 14 00:00:31,00 --> 00:00:35,00 or make sure there's at least one empty column of space 15 00:00:35,00 --> 00:00:38,04 in between that other column that you don't want to sort. 16 00:00:38,04 --> 00:00:39,07 Something else that you can do 17 00:00:39,07 --> 00:00:42,07 is check the boundaries of what you're sorting. 18 00:00:42,07 --> 00:00:44,04 Excel is smart enough to know 19 00:00:44,04 --> 00:00:46,01 that any adjacent columns 20 00:00:46,01 --> 00:00:48,05 will also be included in that sort. 21 00:00:48,05 --> 00:00:50,04 So when I sort by size, 22 00:00:50,04 --> 00:00:52,03 it's automatically going to rearrange 23 00:00:52,03 --> 00:00:53,06 all of the other columns 24 00:00:53,06 --> 00:00:57,01 and keep everything all together on their respective rows. 25 00:00:57,01 --> 00:00:58,09 If you ever want to make sure, 26 00:00:58,09 --> 00:01:01,07 just hit control A on your keyboard. 27 00:01:01,07 --> 00:01:03,08 That's going to select all of the data, 28 00:01:03,08 --> 00:01:08,06 but you can also hit control plus the period key. 29 00:01:08,06 --> 00:01:10,03 And as I keep hitting this, 30 00:01:10,03 --> 00:01:13,03 it's going to move around to every boundary. 31 00:01:13,03 --> 00:01:15,02 You can see the top right boundary, 32 00:01:15,02 --> 00:01:17,00 and here's the bottom ones. 33 00:01:17,00 --> 00:01:18,05 This will help you make sure 34 00:01:18,05 --> 00:01:22,04 it's going to sort exactly what you want it to. 35 00:01:22,04 --> 00:01:25,00 Now I'll click inside 36 00:01:25,00 --> 00:01:27,05 anywhere in this column that I want to sort by. 37 00:01:27,05 --> 00:01:32,03 And on the home ribbon tab, I'll click sort and filter. 38 00:01:32,03 --> 00:01:36,00 I would like to sort this smallest to largest. 39 00:01:36,00 --> 00:01:37,05 Excel is smart enough to realize 40 00:01:37,05 --> 00:01:41,03 that the column was numerical, and instantly, it sorts. 41 00:01:41,03 --> 00:01:43,00 It keeps everything else together 42 00:01:43,00 --> 00:01:45,08 so that all of my columns and rows match up. 43 00:01:45,08 --> 00:01:50,00 And here's my 8 ounce data, my 16 ounce, and so on. 44 00:01:50,00 --> 00:01:52,07 You can even sort by more than one column. 45 00:01:52,07 --> 00:01:55,00 That's called a secondary sort. 46 00:01:55,00 --> 00:01:56,07 In fact, you could keep going 47 00:01:56,07 --> 00:01:59,01 and add more columns to sort by. 48 00:01:59,01 --> 00:02:01,05 Let's check out this worksheet, 49 00:02:01,05 --> 00:02:04,02 which contains an employee directory. 50 00:02:04,02 --> 00:02:06,02 I'd like to sort this by department, 51 00:02:06,02 --> 00:02:08,04 and then I'd like to put a secondary sort on it 52 00:02:08,04 --> 00:02:10,02 to sort by extension. 53 00:02:10,02 --> 00:02:12,01 This is a great example to show you, 54 00:02:12,01 --> 00:02:14,07 because not only can you sort alphabetically, 55 00:02:14,07 --> 00:02:16,05 you can also sort numerically, 56 00:02:16,05 --> 00:02:19,02 and even in the same sort algorithm. 57 00:02:19,02 --> 00:02:22,00 So what I'll do first is select my department, 58 00:02:22,00 --> 00:02:23,08 and then on the home ribbon tab 59 00:02:23,08 --> 00:02:25,08 I'll click sort and filter again. 60 00:02:25,08 --> 00:02:29,04 But this time, I'm going to choose custom sort. 61 00:02:29,04 --> 00:02:31,06 Now, you'll immediately notice 62 00:02:31,06 --> 00:02:35,04 that there's a section here that says "My data has headers", 63 00:02:35,04 --> 00:02:37,04 and it has a check mark next to it. 64 00:02:37,04 --> 00:02:39,06 Excel put that check mark on it 65 00:02:39,06 --> 00:02:42,04 because it detected that I have these header titles here, 66 00:02:42,04 --> 00:02:44,07 and it's going to leave that out of the sort 67 00:02:44,07 --> 00:02:48,03 so it doesn't include it and sort by the word "department". 68 00:02:48,03 --> 00:02:50,02 So if you don't have headers, 69 00:02:50,02 --> 00:02:53,02 make sure you don't have a check mark next to this. 70 00:02:53,02 --> 00:02:55,02 Here's all my columns, 71 00:02:55,02 --> 00:02:58,08 and I can come down here and choose to sort by department. 72 00:02:58,08 --> 00:03:02,09 It's even taken those headers and put them in this field. 73 00:03:02,09 --> 00:03:05,03 I do want to sort on cell values, 74 00:03:05,03 --> 00:03:08,04 although if I change the cell color or the font color, 75 00:03:08,04 --> 00:03:10,00 I can even sort on those. 76 00:03:10,00 --> 00:03:11,00 And we're going to talk about 77 00:03:11,00 --> 00:03:12,09 conditional formatting in a little bit. 78 00:03:12,09 --> 00:03:15,07 You can even sort on those icons. 79 00:03:15,07 --> 00:03:21,06 And finally, the sort order, I would like it to be A to Z. 80 00:03:21,06 --> 00:03:24,07 Here's where I'm going to choose add level. 81 00:03:24,07 --> 00:03:27,01 So first we're going to sort by department 82 00:03:27,01 --> 00:03:29,07 and then by extension. 83 00:03:29,07 --> 00:03:31,05 And again, I'm going to sort on the values 84 00:03:31,05 --> 00:03:32,07 that are in the cell. 85 00:03:32,07 --> 00:03:36,07 And because Excel has detected that extension is numerical, 86 00:03:36,07 --> 00:03:40,00 it's already asking me to sort by smallest to largest, 87 00:03:40,00 --> 00:03:42,07 though I could change that if I wanted to. 88 00:03:42,07 --> 00:03:44,01 Now I could keep going. 89 00:03:44,01 --> 00:03:47,01 I could click add level and sort by any of these columns, 90 00:03:47,01 --> 00:03:51,03 but I'm going to leave it at that and click okay. 91 00:03:51,03 --> 00:03:54,01 It instantly rearranged all my data. 92 00:03:54,01 --> 00:03:55,05 Here's my departments, 93 00:03:55,05 --> 00:03:59,03 and those departments are sorted by extension. 94 00:03:59,03 --> 00:04:02,06 For example, the facilities team is much smaller, 95 00:04:02,06 --> 00:04:05,07 and I can see that those are still in order. 96 00:04:05,07 --> 00:04:08,09 At any time, I can change the sort order. 97 00:04:08,09 --> 00:04:12,09 Suppose I wanted this sorted alphabetically by last name. 98 00:04:12,09 --> 00:04:16,01 I'll select this column, click sort and filter, 99 00:04:16,01 --> 00:04:20,00 choose from A to Z, and now it's sorted.