Sorting Data helps in organizing or arranging the data in a specified order to find values quickly. In Excel, you can sort a range or table based on texts, numbers, dates, and time on one or more columns. You can also sort data on basis of cell color, font color, or custom formatting icon.
Excel has several advanced in-built sorting options to help with data management. In this article, we will discuss the various methods of sorting data in a specified order as well as various criteria.
Sorting Data in Excel
Before you sort your data, you must choose whether if you want to sort the entire worksheet or just a cell range.
The sorting arranges all of the data in your spreadsheet by one column or multiple columns. You can also choose to sort only one column of the worksheet. You can sort your data on the basis of text, numbers, dates, and time.
We’ll use the following example to show you how to perform the different methods of sorting in Excel.
If you want to quickly sort your data, just click on any cell in the column that you want to sort. In the below example spreadsheet, we wish to sort our data by ‘Rep’ names. So, click on any cell in column B.
Go to ‘Data’ tab and click on ‘AZ’ sorting icon, which will sort your date by ‘Rep’ names.
Now, your data is sorted by Representative names. When the B column is sorted, rows corresponding to each cell in column B will move along with it.
Sorting Data in Only One Column
You can sort your data in one column, which will not affect adjacent columns on the worksheet. First, select the range/column (Item) you want to sort.
Then, go to the ‘Data’ tab and click the sorting icon. Excel will show you a ‘Sort Warning’ message. In that, select the ‘Continue with the current selection’ option and click ‘OK’.
Now, only the ‘Item’ range (column) is sorted in alphabetical order. Remember, sorting only one column will mismatch your whole worksheet with that column.
Sorting Data by Date/Time
You can sort date, time, number the same way you sort text in Excel. Numbers can be sorted from lowest to highest or highest to lowest, and dates and time can be sorted from newest to oldest or oldest to newest.
You can either select a cell in the column that you want to sort and click the ‘AZ’ or ‘ZA’ icon in the ‘Sort & Filter’ Group of Data tab to sort your data in ascending or descending order. Or click on the ‘Sort’ icon next to quick sorting icons in the ‘Data’ tab.
It will open up the Sort dialog window. Open the ‘Sort by’ drop-down menu, this drop-down menu will list all your column headers in the worksheet and since, we want to sort our data set by date, select the ‘OrderDate’ option. Then, select the ‘Newest to Oldest’ option in the ‘Order’ drop-down menu to sort the data in ascending order.
The result is shown in the following screenshot.
Sorting Data by Cell Color/Font Color/Cell Icon
To sort your data on cell color, font color, or cell icon, open the ‘Sort’ dialog box by clicking on the ‘Sort’ icon in the Data tab. Then, choose your option in the ‘Sort on’ menu. Here, we are choosing ‘Cell Color’ to sort the dataset.
Next, choose the color you want to be at the top of the column. Then, click ‘OK’.
This is how a table sorted based on cell color will look like.
Multiple Level Data Sorting (Sorting Multiple Columns)
Multiple-level sorting allows you to sort the data (table) by values of one column and then organize it again by values of another column(s).
For example, we are first sorting the data set by ‘Item’ name and then sort it again by ‘OrderDate’. To do that, select any cell in the table and click the ‘Sort’ Icon in the ‘Data’ tab.
In the ‘Sort’ dialog box. Select the ‘Item’ from the ‘Sort by’ drop-down list. Then click on ‘Add Level’ and select ‘OrderDate’ from the ‘Then by’ drop-down list. Click ‘OK’.
Now, the records are sorted by Item first and OrderDate second.
Sorting in a Custom Order
Sometimes you don’t want to sort by text, number, or date. Sometimes you want to sort by something else, such as months, days of the week, regions, or some other organizational system.
Let’s you have the following table and you want to sort it based on the priority of the orders.
To do that, click on any cell inside the data set and open the ‘Sort’ dialog box from the Excel Ribbon.
Then, in the ‘Sort by’ drop-down, select ‘Priority’; in the ‘Sort On’ drop-down select ‘Cell Values’; and in the ‘Order’ drop-down, select ‘Custom List’.
When you click on the ‘Custom Lists’ option from the drop-down menu, it will open the Custom Lists dialog box.
Here, type your custom list in the ‘List entries:’ and click ‘Add’. For example, we are adding High, Normal, and Low priorities to the list.
A new sorting order is now added to the custom list. Select the list and click ‘OK’.
Now, you can select your custom sorting order from the ‘Order’ drop-down.
Now, dataset is sorted by Priority (High, Normal, Low).
Sorting Data in a Row
Instead of columns, you can sort the data by rows. To do that select any cell in a row and click on the ‘Sort’ icon from the ‘Sort & Filter’ group in the Ribbon.
In the ‘Sort’ dialog box, click ‘Options’ button.
Select the ‘Sort left to right’ option instead of ‘Sort top to bottom’ under Orientation in the ‘Sort Options’ dialog box. And click ‘OK’.
Then, select the row you want to sort under ‘Sort by’ drop-down and click ‘OK’.
As you can see below, the dataset is now sorted by the values in the selected row.
Now you learned to sort in Excel, you can quickly reorganize your worksheet by sorting your data.