Most of the data we encounter in Excel, is in the form of a list. The columns may contain different data types, but generally the different lines for data or transactions are listed vertically.
Sorting data top to bottom
Sorting data in these lists is easy, it’s right there in the Ribbon.
All we have to do is select the data to be sorted, ensuring the first column selected is the column to be sorted. Click Data -> Sort & Filter -> A-Z (or Z-A).
The list will then be sorted.
If we want a more advanced sort, which sorts columns in a specific order, that is possible too. Click Data -> Sort & Filter -> Sort.
The Sort window will open. In here, we can add new columns to be sorted, decide how to sort (i.e. by values, by colors etc), the order to sort (A to Z, or Z to A), and which order the apply the sort to each column.
Sorting data left to right
But, sometimes we want to sort from left to right, rather than top to bottom.
Look at the data below.
Oh great! Somebody has sent us some data with the month names ordered alphabetically. This data is almost useless in this format. The good news is, we can use two time saving tricks to correct this data (1) sorting left to right (2) sorting using a Custom List.
Select the column headings and the values. Click Data -> Sort & Filter -> Sort.
In the Sort window click the Options… button (you’ve never noticed that before, have you?). The Sort Options window will appear, select Sort left to right. Click OK to close the Sort Options.
Next, choose the Row number you wish to sort, then select Custom List… from the Order drop down list.
The Custom Lists window will appear. As the months of the year is already a Custom List we can select it, then click OK.
Click OK in the Sort window. Ta Dah! The data has now been sorted correctly.
- Sorting left to right does not work when using data in an Excel Table. It will be necessary to covert it to a normal range before sorting, then converting it back to a Table.
- Custom Lists can also be used on vertical lists.
- The “My data has headers” option becomes greyed out when you are sorting from left to right.
- To sort by anything other than A-Z or Z-A there must be a Custom List created.
Get Excel news, tips & tricks straight to you inbox. Helping you to save time and achieve more with Excel.
You will also receive the Tab Hopper Add-in for FREE.