Sorting data in Excel is a crucial part of organizing it for analysis or presentation. What if you want to order data in a different way, say “New York” first, and then “California”, and another state? One size may not always fit all as data can come in various types, and custom sorting features will come in handy in those situations.
Luckily, Excel has a plethora of options for different methods of sorting. In this article, we’re going to show you the best ways to organize data, how to sort in Excel, and walk you through available tools. You can download our sample workbook below.
Quick Sort
Excel’s ‘quick sort’ functionality, as the name suggests, is one of the quickest ways to sort your data. Click the cell or range containing your data, and click either the AZ or ZA button in the DATA tab.
Clicking one of these buttons will sort your data in the selected (ascending or descending) order. These buttons are very useful if you need to sort by a single column. As for how to sort in Excel in a more customized fashion, you need to use the Sort dialog box. The same quick sort buttons are available in right-click menu with other sorting options.
Sort Two or More Columns
Excel's Sort menu allows user to establish a multi-level sorting logic. Columns can be sorted in various types and order. You can activate the Sort dialog by clicking the Sort button next to the quick sort buttons.
In the Sort menu, each row represents a sort level, from top to bottom.
Use the dropdowns under the Column, Sort On and Order columns to determine the columns to be sorted and sorting methods to be applied. Click the Add Level button to add a new row of dropdowns for multi-level sorting. Clicking OK button will apply the sorting rules.
If My data has headers checkbox is not enabled, Excel will assign automated names and you might see names like "Column A, Column B, …" in the Column dropdown list.
How to Sort in Excel in a Custom Order
Not all data can be sorted from A to Z, or numerically. Common examples are dates, days, and months. To sort data in a custom order, use the "Custom List…" feature found in the dropdown list under the Order column.
A new dialog box named Custom Lists will pop up. It includes a list of custom lists at left and a multi-line textbox at the right. You can select an existing custom lists that includes month and day names by default. Or, you can add your own. While NEW LIST is selected at left panel, type your list to right. Press Enter button to separate each item and click Add button when your list is finished. Your new list will be added to the left panel.
Click OK to return back to the Sort menu. You will see the custom list in the dropdown. Click OK again to apply your settings.
Sorting a Row
Although spreadsheets and tables are commonly used in vertical order, you may need to sort data in horizontal fashion. To do this, you first need to go to the Sort dialog, and then click the Options button to open the additional options.
Choose Sort left to right option and click OK to apply horizontal sorting. This time you will see some changes in the Sort dialog box. The first column name is now changed to Row, and My data has headers checkbox will be disabled. If you click the dropdown under the Row column, you will see items like "Row 1, Row 2, …", so you need to know the exact row number of the data you want to sort. Other options will remain same as before.
Sort by Cell Color, Font Color or Selected Cell Icon
You can sort your data by the color, font, or icon of the cell. The best part is that sort by cell and font color options are also compatible with conditional formatting. These options can be accessed by the Sort dialog box and right-click menu.
Of course you can always use macros or third party tools to get similar effects, but Excel does a pretty good job at this anyways. Managing tables doesn't have to be a challenge, because you now know how to sort in Excel!
