Why does sorting a column in descending order change the values in the cells?

[Hunter McGuire]

Hi guys,

I am trying to do a simple Excel task and finding that when I do it, my data automatically changes. What I'm trying to do is sort my spreadsheet by a specific column, in descending order. (I need the whole spreadsheet to be sorted by this one column.) By when I highlight the column and click "Sort" and then choose "Descending," all the values in that column's cells change. It is bizarre.

There are no blank cells in the column I wish to sort by, so that's not the issue. One thing that may be to blame is, I converted this column's values to decimal numbers from percentages. (I did that because I couldn't figure out how to sort the percentages, so I thought it would be easier with decimals.)

Can anyone suggest a solution? I am attaching the spreadsheet. The column I wish to sort by is Column N, which is labeled "Change."

[ben_hensel]

Get rid of the blank columns (O, X, Z).

Their existence is telling excel that the stuff on either side of them are different tables.

So if you sort/filter the stuff in columns A-M, it won't move anything in columns P-W.

Since column N is dependent on column W, that's the source of your error.

[Andy Pope]

Can you give an example of the before and after value as it's not clear what you mean.

One thing to be aware of if your data range is not contiguous. Column O is empty therefore columns to the right will not automatically be included in the sort.

[Hunter McGuire]

Hi Andy, sure thing.

For example, the value in cell N9 before sorting is 2.458. After I sort (by descending order), that value (which now appears in cell N14) changes to 1.271. So odd.

Were you able to open the spreadsheet and play with sorting Column N yourself?

I don't know if this is relevant, but as I said before, Column N was all percentages which I converted to numbers. Not sure if that might be throwing a wrench in the gears.

Thanks

[Andy Pope]

For me the value in N9 becomes n/a. The rest of the column is formula for the row and the values appear sorted largest to smallest.

I don't see 1.271 in the column N

Works both as numbers or percents

[Hunter McGuire]

Oh whoops, I didn't see ben_hensel's reply. Well, Ben Hensel, you were right -- I removed the blank columns like you said, re-did my sort, and voila! It worked. I am a happy, happy man, and it's all because of you, sir. I hope you have a great day and a great weekend!