Excel Sort Doesn't Work

  • Thread starter Thread starter PeterOut
  • Start date Start date
P

PeterOut

I am using MS Office Excel 2003 (11.8307.8221) SP3 on MS Windows XP
5.1 (build 2600.xpsp_sp3_gdr.090206-1234: SP 3).

I have the following list of numbers.
C-B
0.044991
0.012016
0.027636
0.187865
0.01452
-0.104374

I try to sort them by highlighting them and choosing Data:Sort
Sort by C-B. Header row, TRUE.
The numbers remain unchanged.

What gives?

Many thanks in advance,
Peter.
 
Good chance that Excel sees the "numbers" as text, especially if copied from
somewhere else.

Select the numbers and Data>Text to Columns>Finish

Try the sort again.


Gord Dibben MS Excel MVP
 
Good chance that Excel sees the "numbers" as text, especially if copied from
somewhere else.

Select the numbers and Data>Text to Columns>Finish

Try the sort again.

Gord Dibben MS Excel MVP
Click to expand...

Thank you for your reply. Unfortunately that did not work. I should
have mentioned that the data was in column I. I obtained the first
element with =$C2-$B2 and dragged the cursor down to get the remaining
elements in the column from the values in columns B and C.
 
Peter,

Excel is simply recalcing the formulas _after_ the sort and getting the same
values. Copy the cells then paste special values before the sort.

HTH,
Bernie
MS Excel MVP
 
Peter,

Excel is simply recalcing the formulas _after_ the sort and getting the same
values. Copy the cells then paste special values before the sort.

HTH,
Bernie
MS Excel MVP
Click to expand...

Didn't work for some reason. What option was I supposed to use for
paste special?

I managed to get it to sort by saving the page as a text file and then
reloading it so the numbers were just numbers rather than
derivations. I have managed to do it with derivations on another PC
so I don't know why it is not working here.

Thanks,
Peter.
 
Peter,

Select the cells that you want to sort, right-click and choose "Copy", then
right-click the same selected cells and choose "Paste Special..." -
click the button next to "Values" then press OK.

Then - do your sort.

HTH,
Bernie
MS Excel MVP
 
0.044991
0.012016
0.027636
0.187865
0.01452
-0.104374
Click to expand...


Works fine on mine.

Perhaps even though you changed the cell formatting to be proper, the
originally entered data is still text.

Cut and paste the list into notepad, and then copy and paste it back
into excel from there. That will strip any formatting, and paste only
numeric data into a numeric cell.
 
Didn't work for some reason. What option was I supposed to use for
paste special?
Values.

I managed to get it to sort by saving the page as a text file and then
reloading it so the numbers were just numbers rather than
derivations. I have managed to do it with derivations on another PC
so I don't know why it is not working here.
Click to expand...

Because of the way they were originally entered from where you pasted
them, despite the cell formatting.

Hand enter them again, and that should change.
 
Back
Top