Hi, this is Wayne again with a topic “Sorting in Excel”.
In this Excel tutorial, I’m gon na take a closer look at how the sorting features work in Excel, and you can see the spreadsheet I’m using for this. It’S a list of synth-pop NuWave, electronic music and a list of albums that have been released. These are all more recent examples of synth pop new wave, etc, but anyway, this spreadsheet is for a hypothetical synth pop store that would sell music like this in CD format. I know everybody says: CDs are no longer relevant, but I beg to differ, and so this hypothetical company wants to track their inventory, their sales and the price of each item and the data here.
You’Ll notice is in table format, it’s been put into a table and that is kind of helpful when you’re doing sorting it’s not necessary, it’s not required, but it does save you at times some headaches, so you may want to from time to time, use insert table And you can watch some of my other videos to learn about tables in Excel, but whether or not you’re using a table. If you want to sort – let’s say by album title, there’s a couple of ways. You could do that a lot of people click here on the column, letter B in this case, and then they go here to the right and they click on sort and filter, but notice, that’s not an option for me. It’S grayed out and I think the reason why is because my data is in a table if it wasn’t in a table. It would allow me to do that, but you know what that’s a mistake anyway, because it would at least attempt to sort just this column, and I don’t want just this column sorting. I want all of the data to stay together. In other words, I don’t want this text moving down while the text to the left and the right of its stay, where they are okay, so I’m gon na recommend that you not sort this way by clicking on the column title. Instead, what you should do is click anywhere in the column anywhere except the column heading so album in this case. I don’t want to select that anywhere else, though I’ll just click here, the blinding dark, which is one of the more recent albums by covenant.
Now that I’ve selected, that I could just click on sort and filter sort, a to Z and look what happened all of the data got resources, the album title and in alphabetical order, and yet the column heading album stayed where it was and the reason why is Because I did not select the entire column, I just clicked somewhere in the column. Let’S try it again this time here in band. If I want to alphabetize by band, I just click anywhere in column, a except, not a one, and then I click sort and filter a to Z and it worked now. Let’S say I have two albums by Brandon Flowers that are for sale.
So I need to right click here on 4 and click insert, it gave me a new row and I’ll just copy Brandon Flowers, down and I’ll put in the name of another album from him called flamingos and I’ll just put in the rest of this data here. So now watch what I can do. I could sort by album if I click sort and filter a to Z, and then I could sort by band sort by band a to Z and now look what it did. Yes, it alphabetized and sorted my data from A to Z based on the band but notice that it flipped the order of these two brand and flower albums now flamingo is on the top and desired effect is underneath. The reason why is because Excel remembers that I sorted first by album and then I sorted by band whatever your most recent sort is that’s the one that takes priority, and so the priority here is to alphabetize by band. But there is a secondary priority, a secondary sort, which was Excel remembered that I sorted by album name too, and so you can get some pretty interesting sorts going. For example, I could sort again this time based on the genre, so I click sort A to Z and, of course, in addition to using this Home tab home, ribbon editing group, instead of using that, if you want, you can go to data and click sort. A to Z and I’ll do it that way, this time so I’ll sort, A to Z and now it’s sorting by genre? Ok, so I can see those all listed there, but then I could sort by band, and so you get a combination of the two sorts in this case.
It didn’t make that much difference, but with a larger spreadsheet. It certainly would now before we end this tutorial. There is another feature that you should know about, and that is here on the data, tab, data ribbon, sort and filter group. You can see that there is a sort button and, if you click on that, it gives you some additional options before I click on it, though, I want to select all of my data, so I’m going to hold the control key on the keyboard and tap a So that selects all of my data, if you want to make sure it got all of your data, you can hold ctrl and tap the period button. So I tap it once it shows me the upper left. It shows me the upper right.
If I tap it again, if I do control period again, the lower right again, the lower left, so it’s a way to see everything and to make sure. Okay, that’s all of my data. Everything is where it should be.
It’S all selected now that I’ve got that. I’M gon na click here on this larger sort button, and it gives me a bunch of ways that I can sort my data. I want a first sort by band and I want to sort it on the values, in other words, in this case the letters and how alphabetically a-to-z I could do Z to a if I want you can also sort not by the values, but by the cell.
Color, I don’t necessarily recommend that, but you could – and there are some other ways that you could sort as well alright, so now that I’ve got that set up, I can add a second level of sort. So first I want to sort by band then by genre and again, values a to Z and then maybe a third level of sort and maybe that’s by the number of copies sold from largest to smallest. So you click OK and it puts it all into effect and in this case again it didn’t make huge changes for me.
But in the case of a tie like this Brandon Flowers, for example – that’s a tie, it sorted them alphabetically and it’s a tie. It breaks the tie by looking at the genre in this case they’re both the same and then, if those are a tie, it breaks the tie again this time with, I think I said, copies sold from largest to smallest. So if you really want to get fancy with swords, that’s how you do it, you select all your data and then you go here to the sort button and set up a multi-level sort thanks for watching this video, I hope that you found it to be useful If you did, please click the like button below and consider connecting with me on my social media websites like Facebook, Pinterest and Twitter, and definitely do subscribe to my YouTube channel for more videos about technology for teachers and students and watch for another video. From me. At least every Monday, if you’d, like more information about these bands that I’ve mentioned here and their latest albums, please look in the description below and if you’d like to support my youtube channel, please consider becoming a patron of mine through my patreon account and you’ll find Links to that also in the description below .