Excel’s sorting capabilities are super handy for organizing and analyzing data. You can sort by text, numbers, dates, times, colors, and icons. This guide will cover various ways to sort your data in Excel so it’s exactly the way you need it.
Sorting your Excel data
Before sorting, think about whether you want to sort your entire sheet or just a selected range. Sorting can apply to one column, multiple columns, or only part of your data without messing up the rest of the sheet.
For example, let’s say you have some sales data and want to sort by representative name:
Method 1: Quick Sort (A to Z)
-
Click any cell in the column you want to sort by. In our case, click a cell in the ‘Rep’ column (column B).
-
Go to the ‘Data’ tab on the Excel ribbon, and click the ‘A to Z’ sort button to sort alphabetically by the chosen column.
After sorting, the data will be rearranged with the representatives’ names in alphabetical order, and all related info moves with them in each row.
Sorting by date or time
Sorting by date or time helps to arrange time-sensitive data, usually from oldest to newest or vice versa.
-
Click any cell in the column that holds the dates or times you want to sort by. For example, click in the ‘OrderDate’ column.
-
Go to the ‘Data’ tab and click on the ‘Sort’ button. This opens the sort dialog box, where you’ll see more options.
-
In the dialog box, use the ‘Sort by’ dropdown and choose ‘OrderDate’. Then use the ‘Order’ dropdown to select ‘Newest to Oldest’ or ‘Oldest to Newest’.
Once you press ‘OK’, your data will be sorted based on the date order you selected.
Sorting by cell color, font color, or cell icon
If your data uses cell colors, font colors, or icons, you can sort by these attributes to bring certain records to the top.
- Click anywhere within your data set and open the ‘Sort’ dialog box, which you’ll find on the ‘Data’ tab.
- In the Sort dialog box, select the column you want to sort by from the “Sort by” dropdown. Then, from the ‘Sort On’ dropdown, choose either ‘Cell Color’, ‘Font Color’, or ‘Cell Icon’, depending on what you need.
- In the ‘Order’ section, pick the specific color, font, or icon you want to sort by. Choose if it should go on the top or bottom.
Hit ‘OK’ to sort. Your data is now sorted by the cell formatting you selected.
Sorting by multiple columns
For sorting by more than one column, try multi-level sorting. This lets you organize your data based first by one column, and then by another.
- Click any cell inside your data and open the ‘Sort’ dialog box from the ‘Data’ tab.
- In the Sort dialog box, choose the first column you want to sort by. For instance, pick ‘Item’.
- Click ‘Add Level’ to include another sort criterion. Then, choose the second column from the ‘Then by’ dropdown, like ‘OrderDate’. Set the sort order for each column.
When you click ‘OK’, your data will sort by ‘Item’ first, then by ‘OrderDate’.
Sorting only one column
Sometimes you just need to sort a single column without sorting the rest of the data. Be careful, though, because this can cause data mix-ups if not done right.
-
Select the specific column you want to sort, like the ‘Item’ column.
-
Go to the ‘Data’ tab and hit the ‘A to Z’ sort button.
-
Excel shows a ‘Sort Warning’ dialog. Select ‘Continue with the current selection’ and press ‘OK’.
The selected column will now sort on its own, but be aware that this could misalign the data across your sheet.
Sorting in a custom order
If you need to sort by a custom list, like priorities or rankings unique to your data, you can set that up.
-
Click any cell in the data and open the ‘Sort’ dialog box from the ‘Data’ tab.
-
In the ‘Sort by’ dropdown, pick the column you need to sort. Under ‘Sort On’, select ‘Cell Values’, and then pick ‘Custom List’ in the ‘Order’ dropdown.
-
In the ‘Custom Lists’ dialog, enter your custom order in the ‘List entries’ box. For example, type ‘High’, ‘Normal’, ‘Low’, each on its own line, and click ‘Add’.
-
Select your new custom list and click ‘OK’ to get back to the Sort dialog box. Your custom sort order will now be there in the ‘Order’ dropdown.
Press ‘OK’ to sort the data. It will now sort according to your custom order.
Sorting data in a row
Excel can also sort data horizontally across rows, instead of down columns.
-
Click any cell in the row you want to sort, then open the ‘Sort’ dialog box from the ‘Data’ tab.
-
In the Sort dialog box, click ‘Options’ to open additional settings.
-
In the ‘Sort Options’ dialog, select ‘Sort left to right’ under Orientation, then click ‘OK’.
-
Back in the Sort dialog box, select the row you want to sort from the ‘Sort by’ dropdown, and set up your sort preferences.
Click ‘OK’, and your data will sort horizontally based on values in the row you selected.
By learning these sorting techniques in Excel, you can easily reorganize your data, making it easier to read and faster to work with.