The Complete Guide to Sorting Data in Excel

Sorting data is one of the most common tasks you’ll perform in Excel. Being able to sort your data properly allows you to quickly find and analyze information. In this complete guide, I’ll walk you through everything you need to know to sort data in Excel.

Why Sorting Data is Important in Excel

Here are some of the key reasons you’ll want to make sure you know how to sort data in Excel

• Find data quickly – Sorting allows you to reorder your data so you can find specific rows or values quickly. For example, sorting by last name makes it easy to locate a specific person.

• Identify trends and patterns – Sorting makes it easier to visually identify patterns and trends in your data. By sorting sales data from highest to lowest, you can quickly see your top-selling products.

• Organize messy data – Raw data is rarely sorted Sorting it organizes your data so it’s neater and structured

• Analyze data – Sorted data is easier to analyze It’s simpler to apply filters, formatting, and formulas when your data is organized

• Remove duplicates – Sorting lets you identify duplicate values so you can remove them if needed.

As you can see, taking the time to properly sort your data pays dividends in terms of analyzing and getting the most from your Excel workbooks.

How to Sort Data in Excel

Sorting data in Excel is easy and only takes a few clicks. Here are the basic steps:

1. Select the cells/column you want to sort.

2. Go to the Data tab and click the Sort button.

3. In the Sort dialog box, choose the sort column, sort order, and other options.

4. Click OK.

And that’s it! Excel will reorder the selected cells based on the column and sort order you specified. Let’s take a closer look at how to select cells and set your sorting options.

Selecting Cells to Sort

Before sorting, you first need to select the cells you want to include in the sort operation:

• To sort a single column, select any cell in the column.

• To sort multiple columns, select all of the cells in those columns.

• To sort rows based on a single column, select the entire range including the column you want to sort by.

• Use Ctrl + click to select non-contiguous columns or cells.

Excel will always sort the entire region of selected cells, even if you only select a single cell in the column you want to sort by.

Choosing Sort Options

After selecting your cells, open the Sort dialog box to configure your sort:

![][]

Here are the key options:

• Sort by – The column you want to sort by. Defaults to the column of the selected cell.
• Sort on – Values, cell color, font color, or cell icon.
• Order – Ascending (A to Z) or descending (Z to A).
• Add levels – For advanced sorting by multiple columns.
• Header row – Keep the header row in place when sorting.
• Expand the selection – Sort all cells associated with the selected data.

Once your options are set, click OK to perform the sort. Excel will rearrange your selected cells based on the column and order you specified.

Keyboard Shortcuts for Sorting

For quick one-click sorting, you can use these handy keyboard shortcuts instead of the Sort dialog box:

• Sort Ascending: Ctrl + Shift + ↑
• Sort Descending: Ctrl + Shift + ↓

Select your cells, use the shortcut, and Excel will instantly sort using the current column.

Sorting by Multiple Columns

Excel’s Sort dialog box makes it easy to perform more advanced sorts using multiple columns:

1. Select your data range to be sorted.

2. Go to the Data tab > Sort.

3. For the first sort column, select the column and order.

4. Click Add Level.

5. Choose the next sort column and order.

6. Repeat steps 4-5 to add additional sort levels.

7. Click OK to perform the multi-level sort.

This will sort your data by the first column, then sort each matching group by the second column, and so on. Some common examples:

• Last name, then first name
• State, then city
• Date, then product

Multi-level sorting allows you to rearrange your data with great flexibility. Add as many sort levels as you need.

Custom Sort Orders

By default, Excel sorts text values alphabetically and numbers from smallest to largest. But you can also define your own custom sort orders.

This is helpful when you want to control the sort order instead of using regular alphabetic or numeric order. For example:

• Weekdays in a specific custom sequence
• Product tier (Good, Better, Best) in order of quality
• Status values (Active, Inactive, Archived) in logical order

To customize the sort order:

1. Select your data to sort.

2. On the Data tab, click Sort.

3. Click the Options button.

4. Click Custom Lists.

5. Enter your custom values in the desired order.

6. Click OK twice to perform the custom sort.

Excel will then sort your data based on the custom order you defined rather than default alphanumeric order.

Sorting Text Properly

By default, Excel sorts text alphabetically, character-by-character. This causes problems when sorting names and words with capital letters.

For example, a default ascending sort would place these values out of order: MacDonald mcdonald McDonald

To properly sort text values:

1. Select your data and go to the Sort dialog box.

2. Click Options.

3. Check the Sort left-to-right box.

4. Select Case sensitive to take capitalization into account.

5. Click OK twice to perform the proper text sort.

Names and text will now sort naturally as you would expect!

Sorting Numbers Correctly

When sorting numbers, you may occasionally see strange results: 1 10 100 2

This happens because Excel sorts numbers as text without regard to their numeric value.

To properly sort numbers:

1. Select your data and open the Sort dialog box.

2. Click Options.

3. Check Sort left-to-right.

4. Select Values in the Order box.

5. Click OK twice to perform the numeric sort.

This will sort numerically from smallest to largest value.

Sorting Date Values

Dates can be sorted chronologically or by calendar month. By default, Excel sorts dates as text values alphabetically.

To properly sort dates:

1. Select your date data.

2. Go to the Data tab and click Sort.

3. Select the column containing the dates.

4. Choose the desired date sort order: – Oldest to newest – Newest to oldest – By month January to December – By month December to January

5. Click OK.

This will sort your dates chronologically or by calendar month depending on the option you selected.

Sorting by Cell Color, Font Color, or Icon

In addition to cell values, you can also sort by:

• Cell fill color
• Font color
• Cell icon

This allows you to manually or conditionally format your data, then sort based on the formatting.

To sort by color or icon:

1. Select your cells with the conditional formatting applied.

2. Go to the Sort dialog box.

3. In the Sort On drop down, pick Cell Color, Font Color, or Cell Icon.

4. Choose the Order.

5. Click OK.

Excel will sort your colored and icon cells into order based on the options you selected.

Sorting Worksheets vs Sorting Ranges

An important distinction in Excel is whether you want to sort an entire worksheet or just a specific range of cells.

By default, the Sort dialog box will sort all cells on the worksheet, including cells outside of your selected range.

To avoid accidentally sorting blank areas, unselect the Expand the selection option in the Sort dialog box. This will confine the sort to only the selected cells.

Sort Rows Instead of Columns

The Sort dialog box reorders your selected cells vertically in columns. To sort entire rows horizontally:

1. Select the rows you want to sort along with the column to sort by.

2. Open the Sort dialog box.

3. Check My data has headers to keep the first