Microsoft Excel is a powerful data management tool that can help you organize and analyze your data. Here are seven ways to sort data in Microsoft Excel:
- Use the Sort function. This lets you sort data by a specific column or range of columns. You can also use the Sort buttons on the Data tab of the ribbon.
- Use the Filter function to sort data by a specific condition. For example, you can filter data to show only values that are greater than or equal to 10,000.
- Use the Sort & Filter combo box to sort and filter data at the same time.
- Use the QuickSort button on the Home tab of the ribbon to quickly sort your data in ascending or descending order based on one or more criteria (such as column name, value, or type).
- Use drag-and-drop sorting to move sorted columns and rows around in your spreadsheet window. You can also use keyboard shortcuts (such as Ctrl+A for ascending order and Ctrl+Z for descending order) to quickly move columns and rows around your spreadsheet window.
- Use custom sorting rules in Microsoft Excel formulas (see below). This lets you create customized sorting orders for your data without having to use any buttons or filters on the Data tab of the ribbon!
- Export your sorted data as a comma-separated value (.CSV) file for further analysis or import it into another spreadsheet application (such as Google Sheets) for further analysis and collaboration with other users of that application ..
You can use the quick sort for those basic options or create a custom sort that gives you more flexibility. Whatever your data, be it text or numbers, take a look at these various ways you can sort it.
A to Z Sorting
If you want to sort by text in alphabetical order or numbers like currency or decimals in numerical order, you can do this in Excel in just a few clicks.
RELATED: How to Sort Values in Microsoft Excel
Select the data you want to sort and open the Sort tool one of these ways:
On the Home tab, select “Sort & Filter” in the Editing section of the ribbon. At the top of the pop-up box, choose “Sort A to Z” or “Sort Z to A. ” On the Data tab, select “A – Z” or “Z – A” in the Sort section of the ribbon.
Your data is then sorted in the order you picked, either alphabetically from A to Z or the reverse. This option also sorts numbers the same way, from lowest to highest or the opposite.
You can also use the A to Z sorting when you create a custom sort which we’ll describe next.
Row Sorting
For more advanced options in Excel like sorting by row instead of column, you can create a custom sort.
Select your data, go to the Data tab, and click “Sort” in the Sort & Filter section of the ribbon. Then, click “Options” at the top of the Sort box.
In the small pop-up box, mark the Sort Left to Right option, check the box for Case Sensitive at the top if you like, and click “OK.”
You can then continue setting up your sort by row with the drop-down boxes. Depending on the options you pick in the first drop-down boxes, you’ll be able to sort from A to Z, top to bottom, or another option.
Color Sorting
Also using the custom sort option in Excel, you can sort by color. This allows you to sort your data by the cell or font color. Select your data and then “Sort” on the Data tab to open the Sort box.
RELATED: How to Sort by Color in Microsoft Excel
Choose the following in the drop-down boxes:
Sort by: Pick the column or row. Sort on: Pick either “Cell Color” or “Font Color” depending on what you want to sort. Then, use the additional box that appears to select the color. Order: Pick “On Top” or “On Bottom” per your preference.
Click “OK” when you finish, and you’ll see your data sorted by your chosen color.
Conditional Formatting Icon Sorting
If you take advantage of displaying icons for your data in Excel based on conditional formatting, you can use this as a sort option too. Select your data and then “Sort” on the Data tab to open the Sort box.
RELATED: How to Use Icon Sets to Represent Values in Microsoft Excel
Choose the following in the drop-down boxes:
Sort by: Pick the column or row. Sort on: Pick “Conditional Formatting Icon” and then use the additional box that appears to pick the icon. Order: Pick “On Top” or “On Bottom” per your preference.
Click “OK” when you finish, and you’ll see your data sorted by your chosen conditional formatting icon.
List Sorting
One convenient sorting option many don’t realize exists in Excel is list sorting. For instance, you may want to sort by day of the week or month of the year. Select your data and then “Sort” on the Data tab to open the Sort box.
Choose the following in the drop-down boxes:
Sort by: Pick the column or row containing the list items. Sort on: Pick “Cell Values. ” Order: Pick “Custom List. ” Then choose an existing list in the Custom Lists section and click “OK. ”
You’ll see the list appear in the Order drop-down menu in the Sort box. Click “OK” to sort your data by the list.
You’ll then see your data sorted by your chosen list.
New List Sorting
Another way to sort using a list is with a new custom list. This is convenient if you haven’t had time to create the custom list in Excel yet, because you can make it with the sort tool. Select your data and then “Sort” on the Data tab to open the Sort box.
RELATED: How to Create a Custom List in Microsoft Excel
Choose the following in the drop-down boxes:
Sort by: Pick the column or row containing the list items. Sort on: Pick “Cell Values. ” Order: Pick “Custom List. ” Select “New List,” click “Add,” and then enter each list item in the List Entries box. Be sure to enter them in the order you want them. Click “OK” when you finish.
You’ll see the list in the Sort box’s Order drop-down menu. Click “OK” to sort your data by this list.
You’ll then have your data sorted by the new custom list you just created.
Multi-Level Sorting
If you’d like to sort by more than one column or row in Excel, you can add another level when you create a custom sort. For example, you might sort alphabetically by name first and color second. And, you can add levels to any of the above sorting methods if you like.
RELATED: How to Use the Microsoft Excel SORT Function
In the Sort box, set up your first sort option with the drop-down boxes. Then, choose “Add Level” and set up the secondary sort you want to use.
Keep in mind that, Excel sorts your data based on this list of levels from top to bottom. So if needed, you can rearrange them. Select a level and then use the arrow buttons to move that level up or down.
When you finish, click “OK” to apply the sort for your multiple levels.
Sorting your data in Microsoft Excel helps you see it the way you need to for analysis or review. So keep these sorting methods in mind and also look at how to sort by date in Excel.
