An Introduction to Excel Data Sorting Options

Where there is data, there will always be a need to sort it.

Excel gives you enough option to slide and dice your data, and a running knowledge of using Excel data sorting could be very useful.

In this tutorial, I will show you how to use Excel data filter options.

Accessing Excel Data Sorting Options

You can access the Filter and Sort option through the Excel Ribbon in the Data tab.
Excel Data Sorting - Filter and Sort in Excel Ribbon

Excel Data Sorting Using Ribbon Icons

Suppose you have the data as shown below:
Excel Data SortingNow this data can be sorted either based on Sales Rep Name (A to Z or Z to A) or by Sales Value (High to Low or Low or High).

To sort sales rep name from A-Z, select the data (without header) and click on A-Z or Z-A option in the ribbon.
Excel Data Sorting - Filter in Excel A to Z

You will notice that the Sales Rep names have been sorted. Since you selected the entire data, this also takes care of the Sales values. So when the sorting happens, the Sales value for each Sales Rep remains intact.

If you want to sort only the Sales Rep Name or the Sales value, select only that data and click on these buttons. You will notice that excel flashes a dialogue box with an option to expand the selection. Excel is smart enough to identify that there is more data than you have selected, and to ensure that you have not selected partial data by mistake, it shows the dialogue box. You can continue with the existing selection, or expand the selection.
Excel Data Sorting - Expand Selection

When you select only a part of the data (for example only Sales Rep Name or only Sales value) and sort it, only the selected data gets sorted (remaining data remains as is).

Excel Data Sorting Using Dialogue Box

Apart from A to Z, and values, there are multiple criteria you can use to sort data in Excel. These additional options can be accessed by clicking on the Sort option in Excel ribbon.
Excel Data Sorting - Excel Sort RibbonTo open the Sort dialogue box, first select the data and then click on the button. The sort dialogue box gives you access to more sorting options:

  • Adding additional sorting levels in data. For example, sort the data first by Month and then by Sales Value.
  • Apart from values, option to sort based on cell color, font color, or cell icon.

Excel Data Sorting - Sort Dialogue Box Excel

Adding additional sorting levels in data

Suppose you have the data as shown below:
Excel Data Sorting - Sort Add Level Excel

Adding another level of sorting could mean to first sort this data by Quarter, and then by Sales. It helps you in identifying who did the highest sales in each quarter, and also compare Sales Rep performance within a quarter.

To do this:

  • Select the data set (including headers).
  • Open the Sort Dialogue Box by clicking on its icon in the ribbon.
  • In the Sort dialogue box, select Sort by Quarter, sort on Values, and Order A to Z.
  • Click on Add Level button. This adds another level of sorting where you can specify the criteria.
  • In the second level of sorting, select Then by Sales, Sort on Values, and Order Largest to Smallest.
    Excel Data Sorting - Criteria Dialogue Box Additional Level
  • This would sort the data first by Quarter and then by Sales Value
    Excel Data Sorting - additional level output
Sorting on Cell Color

Excel allows you to sort on cell color, font color, or cell icons. The basic principle in these 3 is the same. I will show you how to sort based on cell color. Suppose, I have the same data set as above, but I color code it to highlight sales which I believe are more important in green, and the ones less important in red.
Excel Data Sorting - based on Cell Color Data

Here is how you can sort based on cell color:

  • Select the data set (including headers).
  • Open the Sort Dialogue Box by clicking on its icon in the ribbon.
  • In the Sort dialogue box, select Sort by Sales Rep Name, sort on Cell Color, Order green color, and On Top.
  • Click on Add Level button. This adds another level of sorting where you can specify the criteria.
  • In the second level of sorting, select Sort by Sales Rep Name, sort on Cell Color, Order red color, and On bottom.
    Excel Sort Dialogue Box Cell Color Value

This stacks the green color cells at the top and red color cells at the bottom
Excel Sorting Cell Color Value Output

Note:

  • If your data does not have headers, make sure that you have unchecked the option ‘My data has headers’ in the sort dialogue box.
  • You also get access to sorting options when you apply filter and click on the filter icon to display the options. 

You May Also Like the Following Excel Tutorials:

>