In this tutorial, I will be teaching you guys how to sort in Microsoft Excel. The sorting function allows you to change the order of, or rearrange the data in your spreadsheet. Note that this function works on columns (the vertical array) by default. It doesn’t work on rows (the horizontal array) unless you’re doing Custom Sort.
In the later versions of MS Excel (such as 2015 and 2016 versions), the sort function can be found under the Home tab, at the upper right corner. See picture below:
Another way to access it is by right-clicking on a column (preferably the column header – the very top of the column). After right-clicking, go to Sort, this will open a sub menu and give you different sorting options. See picture below:
The third option would be the quick access toolbar located at the topmost part of the application. But this toolbar does not offer all the sorting options.
Now, there are different kinds of sorting methods, I will explain them below one by one. These options will give you different results.
Sort Alphabetically / Sort Smallest to Largest and Sort Largest to Smallest
There is this option called Sort Smallest to Largest and Sort Largest to Smallest. These apply both to numbers and alphabets (alphanumeric). Just as the name, it sorts the data in those order – from smallest to largest (A-Z, 0-9) for Sort Smallest to Largest, and largest to smallest (Z-A, 9-0) for Sort Largest to Smallest.
For example, we wanted to sort the name of the brands in the order from A-Z. To do that, you pick Sort from Smallest to Largest from the sort option and the result would be this:
And if you wanted to sort it in the order of Z-A, pick Sort from Largest to Smallest, and the result would be this:
The same happens to numbers, it can be sorted from the smallest to largest value or vise versa:
Note that if you Sort a certain column, for example, you sort Column B (SKU) as shown above. It will also affect the order of the other columns. In other words, you can only sort one column per Excel sheet. This is most logical considering that the value of each row in the spreadsheet corresponds to the value of every column in a particular row. For instance Adidas has SKU 190, while Nike has 213. If the values don’t change along with the sorting, then their SKUs could end up getting swapped with the other brands and you will end up with a mess up data.
It is also worth noting that this Sorting Method also works with dates. From older to most recent for Sort Smallest to Largest, and from most recent to oldest date for Sort Largest to Smallest.
Sort by Color
Another way to sort data is by color. This can be done in two ways. Either by Cell Color or by Font Color. The faster way to do this would be to right-click on a colorized cell or on a cell with a specific font color. Let’s try the colorized-cell sorting first, see picture below:
In the example above, we right-click on the yellow-colored cell. Next, we went to Sort and we found the option “Put Selected Cell Color On Top”. If you select this option, this is what will happen:
As you can see in the picture above, all the cells with yellow-color fill went up there.
Now, this time, let’s pick the red-colored cell and select “Put Selected Cell Color On Top”. This is what will happen:
And as you can see, the selected red-colored cell went on top.
Technically, what this sort option does is put the selected cell color on top regardless of the value. It based its sorting on the color fill of the cell.
The same happens when you use the option “Put Selected Font Color On Top”. The only difference perhaps is that this option bases its sorting on Font color instead of the cell color. See picture below:
For now, we’ll end our tutorial here. I will tackle the remaining sorting options “Put Selected Cell Icon On Top” and Custom Sort… maybe in the future posts. I would like to separate these two because these require special formatting, which can be a bit complicated.
Finally, for practice purposes, I have shared an Excel Spreadsheet here: (see link below)
Excel Practice Spreadsheet
Here’s what you can do with it:
– Try to Sort the brands in order of A-Z
– Try to Sort the inventory from largest to smallest.
– Try to Sort the red-colored cell and make it appear on top.
– Color the font of some values on the spreadsheet and make them appear on top.
– Sort the date from most recent to oldest.
Good luck…
Follow me at:
