Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a column of integers in my spreadsheet, and the column is formatted as a number. Each cell inside the column is formatted as a number.

But I can't seem to sort them in numerical order - you know, 1,2...9,10,11...

Instead, it always sorts them according to text sorting : 10, 100, 101, 102...11, 111, 112, ....

How can I sort my column of numbers by number instead of by text?

share|improve this question
    
This question exists, superuser.com/questions/218012/… , however, it deals with pivot tables and the answer is to create another column with numbers. But the column in my spreadsheet is already converted to number format. –  rlb.usa Oct 4 '11 at 21:02
    
What version of excel, this worked for me in Excel 2007 –  Nick Josevski Oct 4 '11 at 21:51
    
It is Excel 2010 –  rlb.usa Oct 4 '11 at 22:04
add comment

2 Answers

up vote 4 down vote accepted

It sounds as if your numbers are being interpreted as text by Excel. A good indication of this is that they automatically align to the left of each cell. Numbers normally are aligned to the right.

If this is the case, a simple fix is to move into a cell, press F2 then Enter. Repeat for each cell containing the numbers that don't sort. This works by putting the cell into edit mode and then out again. If the contents can be interpreted as a number then Excel converts it to a number.

If you have too many cells to do this manually, you can use the VALUE function to create another column contain true numeric values and then paste them (by value) back over the offending cells.

share|improve this answer
    
There is another catch, checkbox that says 'My data has headers' which has to be unchecked before sorting –  Xinus Jan 6 '13 at 11:48
add comment

You can do this on the data directly in a single shot by without creating working columns. My favoured method is using Paste Special (I normal multiply by the "text" by 1)

Debra Dalgleish provides 7 methods here, http://www.contextures.com/xlDataEntry03.html

The Paste Special method as listed by Debra is

  • Select a blank cell
  • Choose Edit>Copy
  • Select the cells that contain the numbers
  • Choose Edit>Paste Special
  • Select Add
  • Click OK To apply number formatting, choose Format>Cells
  • On the Number tab, select the appropriate format, then click OK
share|improve this answer
    
Doesn't help, they are already formatted as numbers –  rlb.usa Oct 4 '11 at 22:01
1  
Although Pasting and then using one of the paste options that comes up to paste as a number does work –  rlb.usa Oct 4 '11 at 22:07
    
Yes, its the Paste Special operation that forces the convert. Glad to hear you got it working. –  brettdj Oct 4 '11 at 22:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.