Sort by cell color, font color, or cell icon - Microsoft Excel Tutorial

- [Instructor] Now, here are some really exciting features that you can use to sort your data. And I'm telling you, when you know how to sort, you can sort and filter your way to glory. It might be messy. But if you don't know how to write really complex formulas, if you can sort and filter, you can go a long, long way. So let's look at this data. We've got the car ID, the style, the transmission type, color, miles, the year, and in favorites. And in that favorite column, that's where I went in and manually put that flame icon with my favorites. Now, I'll scroll down. Okay, there we go. Brief look at that. In the year column, there are conditional formatting icons and then you see the cells that are highlighted gold. Those are also done via conditional formatting. So let's look at the conditional formatting rules. Go to the Home tab on the ribbon. Go to Conditional Formatting, Manage Rules. Okay, go here. This worksheet. The second rule, where it says cell value greater than or equal to 110,000. That's why those cells are highlighted gold because their mileage is 110,000 miles or above. Now what about this icon set rule? I'm selecting it. Now, Edit Rules so that we can look at it. If the year is 2015 or above, and an exclamation point if the car year is below 2015. OK. And OK. Now we understand what the rules are doing. Now, I am going... Now, okay... Now let's do some sorting. First of all, the data is one contiguous data set. There are no empty rows or columns. That is critical. Now go to Data. And then open the Sort dialogue box. Yes, the data does have headers. First, I want to sort and keep the gold highlights at the bottom. I'm going to sort by miles and it's given me the option to sort on the cell value smallest to largest. I want to sort by cell color. Let's open here. No cell color on top. That's what I want, but let's click here. The gold. Okay. I can say on bottom. All right. Let's add a level. Then sort by favorites, cell values. I need to sort this Z to A to ensure that the flame icons remain above the empty icons. Okay. Z to A. And then I want to sort by the style. Add a level. Sort by style. Leave it at A to Z and OK. Notice the cars that have a gold field for mileage, they are indeed at the bottom. The ones that I marked as favorites are on the top. And then of my favorites, we have coupe, sedan, and SUV all in order. And then in this group, it starts all over again. Minivan, sedan, SUV. I can even go back in. Go to Sort. I'm going to add a level. Notice that it put itself in this second position. All right, I want to sort by year and by the conditional formatting icon. The exclamation point, I want on the bottom. OK. And now this gives us a different view of our data. The highest mileage cars are at the bottom still. Now, it has split my favorites, okay. Because now the higher priority in the list is to split the years. The ones that are 2015 and younger and then the older ones. And then my favorites come into play. But let's do this. I want to highlight favorites, and then move it up. Now, OK. A different view of the data. So much you can do with this. And I encourage you to make some data and play with the interface. See what the different features do and how you can look at your data. All right, let's do one other thing. Here are some offices. I can look and see the Tokyo office opened in 2019, the manager is Raiko, the GMT is plus nine. Hmm. Let's say we want to sort this data left to right by the city named headers. I'm going to highlight this data. Now, it's important that I highlighted all of this. I couldn't just sit my cursor in a cell because we do not want the data in B4 to B7 to also shift on us. Okay, I've highlighted the data of interest. Go to Sort, Options, sort left to right, OK. Sort by what? By row three. Sorting A to Z? Yep. OK. And then highlight the entire sheet and then double-click to get all of the columns to auto-widen. All right. And there we are. All of the columns are in alphabetical order. And if anybody asks about Lima, Peru, we could tell them they are GMT minus five and the manager of the office is Vicente.