Home life · Updated
How to Sort Excel Columns Numerically
Quick answer Select the column, go to Data > Sort, choose your column and sort by Smallest to Largest (or Largest to Smallest). Use Number as the sort order to avoid text-style sorting.
What You’ll Need
- A computer with Microsoft Excel (2010 or later, or Excel for Microsoft 365)
- An Excel file (.xlsx) with the column you want to sort
- Basic familiarity with selecting cells and using the ribbon menu
Step-by-Step
- Open your Excel file – Double-click the file to open it in Excel. If you have multiple sheets, click the tab at the bottom that contains the column you want to sort.
- Select the entire column – Click the letter at the top of the column (e.g., “A”) to highlight the whole column. If you only want to sort a specific range, select those cells instead.
- Go to the Data tab – In the ribbon at the top, click the “Data” tab. This is usually between “Formulas” and “Review”.
- Click Sort – In the “Sort & Filter” group, click the “Sort” button (it looks like an A-to-Z arrow). If you selected only a range, Excel may ask if you want to expand the selection – choose “Expand the selection” to keep related rows together.
- Choose your column and sort order – In the Sort dialog box, under “Column”, pick the column you want to sort (it should already be selected). Under “Sort On”, choose “Cell Values”. Under “Order”, select “Smallest to Largest” for ascending or “Largest to Smallest” for descending.
- Set the sort order to Number – Click the “Order” dropdown and select “Custom List” if you need a custom order, but for numeric sorting, just choose the standard option. If your data has numbers stored as text, you may need to use the “Sort On” dropdown and choose “Cell Values” – Excel will handle numeric sorting automatically.
- Click OK – Press OK to apply the sort. Your column will now be sorted numerically. If you sorted a range, the adjacent columns will move with it.
Common Mistakes
- Sorting only one column – If you select only a single column and sort, the rest of your data won’t move with it, breaking the row alignment. Always select the entire data range or expand the selection.
- Numbers stored as text – If your numbers are left-aligned and have a green triangle in the corner, they are stored as text. Sorting will put 10 before 2 because text sorting compares character by character. Convert them to numbers first (see Troubleshooting).
- Sorting with headers – If your column has a header row, make sure “My data has headers” is checked in the Sort dialog. Otherwise, the header will be sorted as part of the data.
- Using the wrong sort order – Clicking the A-Z button on the Data tab sorts alphabetically, which for numbers gives a text-like order. Always use the Sort dialog and choose “Smallest to Largest” for numeric.
Tips
- Use the filter arrow – Click the dropdown arrow in the header cell, then choose “Sort Smallest to Largest” – this is a quick shortcut.
- Keyboard shortcut – Press Alt + D + S to open the Sort dialog quickly (works in most versions).
- Sort by multiple columns – In the Sort dialog, click “Add Level” to sort by a second column (e.g., first by number, then by name).
- Use the SORT function – In Excel 365, you can use =SORT(A2:A100) in a new column to get a sorted list that updates automatically.
- Check for hidden rows – If you have hidden rows, sorting may exclude them. Unhide all rows before sorting to avoid surprises.
Troubleshooting
- Numbers are not sorting correctly (10 before 2) – This is a classic text-sorting issue. Select the column, go to Data > Text to Columns, click Finish (without changing options) to convert text to numbers. Alternatively, use the error icon and choose “Convert to Number”.
- Sort button is grayed out – This happens when the sheet is protected or cells are merged. Unprotect the sheet (Review > Unprotect Sheet) or unmerge cells before sorting.
- Data gets mixed up – If you sorted only a range and lost alignment, undo (Ctrl+Z) and redo with the entire data range selected. Always check “Expand the selection” when prompted.
- Sorting includes the header – If the header row gets sorted into the data, go to Sort dialog and check “My data has headers”.
- Blank cells appear at the top – By default, Excel places blanks at the bottom for ascending sort. If you want them at the top, sort descending or use a helper column with a formula.
Frequently Asked Questions
Why does Excel sort my numbers as text?
Excel treats numbers as text if they have leading apostrophes, spaces, or were imported from a text file. Convert them using Text to Columns or the error icon.
Can I sort a column without affecting other columns?
Yes, but it will break the row relationships. If you want to sort only one column, select it and sort – but be aware that the rest of the row won’t move with it. Usually, you want to keep rows intact.
How do I sort by numbers in descending order?
In the Sort dialog, choose “Largest to Smallest” under Order. Or use the filter arrow and select “Sort Largest to Smallest”.
What if my column has both numbers and text?
Excel will sort numbers first, then text. If you want a specific order, you may need to separate them or use a custom sort list.
Can I sort by numeric value in a pivot table?
Yes, in a pivot table, right-click a value cell and choose Sort > Sort Smallest to Largest. The pivot table will reorder rows based on the numeric values.
Is there a way to sort automatically as I add new data?
Use the SORT function in Excel 365 to create a dynamic sorted list, or use a table and re-sort manually after adding data.
Related
- How to Stretch a Tight Jeans Waistband
- How to Stretch Jeans Waist Permanently
- How to Stretch in SketchUp: Scale Objects Easily
Sources: Microsoft Support. Menus and buttons can differ slightly by version. Spotted something out of date? Tell us.