3 Ways to Sort your data in Excel

Sep 28, 2023 | Excel Hints and Tips

It’s always good to have choices when you are working in Excel.  There will be times when one option is better than another depending on exactly what you are trying to do.  So here are 3 ways to perform your sort.

Any of these methods can be used to sort text, numbers, dates or a combination of these into ascending or descending order.

Using the 2 Sort icons

The quickest way to sort is using the sort icons.  The data must have headings in the top row and there cannot be any blank rows or blank columns.

  1. Click in the column you want to sort by
  2. Go to the Data tab
  3. Use one of the 2 Sort icons below to sort ascending or descending respectively
Ascending and Descending icons image

This method will not work with blank rows or columns in the data as Excel will assume that this is the end of the data so part of it will be sorted and the other part won’t.  This will cause your data to be out of sync.

Using the individual Sort icon

This gives more flexibility to your sort.  The data doesn’t have to have headings and can have blank rows and columns.  It also allows for a multi category sort to be performed amongst other functionality.

  1. If the data has no blank rows or columns then clicking in one cell in the data will suffice.  If the data has blank rows and columns then all the data must be selected
  2. Go to the Data tab
  3. Click on the Sort icon below
Sort icon image

4. By default the assumption is that the data has headers. This can be unticked if it doesn’t

Sort dialog box image

5. Choose the column to be sorted by and whether this is ascending or descending

6. Add level can be used to add further sorting levels if required.

7. Click on OK

Sort using Dynamic Array Functions

Dynamic array function are different to normal functions in that the result of the function is not an individual cell but a range of cells.  There are two functions that can be used to sort the data which are SORT and SORTBY.  The advantage of using functions is that when the data changes the sorted data automatically updates instead of manually having to sort it again.  The disadvantage is that you end up with 2 sets of the data, one sorted and one not.

SORT Function

The SORT function can sort the data into ascending or descending order and the data can be sorted by row or by column.

The structure of the function is:

=SORT(Array, Sort index, Sort order, By col

Where the array is the range of data, sort index is the row or column to sort by, sort order to sort into ascending or descending order and by col to choose where the data is sorted by row or by column

SORTBY Function

The SORTBY function can sort the data only by rows but can have multiple criteria.

The structure of the function is:

=SORTBY(Array, By array 1, Sort order 1, By array 2, Sort order 2,………….)

The By array and sort order can be repeated many times, or just once depending on how many levels of sorting are required.

The array is the range of data, the by array is the column of data to be sorted by and the sort order determines whether the data is sort into ascending or descending order.

A more detailed explanation of these functions including examples can be seen in the Further Reading links below.

Further Reading

If you’ve enjoyed reading about ways to sort your data in Excel, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.