Sort numbers correctly in Excel

In this article, you will learn how to sort numbers that Excel treats as text.

Sort numbers correctly

Excel may sort numbers as text, producing an order such as 1, 10, 11, 12, 2, 21, 3. In that order, Excel is comparing characters rather than numeric values. Formatting the cells as numbers may not fix values that are stored as text.

One workaround is to perform an arithmetic operation on the values in a helper column, then sort by that column. You can fill a formula down the column or use VBA. For example, to sort 3,000 rows using the numbers in column B:

Sub fill_cells() Dim i As Integer For i = 1 To 3000 Cells(i, 3).Value = Cells(i, 2).Value / 1000 Next i End Sub

Column C now contains the values from column B divided by 1,000. Select column C, open the Data tab, and sort by it.

Article author: Andrei Olegovich