Excel Sort: How to Get Your Data Super Organized!

Udemy Editor

Data analysis is an integral part of any business. MS Excel is the best spreadsheet tool available today to maintain and analyse data. If you’re a businesses owner, you’re probably already using Excel. For those who are new to it, you may want to check out this course on using Excel for Business.

In some situations, you may want to analyse the data on your spreadsheet in a certain order. Of course you don’t want to have to mentally sort and then re-enter the sorted data. Since this is quite a common case (as we show in this course) Excel has some methods inbuilt to do the work for you. In this article, we walk you through a few ways you can sort your data in Excel. We assume you have a working knowledge of Excel. If not you can always try out this basic course on Excel.

At times, you may want to sort the names in the alphabetical order or compile a list of product inventory from the highest to lowest based on the date of purchase. The Excel Sort command helps you organize, visualize and understand your data better. This will enable you to make effective decisions, based on your data. Let’s go explore some ways to do this.

How to Sort Text : Using the Sort Command

Select the column that contains the text values you want to sort. See the image below for an example:

Go to the “Data” tab and select the “Sort & Filter”. You’ll get a dialogue box asking details of how you want to sort the data. You can do one of the following:

If you want to sort in the ascending order click Sort A to Z. Here’s how your example will now look:

If you want to sort in the descending order click Sort Z to A.

You Can Also Do a Case-Sensitive Sort:

In the Sort dialog box, click Options button.

Select the Case Sensitive checkbox.

Click OK twice.

Reapply Sort Command:

If you happen to change data that you’ve already sorted, chances are you’d want to redo the sort. Here’s a simple way to do it:

Select the table.
Click “ Data” tab and select Sort & Filter group
Click Reapply option and confirm the change by clicking on “OK” buttons

Caution: If the column that you want to sort contains mixed data where some of the numbers are stored as numbers and others as text, then you should convert all the numbers to text. Else, the numbers stored as numbers will be sorted first and only then the numbers stored as text will be sorted. This can cause unwanted confusion. To convert all the numbers to text,

Select the cell range, right click -> Format Cell-> Number tab->Category-> Text->OK.

How to Sort Columns by Date in Excel

Click on the column where the data is date.

Click “Data” tab and then click on the “Sort” option.

In the Sort dialogue box, click on the radio button “Continue with the Current Selection” and then click on “Sort.”

Click on the arrow underneath “Order” and select either “Newest to Oldest” or “Oldest to Newest” depending on the ascending or descending order you want your dates to be sorted.

Ensure that the “Sort on” selection box reads “Values” before clicking “OK.”

Now you should have the column arranged according to the dates.

Caution: If the sorted dates don’t look right, you may want to check the format. There are times when your dates happen to be actually stored as text or numbers, and not as really as dates. To fix it, just select the entire column and change it to date format. If you’d like more information on how to do that, you can refer back to our earlier post on how to change the date format.

Also note, if you want the date to be sorted by day of the week, you have to convert the current date format to show the day of the week – before you sort it! But just in case you want to skip around that, and want to sort by the day of the week regardless of the format of the date, convert the dates to text. You can use the text function (for help on how to do that, look up our advanced Excel course here). However, remember that this function will return a text value, and the sort operation will be based on alpha-numeric data.

How to Sort by Cell Color, Font

You can sort the values of a range of cells or table column that was manually or conditionally formatted. Here are the steps:

Select the table column or range of cells.
On “Data” tab select Sort & Filter group and click on Sort.
In the dialogue box, under Column, in the Sort by box, select the column that you want to sort.
Under Sort On, Select the type of sort. Chose one of the following depending on your sorting action:

Select Cell Color
Select Font Color
Select Cell Icon

Under “Order”, click the arrow button and select one of the commands depending on how you want to sort.

To move the cell based on the font color, cell color or cell Icon either to top or left, select On Top for a column sort and On Left for a row sort.

To move the cell based on the font color, cell color or cell Icon either to bottom or right, select On Bottom for a column sort and On Right for a row sort.

Note that unlike the other sorts, there is no default cell icon, cell color, or font color to the sort order. You have to explicitly decide the “Order” for each sort operation.

How to Sort Rows

Select the rows that you want to sort.

On “Data” tab, in the “Sort & Filter” group, select Sort.

Now Click on “Options” button.

Under “Orientation”, click “Sort left to right” and click “OK” button.

Under “Column”, in the “Sort by” box, select the row that you want to sort.

As per your preference do one of the following:

“By Value”

Under “Sort On” option chose “Values.”

Under “Order”, do one of the following:

For text values, select “A to Z” or “Z to A.”
For number values, select “Smallest to Largest” or “Largest to Smallest”.
For date or time values, select “Oldest to Newest” or “Newest to Oldest”.

If you’ve been working with data for a while, you’ll understand the importance of the sort function and how important it is to get your data right! Choose the sort options carefully to help make your data analysis easier. You can check out some really cool Excel best practices in this course, that experts often use. One killer way to present your data is to use charts, and you may also want to try out this awesome course on how to make charts easily with Excel. Go ahead, try it out for yourself. Drop us a word in the comment section to let us know how you fared.