AjapaReporter

Home life · Updated

How to Sort Numbers in Excel: Step-by-Step

Time it takes5 min
Steps7in the list below
LevelEasyno special skills
Official sourceMicrosoft Supportcheck it for changes

Quick answer Sort numbers in Excel by selecting your data, opening the Data tab, and using the Sort A to Z or Sort Z to A buttons. This orders rows by the numeric value in a chosen column, either smallest to largest or largest to smallest.

What You’ll Need

  • A computer with Microsoft Excel (2013 or newer, or Excel for Microsoft 365).
  • A spreadsheet containing a column with numbers (e.g., prices, quantities, scores).
  • Basic familiarity with selecting cells and using the ribbon.

Step-by-Step

  1. Select the data range – Click and drag to highlight all cells that include the numbers you want to sort plus any adjacent columns that should move together (e.g., names, dates). Do not select entire rows or columns unless you want to sort the whole sheet.
  2. Open the Data tab – On the ribbon at the top of Excel, click the “Data” tab. This is where sorting and filtering tools live. If you don’t see it, your Excel version may call it “Sort & Filter” under a different menu, but it is usually the second or third tab.
  3. Choose your sort order – In the “Sort & Filter” group, click either “Sort A to Z” (for smallest to largest) or “Sort Z to A” (for largest to smallest). For numbers, these buttons are labeled with an A–Z icon, but they work numerically. If you want more control, click “Custom Sort” instead.
  4. Select the sort column – If you used “Custom Sort,” a dialog box appears. Under “Column,” choose the column that contains the numbers you want to sort by (e.g., Column B). Under “Sort On,” keep “Values.” Under “Order,” pick “Smallest to Largest” or “Largest to Smallest.”
  5. Confirm the sort – Click “OK” (or press Enter) to apply the sort. Excel will rearrange the entire selected range based on the numeric values in your chosen column.
  6. Check for header row – If your data has a header (like “Price” or “Quantity”), make sure the “My data has headers” checkbox is ticked in the Custom Sort dialog. This prevents the header row from being sorted as if it were a number.
  7. Verify the results – Scroll through your data to confirm that the rows are now in the correct order. If any numbers appear out of place, check that they are actually stored as numbers, not text (see Troubleshooting).

Common Mistakes

  • Selecting only one column – If you select only the number column, Excel sorts just that column, breaking the relationship with other data in the same row. Always select the full range of rows.
  • Sorting when numbers are stored as text – Cells with numbers that have a green triangle or are left-aligned are often text. Sorting these will put them in order like “1, 10, 2” instead of “1, 2, 10.”
  • Forgetting to include the header – Without the header checkbox, Excel treats the first row as data and may move it to the bottom or top incorrectly.
  • Using the Home tab sort accidentally – The Home tab also has sort buttons, but they may behave slightly differently. Stick to the Data tab for consistency.
  • Sorting blank cells – If your range includes blank cells, Excel places them at the bottom by default. This is usually fine, but be aware it can hide missing data.

Tips

  • Use a table – Convert your data to an Excel Table (Ctrl+T) before sorting. Tables automatically keep rows together and remember the header, making sorting safer.
  • Sort by multiple columns – In the Custom Sort dialog, click “Add Level” to sort by more than one column. For example, sort by “Category” first, then by “Price” within each category.
  • Use the filter arrows – If you have a table or a filtered range, click the drop-down arrow in the column header and choose “Sort Smallest to Largest” or “Sort Largest to Smallest.” This is quicker for one-off sorts.
  • Undo if needed – Press Ctrl+Z immediately if the sort goes wrong. Excel keeps one undo step for the sort action.
  • Check for leading zeros – If your numbers have leading zeros (like product codes 001, 002), they may be stored as text. Sort them as text to preserve the zeros, or convert to numbers if zeros are not important.

Troubleshooting

Numbers sort as 1, 10, 2 instead of 1, 2, 10 – This means your numbers are stored as text. Select the column, click the warning icon that appears (or use “Text to Columns”), and convert to numbers. Alternatively, multiply each cell by 1 using a helper column with the formula =A1*1 and copy-paste values.

Sorting moves the wrong rows – You likely selected only part of the data. Select the entire range of rows and columns that belong together. If you have a table, click anywhere inside it and sort from the header.

Header row disappears or gets sorted – In the Custom Sort dialog, ensure “My data has headers” is checked. If you used the quick sort buttons, Excel usually detects headers automatically, but if not, add a filter first.

Sort button is greyed out – This happens when your selection includes merged cells or the sheet is protected. Unmerge any merged cells in the range, or unprotect the sheet under Review > Unprotect Sheet.

Blank cells appear in the middle – Blank cells are sorted to the bottom by default. If they appear elsewhere, check for spaces or non-printing characters. Use the TRIM function to clean data.

Sort does not update formulas – Sorting moves the cells, so formulas that reference other cells will update automatically if they use relative references. If you used absolute references (like $A$1), they may now point to the wrong cell. Review formulas after sorting.

Frequently Asked Questions

Can I sort numbers without moving other columns?

Yes, but only if you want to break the row relationships. Select just the number column and sort. Excel will warn you about adjacent data. It is safer to include all related columns.

How do I sort numbers in descending order?

Use the “Sort Z to A” button or choose “Largest to Smallest” in the Custom Sort dialog. This puts the highest number first.

Why does Excel sort numbers as text?

This happens when numbers are imported from another program or typed with an apostrophe. Convert them to numbers using the Text to Columns wizard or by multiplying by 1.

Can I sort by numbers that are part of a text string?

Yes, but you need to extract the number first. Use a formula like =VALUE(MID(A1, start, length)) to pull the numeric part, then sort by that helper column.

What if my numbers have decimals or negative values?

Sorting works the same. Negative numbers sort before zero, and decimals sort by their value (e.g., 1.2 comes before 1.3).

Does sorting affect formulas in other cells?

Sorting moves the cells, so formulas that reference those cells will update if they use relative references. Absolute references may point to the wrong cell after sorting, so review them.

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