Filtering and Sorting in Excel

Where there is data, there will always be a need to filter it and sort it. Excel gives you enough option to slide and dice your data, and a running knowledge of using filtering and sorting in Excel could be very useful.

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

Sorting in Excel

Suppose you have the data as shown below
Excel Sort DataNow 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
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 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 a dialogue box. You can continue with the existing selection, or expand the selection.
Excel 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, the remaining data remains as it is.

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 Sort RibbonThis opens 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

Sort Dialogue Box Excel

Adding additional sorting levels in data

Suppose you have the data as shown below:
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 Sorting Criteria Dialogue Box Additional Level
  • This would sort the data first by Quarter and then by Sales Value
    Excel 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 coded it to highlight sales which I believe are more important in green, and the ones less important in red
Excel 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

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)