Home life · Updated
How to Sort Excel by Numerical Order
Quick answer To sort Excel by numerical order, select your data, go to the Data tab, and click either Sort Smallest to Largest (A to Z) or Sort Largest to Smallest (Z to A).
What You’ll Need
- Microsoft Excel (desktop, web, or mobile) with your data open
- Your data should be in a table or range with column headers
Step-by-Step
- Select your data – Click and drag to highlight the cells you want to sort. If your data has headers, include them in the selection. If your data is in a formal Excel Table (created with Ctrl+T), just click anywhere inside it; Excel will automatically include all rows and columns.
- Open the Sort dialog – Go to the Data tab on the ribbon. In the Sort & Filter group, you’ll see two quick sort buttons: a small A→Z icon (with a downward arrow) and a Z→A icon. Click the one that matches your need: A→Z sorts numbers from smallest to largest, Z→A sorts from largest to smallest.
- Confirm the sort – If your selection includes headers, Excel will detect them and keep the header row in place. If you get a Sort Warning dialog, choose “Expand the selection” to keep related data together, then click Sort.
- Check the result – Your rows should now be rearranged based on the numeric values in the selected column. If the numbers are not sorting correctly (e.g., 10 comes before 2), your numbers might be stored as text. See Troubleshooting below.
- Sort by multiple columns (optional) – If you need a secondary sort (e.g., sort by region first, then by sales amount), use the Custom Sort option. On the Data tab, click Sort (the larger button, not the quick arrows). In the dialog, add levels: choose the first column, then click Add Level to add a second column, and set the order for each.
- Use the right-click method (alternative) – Right-click any cell in the column you want to sort by, hover over Sort, and choose Sort Smallest to Largest or Sort Largest to Smallest. This works even if you haven’t selected the whole range, as long as your data is contiguous.
- Sort with Excel Table filters – If your data is in a table, click the drop-down arrow in the header of the column you want to sort. Choose Sort Smallest to Largest or Sort Largest to Smallest from the menu. This is often the fastest method for tables.
Common Mistakes
- Not including all columns – If you select only one column and sort, Excel will ask if you want to expand the selection. If you choose “Continue with the current selection,” only that column will be sorted, scrambling your rows. Always select the entire range or use a table.
- Sorting text-formatted numbers – If your numbers are left-aligned in cells (default text alignment), they are likely stored as text. Text sorting puts 10 before 2 because it compares character by character. Convert them to numbers first (see Troubleshooting).
- Hidden rows or columns – If you have hidden rows, the sort will move them along with visible rows, which can break your data layout. Unhide all rows before sorting if you want a clean sort.
- Merged cells – Sorting data with merged cells can cause errors or unexpected results. Unmerge any merged cells in the sort range before sorting.
- Sorting headers as data – If you don’t have a header row, Excel might treat the first row as data. Make sure to check the “My data has headers” box in the Sort dialog if you have headers.
Tips
- Use Excel Tables – Converting your range to a table (Ctrl+T) makes sorting safer and easier. Tables automatically expand and keep formatting, and the filter arrows are always available.
- Freeze header row – Before sorting, freeze the top row (View → Freeze Panes) so you can see the headers while scrolling through the sorted data.
- Create a backup – If your data is important, make a copy of the sheet before sorting. Right-click the sheet tab and choose “Move or Copy” to create a duplicate.
- Sort with custom lists – For non-numeric orders (like months or weekdays), use Custom Sort and choose a custom list. This is not needed for pure numbers, but useful if you combine text and numbers.
- Use the SORT function – If you want a dynamic sort that updates automatically when data changes, use the SORT function:
=SORT(range, column_index, order). This creates a new sorted array without altering your original data.
Troubleshooting
- Numbers are not sorting correctly (e.g., 10 before 2) – This is the classic text-formatted number issue. To fix: select the column, go to Data → Text to Columns, and click Finish. Or use the error indicator: select the column, click the warning icon, and choose “Convert to Number.” After conversion, re-sort.
- Sort warning appears – This means Excel detected adjacent data that isn’t selected. Always choose “Expand the selection” to keep rows intact. If you accidentally choose “Continue with current selection,” undo (Ctrl+Z) and redo with the full range selected.
- Headers are being sorted as data – In the Sort dialog, ensure “My data has headers” is checked. If you used the quick sort buttons and the header moved, undo and try again, making sure the header is included in the selection.
- Sort button is grayed out – This usually happens when the sheet is protected or the range is not editable. Check Review → Protect Sheet; if protected, unprotect it (you may need the password). Also, if you have a merged cell in the range, unmerge it first.
- Sorting only one column – If you selected only one column and chose “Continue with current selection,” you’ve broken the row alignment. Undo immediately, then select the entire data range (including all columns) before sorting.
Frequently Asked Questions
How do I sort numbers in Excel without messing up rows?
Always select the entire data range (all columns) before sorting, or use an Excel Table. This ensures all rows move together. If you only select one column, Excel will warn you; choose “Expand the selection.”
Why does Excel sort 10 before 2?
This happens when numbers are stored as text. Text sorting compares character by character: ‘1’ is less than ‘2’, so 10 comes before 2. Convert text to numbers using Text to Columns or the Convert to Number option.
Can I sort by numerical order in Excel Online (web version)?
Yes. The web version has the same sort buttons on the Data tab. The steps are identical. Some advanced options like custom sort may be limited, but basic numerical sorting works fine.
How do I sort numbers in descending order (largest to smallest)?
Use the Z→A button on the Data tab, or in the Sort dialog choose “Largest to Smallest” for the order. This is the same as sorting descending.
What if my numbers have decimals or negative numbers?
Sorting works the same way. Excel treats negative numbers as smaller than positive ones, and decimals are compared by their value. Just ensure they are stored as numbers, not text.
How do I sort by multiple columns, like first by category then by number?
Use Custom Sort (Data → Sort). Add levels: first column (category) with order A to Z, then Add Level, choose the numeric column and set order to Smallest to Largest. Excel will sort by the first level, then within each group by the second.
Related
- How to Stretch Out Boots: Easy DIY Methods
- How to Stretch Your Upper Back: 7 Easy Moves
- How to Stretch Your Screen: Fix Overscan & Aspect Ratio
Sources: Microsoft Support. Menus and buttons can differ slightly by version. Spotted something out of date? Tell us.