In this article I will show you how to sort Excel data by several columns, by column names in alphabetical order and by values in any row. Also, you will learn how to sort data in non-standard ways, when sorting alphabetically or numerically does not work.
I believe everyone knows how to sort by column alphabetically or in ascending / descending order. All you need to do is click the A-Z or Z-A buttons residing on the Home tab in the Editing group and on the Data tab in the Sort & Filter group:
However, the Excel Sort feature provides far more options and capabilities that are not so obvious but may come in extremely handy:
Now I'm going to show you how to sort Excel data by two or more columns. I will do this in Excel 2010 because I have this version installed on my computer. If you use another Excel version, you won't have any problems with following the examples because the sorting features are pretty much the same in Excel 2007 and Excel 2013. You may only notice some differences in color schemes and dialogs' layouts. Okay, let's go ahead...
Sorting data by multiple columns in Excel is pretty easy, isn't it? However, the Sort dialog has plenty more features. Further on in this article I will show you how to sort by row, not column, and how to re-arrange data in your worksheet alphabetically based on column names. Also, you will learn how to sort your Excel data in non-standard ways, when sorting in alphabetical or numerical order does not work.
I guess in 90% of cases when you are sorting data in Excel, you sort by values in one or several columns. However, sometimes we have non-trivial data sets and we do need to sort by row (horizontally), i.e. rearrange the order of columns from left to right based on column headers or values in a particular row.
For example, you have a list of photo cameras provided by a local seller or downloaded from the Internet. The list contains different features, specifications and prices like this:
What you need is to sort the photo cameras by some parameters that matter the most for you. As an example, let's sort them by model name first.
The result of your sorting should look similar to this:
I know that sorting by column names has very little practical sense in our case and we did it for demonstration purposes only so that you can get a feel of how it works. In a similar way, you can sort the list of cameras by size, or imaging sensor, or sensor type, or any other feature that is most critical for you. For instance, let's sort them by price for a start.
What you do is go through steps 1 - 3 as described above and then, on step 4, instead of Row 2 you select Row 4 that lists retail prices. The result of sorting will look like this:
Please note that it's not just one row that has been sorted. The entire columns were moved so that the data was not distorted. In other words, what you see in the screenshot above is the list of photo cameras sorted from cheapest to most expensive.
Hope now you've gained an insight into how sorting a row works in Excel. But what if we have data that does not sort well alphabetically or numerically?
If you want to sort your data in some custom order other than alphabetical, you can use the built-in Excel custom lists or create your own. With built-in custom lists, you can sort by days of the week or months of the year. Microsoft Excel provides two types of such custom lists - with abbreviated and full names:
Say, we have a list of weekly household chores and we want to sort them by due day or priority.
That's it! Now we have our household tasks sorted by the day of the week:
Note. If you want to change something in your data, please keep in mind that new or modified data won't get sorted automatically. You need to click the Reapply button on the Data tab, in the Sort & Filter group:
Well, as you see sorting Excel data by custom list does not present any challenge either. The last thing that is left for us to do is to sort data by our own custom list.
As you remember, we have one more column in the table, the Priority column. In order to sort your weekly chores from most important to less important, you proceed as follows.
Perform steps 1 and 2 described above, and when you have the Custom Lists dialog open, select the NEW LIST in the left-hand column under Custom Lists, and type the entries directly into the List entries box on the right. Remember to type your entries exactly in the same order you want them to be sorted, from top to bottom:
Click Add and you will see that the newly created custom list is added to the existing custom lists, then click OK:
And here come our household tasks, sorted by priority:
When you use custom lists for sorting, you are free to sort by multiple columns and use a different custom list in each case. The process is exactly the same as we have already discussed when sorting by several columns.
And finally, we have our weekly household chores sorted with the utmost logic, first by the day of the week, and then by priority :)
That's all for today, thank you for reading!
Table of contents