Module 5 - Lesson 13
Sorting Data in Excel: Alphabetical, Numerical, and Custom Sorting
Learn how to organize your spreadsheet data effectively using Excel's powerful sorting capabilities. Master alphabetical and numerical sorting, sort by multiple columns, create custom sort orders, and sort by cell color. Transform chaotic data into well-organized, easy-to-analyze information.
In This Lesson
Why Sorting Matters
Sorting is one of the most fundamental and frequently used operations in Excel. When you sort data, you rearrange the rows in your spreadsheet based on the values in one or more columns. This simple action transforms raw, unorganized data into structured information that is far easier to understand, analyze, and present.
Consider a list of one thousand customer orders. In its original form, the orders might appear in the sequence they were entered, making it nearly impossible to find specific information quickly. But sort that same list by customer name, and suddenly you can locate any customer instantly. Sort by order date, and you see a clear timeline of activity. Sort by order value, and your highest-value transactions appear at the top. The data is the same, but sorting makes it accessible and meaningful.
This lesson will teach you every essential sorting technique in Excel, from simple one-click sorts to sophisticated multi-column arrangements with custom ordering. By the end, you will be able to organize any dataset exactly the way you need it.
What Sorting Does
When Excel sorts data, it reorders entire rows based on values in the column you specify. This is an important concept to understand. Sorting does not just rearrange one column while leaving others in place. It moves complete rows to maintain the relationship between all the data in each row. If row five contains Alice's name, her department, and her salary, all three pieces of information stay together when you sort.
Alphabetical
Sort text from A to Z or Z to A for names, products, categories
Numerical
Sort numbers from smallest to largest or largest to smallest
Chronological
Sort dates from oldest to newest or newest to oldest
Multi-Level
Sort by multiple columns with primary and secondary orders
Sorting rearranges your data but does not delete or modify any values. However, it does change the physical order of rows in your worksheet. If you need to restore the original order later, consider adding a numbered index column before sorting, or use Ctrl+Z immediately after sorting to undo the change.
Quick Sort Buttons
For simple, single-column sorting, Excel provides quick sort buttons that let you sort data with just one click. These buttons are the fastest way to arrange your data when you only need to sort by one column.
Where to Find Quick Sort Buttons
The quick sort buttons are located in two places. On the Data tab in the Sort and Filter group, you will find two prominent buttons labeled A to Z with a downward arrow for ascending order, and Z to A with a downward arrow for descending order. The same buttons also appear on the Home tab in the Editing group under the Sort and Filter dropdown.
How to Use Quick Sort
- Click any cell in the column you want to sort by. You do not need to select the entire column.
- Go to the Data tab on the Ribbon
- Click the appropriate button: A to Z for ascending order, or Z to A for descending order
- Excel automatically sorts the entire data range based on that column
What Ascending and Descending Mean
| Data Type | Ascending (A to Z) | Descending (Z to A) |
|---|---|---|
| Text | A, B, C ... X, Y, Z | Z, Y, X ... C, B, A |
| Numbers | Smallest to Largest (1, 2, 3...) | Largest to Smallest (...3, 2, 1) |
| Dates | Oldest to Newest (earliest first) | Newest to Oldest (most recent first) |
When you click a cell and use quick sort, Excel automatically detects the boundaries of your data. It looks for the contiguous range of cells containing data and includes all of it in the sort. This usually works perfectly, but if your data has blank rows or columns, Excel might not detect the full range correctly. In such cases, select your entire data range before sorting.
The Sort Dialog Box
While quick sort buttons work well for simple sorting, the Sort dialog box provides complete control over how your data is sorted. It allows multi-column sorting, custom orders, and sorting by cell attributes like color.
Opening the Sort Dialog
- Click any cell within your data range
- Go to the Data tab on the Ribbon
- Click the Sort button in the Sort and Filter group. This opens the full Sort dialog.
Understanding the Sort Dialog Options
| Option | Description |
|---|---|
| Column (Sort by) | Select which column to use for sorting from the dropdown list |
| Sort On | Choose what to sort by: Cell Values, Cell Color, Font Color, or Conditional Formatting Icon |
| Order | Select ascending, descending, or a custom list order |
| Add Level | Add additional columns to sort by after the primary column |
| Delete Level | Remove a sorting level you no longer need |
| Copy Level | Duplicate an existing level to modify its settings |
| My data has headers | Checkbox to indicate whether your first row contains column titles |
| Options | Access additional settings like case-sensitive sorting and sort orientation |
Always verify that the My data has headers checkbox is correctly set before sorting. If your data has headers but this box is unchecked, Excel will sort your header row into the middle of your data. If your data lacks headers but this box is checked, Excel will exclude your first row of actual data from the sort.
Alphabetical Sorting
Alphabetical sorting arranges text values in order from A to Z or from Z to A. This is the most common way to organize lists of names, products, locations, categories, and other text-based data.
How Alphabetical Sorting Works
Excel sorts text character by character from left to right. The letter A comes before B, which comes before C, and so on. When the first characters are identical, Excel compares the second characters, then the third, continuing until it finds a difference. This means Apple comes before Banana, but also that Apple comes before Application because the fifth character differs.
Before Sort:
| Name | Department |
|---|---|
| David | Sales |
| Alice | Marketing |
| Carol | Finance |
| Bob | IT |
After Sort (A to Z):
| Name | Department |
|---|---|
| Alice | Marketing |
| Bob | IT |
| Carol | Finance |
| David | Sales |
Case Sensitivity in Sorting
By default, Excel sorting is not case-sensitive. This means apple and Apple are treated as equal for sorting purposes. If you need case-sensitive sorting where lowercase letters are distinguished from uppercase, click the Options button in the Sort dialog and check the Case sensitive box.
Numbers and Symbols in Text
When sorting text, Excel follows a specific order for different character types. Numbers come before letters, so 1st Street sorts before Apple Street. Symbols and special characters have their own positions in the sort order. Blank cells always sort to the end in ascending order or to the beginning in descending order.
When sorting names like Mr. Smith, Mrs. Jones, Dr. Brown, the titles become part of the sort. If you want to sort by the actual last name, consider using separate columns for title and name, or use a helper column with the last name extracted for sorting purposes.
Numerical Sorting
Numerical sorting arranges numbers from smallest to largest or from largest to smallest. This is essential for analyzing financial data, sales figures, quantities, scores, and any other numeric values.
Ascending vs Descending for Numbers
When sorting numbers in ascending order, the smallest values appear first. One comes before ten, which comes before one hundred. Descending order reverses this, placing the largest values at the top. This is useful when you want to see your top performers, highest sales, or largest transactions first.
Ascending (Smallest First):
| Product | Sales |
|---|---|
| Widget C | 1,250 |
| Widget A | 3,800 |
| Widget D | 5,200 |
| Widget B | 8,900 |
Descending (Largest First):
| Product | Sales |
|---|---|
| Widget B | 8,900 |
| Widget D | 5,200 |
| Widget A | 3,800 |
| Widget C | 1,250 |
Negative Numbers
Negative numbers sort correctly in Excel. In ascending order, negative values come before positive values because they are smaller. So negative one hundred comes before negative ten, which comes before zero, which comes before positive ten.
One of the most common sorting problems occurs when numbers are stored as text rather than actual numbers. Text that looks like numbers sorts alphabetically, not numerically. This means 2 would come after 19 because the character 2 comes after the character 1. Watch for left-aligned numbers or green triangles in cells, which indicate numbers stored as text. Convert them to actual numbers before sorting.
Sorting by Date
Date sorting arranges dates chronologically from oldest to newest or newest to oldest. This is crucial for timeline analysis, tracking events, organizing schedules, and reviewing historical data.
How Date Sorting Works
Because Excel stores dates as serial numbers internally, date sorting works reliably as long as your dates are actually recognized as dates by Excel. Ascending order places the earliest dates first, while descending order places the most recent dates at the top.
| Sort Order | Result | Use When |
|---|---|---|
| Oldest to Newest | January, February, March... | Viewing chronological history, timelines |
| Newest to Oldest | December, November, October... | Seeing most recent items first, current activity |
Sorting Times
Times also sort correctly when stored as actual time values. Since times are stored as decimal fractions of a day, earlier times have smaller values and sort before later times in ascending order.
If your dates are entered as text rather than actual date values, they will sort alphabetically rather than chronologically. For example, 2/1/2024 would sort after 12/1/2023 because the character 2 comes after the character 1. Ensure your dates are recognized by Excel as actual dates. Properly recognized dates right-align in cells by default.
Multi-Column Sorting
Multi-column sorting, also called multi-level sorting, allows you to sort by more than one column simultaneously. This creates a hierarchy of sort criteria where Excel first sorts by the primary column, then within matching groups, it sorts by the secondary column, and so on.
Understanding Sort Levels
Think of multi-level sorting like organizing a phone book. First, you sort by last name. But when multiple people share the same last name, you need a secondary sort by first name to put them in order. The last name is level one, the first name is level two.
| Department | Name | Salary |
|---|---|---|
| Finance | Anderson | 65,000 |
| Finance | Williams | 58,000 |
| IT | Brown | 72,000 |
| IT | Davis | 68,000 |
| Sales | Johnson | 55,000 |
| Sales | Smith | 61,000 |
Level 1 sorts by Department alphabetically. Level 2 sorts names within each department.
How to Create a Multi-Level Sort
- Click any cell in your data range
- Go to Data tab and click the Sort button to open the Sort dialog
- Set up your first level by choosing the primary sort column and order
- Click Add Level to add a secondary sort criterion
- Configure the second level with its column and order
- Add more levels as needed using the Add Level button
- Click OK to apply the multi-level sort
The order of levels matters significantly. In the Sort dialog, use the up and down arrow buttons to change which column is the primary sort versus secondary. The topmost level is sorted first, and each subsequent level is applied within groups created by the levels above it.
Custom Sort Lists
Sometimes you need to sort data in an order that is neither alphabetical nor numerical. For example, you might want to sort days of the week from Sunday to Saturday, months from January to December, or priority levels from High to Medium to Low. Custom lists make this possible.
Built-in Custom Lists
Excel includes several built-in custom lists that you can use immediately without any setup. These include days of the week in both full and abbreviated formats, and months of the year in both full and abbreviated formats.
Using Custom Lists in Sorting
- Open the Sort dialog from the Data tab
- Select your column containing the data to sort
- Click the Order dropdown and select Custom List
- Choose an existing list or create a new one
- Click OK to apply the custom sort order
Creating Your Own Custom Lists
To create a custom list for values like Low, Medium, High or any other sequence you need, access the Custom Lists dialog through Excel Options. Go to File, then Options, then Advanced, and scroll down to find Edit Custom Lists. Enter your values in order, one per line, then click Add.
Once you create a custom list, it becomes available for all future sorts and even for AutoFill. Create lists for common sequences in your work like priority levels, status values, or regional groupings. This saves time and ensures consistent ordering across all your spreadsheets.
Sorting by Color
If you have applied cell colors or font colors to your data, either manually or through conditional formatting, you can sort based on those colors. This brings similarly colored cells together, which is useful when colors represent categories or status levels.
How to Sort by Cell Color
- Open the Sort dialog from the Data tab
- Select the column that contains the colored cells
- Change the Sort On dropdown from Cell Values to Cell Color
- In the Order dropdown, select which color should appear on top
- Click Add Level to specify the order for additional colors
- Click OK to apply the color-based sort
Sorting by Font Color or Icon
The same technique works for font colors and conditional formatting icons. Change the Sort On dropdown to Font Color or Conditional Formatting Icon as needed. This is particularly useful when you have applied icon sets or color-coded text through conditional formatting.
When sorting by color, you can only specify which color appears on top. Excel does not have an inherent order for colors like it does for text or numbers. You must add multiple sort levels to arrange multiple colors in a specific sequence, placing each color at its desired position.
Common Sorting Problems and Solutions
Even experienced Excel users encounter sorting issues. Understanding the common problems and their solutions helps you troubleshoot quickly when sorts do not work as expected.
Problem: Only Part of My Data Sorted
This happens when Excel fails to detect your complete data range, usually because of blank rows or columns within the data. Before sorting, select your entire data range manually, or ensure there are no gaps in your data.
Problem: My Header Row Got Sorted Into the Data
This occurs when the My data has headers checkbox is not selected in the Sort dialog. Always verify this setting before sorting, especially when working with new data.
Problem: Numbers Are Not Sorting Correctly
Numbers stored as text sort alphabetically, placing 2 after 19. Look for left-aligned values or green triangles in cells. Convert text to numbers by selecting the cells, clicking the warning icon, and choosing Convert to Number, or by multiplying the values by one using Paste Special.
Problem: Merged Cells Prevent Sorting
Excel cannot sort data that contains merged cells. You must unmerge all cells in the range before sorting. Select the range, go to Home tab, click the Merge and Center dropdown, and choose Unmerge Cells.
| Problem | Cause | Solution |
|---|---|---|
| Partial data sorted | Blank rows or columns in data | Remove gaps or select full range before sorting |
| Headers included in sort | Header checkbox not set correctly | Enable My data has headers in Sort dialog |
| Numbers sort wrong | Numbers stored as text | Convert to numbers before sorting |
| Cannot sort at all | Merged cells in range | Unmerge all cells before sorting |
| Sort does not stick | Protected worksheet | Unprotect the sheet to allow sorting |
Sorting Best Practices
Following these best practices will help you sort data reliably and avoid common problems.
- Always have a header row. This makes it easier for Excel to identify column names and prevents headers from being sorted into your data.
- Eliminate blank rows and columns. Remove any gaps within your data to ensure Excel detects the complete range.
- Avoid merged cells. Merged cells prevent sorting entirely. Use Center Across Selection instead if you need centered titles.
- Verify data types. Ensure numbers are actual numbers and dates are actual dates, not text that looks like numbers or dates.
- Add an index column. Before major sorts, add a column with sequential numbers. This lets you restore the original order if needed.
- Check the header checkbox. Always verify the My data has headers setting before completing a sort.
- Save before sorting. If your data is critical, save your workbook before performing sorts so you can recover if something goes wrong.
When you convert your data range to an Excel Table using Ctrl+T, sorting becomes more reliable. Tables automatically include all data in the range, properly handle headers, and expand to include new data as you add it. This eliminates many common sorting problems.
Practice Exercise
Apply everything you have learned by completing this hands-on exercise covering all major sorting techniques.
- Create a new workbook and save it as Sorting_Practice
- Set up headers in row 1: ID, Name, Department, Hire Date, Salary, Status
- Enter data for 12 employees with various names, three different departments, different hire dates spanning several years, salaries between 40000 and 90000, and status values of Active or Inactive
- Add an ID column with numbers 1 through 12 to preserve original order
- Sort alphabetically by Name using the quick sort button
- Undo the sort using Ctrl+Z to restore original order
- Sort numerically by Salary in descending order to see highest paid first
- Perform a multi-level sort: First by Department ascending, then by Salary descending within each department
- Sort by Hire Date from oldest to newest to see seniority
- Apply conditional formatting to the Status column with green for Active and red for Inactive
- Sort by cell color to group Active employees at the top
- Restore original order by sorting by the ID column ascending
Excellent work on completing the sorting lesson. You can now organize data alphabetically, numerically, by date, by multiple columns, and by color. Up next is Lesson 14: Filtering Data, where you will learn to display only the specific rows that meet your criteria while hiding the rest.
Key Takeaways from Lesson 13
- Sorting rearranges entire rows based on values in one or more columns while keeping row data together
- Quick sort buttons on the Data tab provide one-click A to Z or Z to A sorting
- The Sort dialog box offers full control including multi-level sorting and custom orders
- Ascending order means A to Z for text, smallest to largest for numbers, oldest to newest for dates
- Multi-level sorting creates a hierarchy where Excel sorts by primary column first, then by secondary within matching groups
- Custom lists allow sorting in non-alphabetical orders like days of the week or priority levels
- You can sort by cell color, font color, or conditional formatting icons
- Numbers stored as text sort incorrectly and must be converted to actual numbers
- Merged cells prevent sorting and must be unmerged first
- Always verify the My data has headers checkbox is correctly set before sorting
- Add an index column before major sorts to enable restoring original order
- Converting data to a Table improves sorting reliability and automatic range detection