Skip to content
Tutorials Center
My learning

Topics

Lesson 41 of 93 ยท Tables, Sorting and Filtering

Sorting Data

Learn to sort data in Excel: single and multi-level sorts, custom lists, sort by color, the partial-range trap, and the SORT and SORTBY functions.

Sorting puts rows in a useful order: names from A to Z, sales from largest to smallest, deadlines from soonest to latest. It is one of the first things you do with a new list, and also one of the easiest ways to damage a worksheet if you select the wrong cells. In this lesson you learn the Sort dialog, multi-level sorts, custom orders, sorting by color, the classic "I sorted one column" accident, and the two functions that sort with a formula: SORT and SORTBY.

You should know how an Excel Table is laid out (headers on top, one record per row). The sorting steps below are real Excel. The live examples are stand-ins: small JavaScript and pandas programs that follow the same rules, so you can see what Excel does to the rows.

Three ways to sort

Quick sort A to Z or Z to A buttons on one column.
Sort dialog Several levels, custom lists, colors.
Formulas SORT and SORTBY leave the source untouched.

Buttons change your data. Formulas build a sorted copy.

1 / 2

Sorting with the buttons

Click any single cell inside your data, then open the Data tab and use Sort A to Z or Sort Z to A (the same buttons exist on Home โ†’ Sort & Filter). Excel detects the surrounding block of data, assumes the first row is a header if it looks different from the rows below, and sorts the whole block by the column your cell was in. Every row moves as one unit, so a person's name, department and score stay together.

For numbers the order is smallest to largest (or the reverse). For text it is alphabetical and not case-sensitive by default: apple and Apple are equal. For dates it is oldest to newest, because dates are really numbers (see Dates and Times in Excel).

When a column mixes types, Excel uses a fixed order in an ascending sort: numbers, then text, then the logical values FALSE and TRUE, then errors, and finally blank cells. Blanks always end up at the bottom, even in a descending sort.

Numbers stored as text

A column of numbers that were imported as text sorts like words: "10" comes before "9" because the character 1 is smaller than 9. If a "numeric" column sorts strangely, check for the little green triangles and the left alignment that mark numbers stored as text, and convert them first. Lesson TEXT, VALUE and Number-as-Text Problems shows how.

Multi-level sorts

To sort by more than one column, click a cell in the data and choose Data โ†’ Sort. The dialog lets you add levels:

  1. Sort by Department, Sort On Cell Values, Order A to Z.
  2. Then by Score, Order Largest to Smallest.
  3. Tick My data has headers so the header row is not sorted into the data.

Excel sorts by level 1 first. Only when two rows tie on level 1 does it look at level 2, and so on. The result here is "departments in alphabetical order, and within each department the best score first". Rows that tie on every level keep their original relative order, which makes the result predictable.

JS A stand-in for Excel's multi-level sort

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that Omar and Ravi tie at 91, so they stay in the order they started. Zoe's blank score sits last even though the sort is descending. The third sort used a custom order that is neither alphabetical nor numeric, which is the next topic.

Custom lists and sorting by color

Some orders are not alphabetical: Low, Medium, High, or Mon, Tue, Wed. In the Sort dialog, set Order to Custom List... and pick a built-in list (days of the week and months are included) or type your own entries, one per line. Excel saves the list so you can reuse it. A simple alternative that works everywhere is a small lookup table with a rank number for each label, and a sort on a helper column that looks the rank up (see VLOOKUP and Its Limits).

You can also sort by appearance. In the dialog, change Sort On from Cell Values to Cell Color, Font Color or Cell Icon (from conditional formatting, see Conditional Formatting). Excel then asks which color goes On Top or at the bottom. To order several colors, add one level per color. Sorting by color only works for colors you applied or that conditional formatting produced, and it is a one-time reordering: when a cell changes color later, the rows do not move on their own.

The partial-range trap

The classic accident goes like this. You select only the Name column, press A to Z, and Excel shows Sort Warning with two choices: Expand the selection or Continue with the current selection. If you continue, only the names move. The departments and scores stay where they were, so every person is now attached to someone else's data. Nothing looks broken, and Undo (Ctrl+Z) is the only quick cure.

Rules that keep you safe:

  • Click one cell in the data rather than selecting a single column. Excel then picks up the whole block.
  • Always choose Expand the selection when the warning appears.
  • Keep the table free of blank rows and columns in the middle, because Excel stops detecting at a blank row.
  • Avoid merged cells in the data; sorting needs same-sized cells.
  • Be careful with formulas that point to other rows, such as =B3-B2. After a sort, relative references still point to the cell above, which may now be a different record. Compare totals before and after a sort, or use Excel Tables with structured references.
  • Filtered or hidden rows are not skipped by every command, so clear filters before an important sort.
JS What goes wrong when only one column is sorted

Press Run (or Ctrl + Enter) to see the console output here.

No output.

In the wrong version Mia is suddenly in Support with a score of 91, which really belongs to Omar. In the correct version each person keeps their own department and score.

SORT and SORTBY

In Microsoft 365 and Excel 2021 or newer, two functions return a sorted copy and leave the original data alone. Because the result is a dynamic array that fills several cells, read Dynamic Arrays and Spilling as well.

TEXT
=SORT(array, [sort_index], [sort_order], [by_col])
=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
  • SORT(A2:C7, 3, -1) sorts the three-column block by its 3rd column, descending. sort_order is 1 for ascending (the default) and -1 for descending. Set by_col to TRUE to sort across columns instead of down rows.
  • SORTBY(A2:C7, B2:B7, 1, C2:C7, -1) sorts by other ranges, here department ascending then score descending. The sort keys do not need to be part of the returned block, and they can be calculated, for example SORTBY(A2:A7, LEN(A2:A7)) to order names by length.
  • Neither function sorts in place. When the source changes, the sorted copy updates by itself.

Unlike the buttons, these functions cannot damage the source, they cannot sort by color, and they always use real column positions. SORT takes a column number that breaks if you insert columns in the middle of the array, while SORTBY takes actual ranges, which survive edits better.

The same ideas in pandas

DataFrame.sort_values is the pandas counterpart of the Sort dialog. Multiple keys, per-key direction and a stable algorithm give the same results as Excel:

Python Sorting with pandas sort_values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

na_position="last" is the default, matching Excel's rule that blanks go last. The rank column mirrors the helper-column trick for custom orders.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Where do blank cells end up when you sort a column in descending order?

    Blank cells are always placed last, whether the sort is ascending or descending.
  2. 2. You select only the Name column and sort it, then choose "Continue with the current selection". What happens?

    Only the selected cells move, which separates each name from its other data. Choose "Expand the selection", or click a single cell instead of selecting one column.
  3. 3. Which formula returns the block A2:C7 sorted by column 3 from largest to smallest?

    SORT takes the array, the column index and the order, where -1 means descending. SORTBY expects a range as its sort key, not a column number.
  4. 4. Why does a text column of "10", "9" and "100" sort as 10, 100, 9?

    Text compares character by character, so "1" comes before "9". Convert the text to real numbers to sort numerically.
  5. 5. How do you sort Low, Medium, High in that logical order?

    Alphabetical order would give High, Low, Medium. A custom list, or a rank number looked up in a helper column, defines the order you want.

Summary

  • Click one cell in the data, then sort; Excel moves whole rows. Never sort a single selected column without expanding the selection.
  • Multi-level sorts apply levels in order; ties keep their original order. Blanks always go last.
  • Custom lists and sort by cell color, font color or icon handle orders that are not alphabetical.
  • Numbers stored as text sort like words; fix the data type first.
  • SORT and SORTBY return a sorted copy and keep the source intact; prefer SORTBY when columns may be inserted.
  • pandas sort_values offers the same controls in Python.

Next: Filtering Data.