AjapaReporter

Home life · Updated

How to Sort Numbers in Excel: Numeric Order Guide

Time it takes10 min
Steps8in the list below
LevelEasyno special skills
Official sourceMicrosoft Supportcheck it for changes

Quick answer This guide shows you how to sort data numerically in Excel, from smallest to largest or largest to smallest, using the Sort buttons, the Sort dialog, and the SORT function.

What You’ll Need

  • A computer with Microsoft Excel (any recent version, desktop or web)
  • A spreadsheet containing numeric data you want to organize
  • Basic familiarity with selecting cells and using the ribbon menu

Step-by-Step

  1. Check your data for problems. Before sorting, make sure all numbers in the column are actual numbers, not text. Look for cells with a green triangle in the corner, leading apostrophes, or values that appear left-aligned (numbers are right-aligned by default). Fix any text-formatted numbers by selecting the range, clicking the warning icon, and choosing “Convert to Number.”
  2. Select the data range. Click and drag to select all the cells you want to sort, including the header row if you have one. If your data is a proper Excel table (created with Ctrl+T or Insert > Table), you can just click anywhere inside it—Excel will automatically include the whole table.
  3. Use the quick sort buttons for a simple sort. With your data selected, go to the Home tab on the ribbon. In the Editing group, find the “Sort & Filter” button. Click it and choose “Sort Smallest to Largest” for ascending order (1, 2, 3…) or “Sort Largest to Smallest” for descending order (10, 9, 8…). Excel sorts the entire selected range based on the leftmost column.
  4. Sort by a specific column using the Data tab. If you have multiple columns and want to sort by a column that isn’t the leftmost, click anywhere inside your data range (not selecting a single column), then go to the Data tab. Click the “Sort” button (the one with the A→Z and Z→A icons). This opens the Sort dialog box.
  5. Configure the Sort dialog box. In the Sort dialog, check the box “My data has headers” if your first row contains column titles. Under “Sort by,” choose the column you want to sort by from the dropdown. Under “Sort On,” keep “Cell Values” selected. Under “Order,” choose “Smallest to Largest” or “Largest to Smallest.” Click OK to apply.
  6. Add multiple sort levels (optional). If you want to sort by more than one column—for example, sort by Region first, then by Sales within each region—click “Add Level” in the Sort dialog. Set the second row’s “Then by” column and order. Excel applies the first level as the primary sort and the second as the secondary sort. You can add as many levels as you need.
  7. Sort a single column without moving other data (careful). If you only want to sort one column and leave adjacent columns untouched, select just that column’s cells (excluding headers), then use the quick sort buttons. Excel will warn you with a “Sort Warning” dialog asking if you want to expand the selection. Choose “Expand the selection” if you want the whole row to move together, or “Continue with the current selection” if you truly want to sort only that column. The second option can break the relationship between rows, so use it only when you know what you’re doing.
  8. Use the SORT function for dynamic sorting (advanced). If you want a sorted copy of your data that updates automatically when the original changes, use the SORT function. In a blank area, type =SORT(range, sort_index, sort_order). For example, =SORT(A2:B10, 2, 1) sorts the range A2:B10 by the second column (B) in ascending order (1). Use -1 for descending. This function works in Excel 365 and Excel 2021 or later; older versions may not support it.

Common Mistakes

  • Sorting only one column: This scrambles your rows, mixing up names with numbers. Always select the entire data range or use the Sort dialog with “Expand the selection.”
  • Numbers stored as text: These sort incorrectly—10 comes before 2 because text sorts alphabetically. Convert them to numbers first.
  • Including the header in the sort: If you don’t check “My data has headers,” Excel treats your header row as data and sorts it to the bottom.
  • Hidden rows or columns: Excel sorts only visible cells by default. If you have filtered data, the sort applies only to the visible rows, which can give incomplete results.
  • Merged cells: Sorting a range with merged cells can cause errors or unexpected results. Unmerge before sorting.

Tips

  • Use keyboard shortcuts: Select your data and press Alt+H+S+S for smallest to largest, or Alt+H+S+L for largest to smallest. This works on Windows; Mac users can use Option+Command+Shift+S.
  • Sort by cell color or icon: If you’ve manually colored cells or used conditional formatting, you can sort by those colors. In the Sort dialog, change “Sort On” to “Cell Color” or “Font Color,” then choose the color and where it should go (top or bottom).
  • Create a table first: Converting your data to a table (Ctrl+T) makes sorting safer because Excel automatically includes all columns and respects the header row. Tables also add filter arrows that let you sort with one click.
  • Undo is your friend: If a sort goes wrong, press Ctrl+Z (or Command+Z on Mac) immediately to undo. You can undo multiple steps if needed.
  • Sort by multiple columns for better organization: For example, sort by Last Name, then by First Name, to get a properly alphabetized list even when last names repeat.

Troubleshooting

  • Numbers sort as 1, 10, 2, 3: This means your numbers are stored as text. Select the column, click the warning icon (yellow diamond with an exclamation mark), and choose “Convert to Number.” If that doesn’t appear, select the column, go to Data > Text to Columns, and click Finish—this often converts text to numbers.
  • The sort button is grayed out: This happens when your selection includes merged cells or when the sheet is protected. Unmerge any merged cells, or unprotect the sheet (Review > Unprotect Sheet, if you know the password).
  • Sorting moves the wrong rows: You likely selected only part of your data. Undo, then select the entire range including all columns, and try again. If your data has blank rows or columns, Excel may not recognize the full range—select it manually.
  • Sort dialog doesn’t show the column you want: If your data is not a contiguous block (has blank columns), Excel may not detect it correctly. Select the entire data range first, then open the Sort dialog.
  • After sorting, formulas break: If your formulas reference cells that moved, they may now point to the wrong data. Use absolute references (with $) or better, use Excel Tables so formulas automatically adjust. If formulas are already broken, undo and re-sort with the table structure.

Frequently Asked Questions

How do I sort numbers from highest to lowest?

Select your data range, go to the Data tab, click Sort, choose the column, and under Order select “Largest to Smallest.” Or use the quick sort button with the Z→A icon (which for numbers means descending).

Why does Excel sort 10 before 2?

Because those cells contain text, not numbers. Text sorting is alphabetical, so “10” comes before “2” because “1” is less than “2” as a character. Convert the cells to numbers as described in the troubleshooting section.

Can I sort by a column that has both numbers and text?

Yes. Excel will sort numbers first, then text, in the order you choose. For example, ascending order puts numbers first (1, 2, 3), then text alphabetically (A, B, C). This is often not what you want, so consider separating data types into different columns.

How do I sort without messing up my rows?

Always select the entire data range (all columns) before sorting. If you have a table, click anywhere in it and Excel handles the full range automatically. Never select just one column unless you intentionally want to break the row associations.

Does sorting work on filtered data?

Yes, but it only sorts the visible rows. If you have filters applied, Excel sorts only the filtered (visible) cells. To sort all data, clear the filters first (Data > Clear).

What is the SORT function and when should I use it?

The SORT function creates a dynamic sorted array that updates when your source data changes. It’s useful when you want a live sorted view without altering your original data. Use it in Excel 365 or 2021+. For example, =SORT(A2:B10,2,-1) sorts by column B descending.

Sources: Microsoft Support. Menus and buttons can differ slightly by version. Spotted something out of date? Tell us.