# Excel Sorting Weirdness **URL:** \ **Category:** Factual Questions\ **Created:** [July 29, 2011, 6:02am UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709 "2011-07-29T06:02:03Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [July 29, 2011, 6:02am UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/1 "2011-07-29T06:02:03Z")
I’m working on a spreadsheet for my business. There is a field, calculated from other fields, which I want to sort the spreadsheet by. When I sort the data, I get the data ordered thusly: 139.00 108.50 23.32 29.53 35.00 17.50 8.64 13.99 6.82 6.17 5.60 10.34 10.29 10.07 9.80 17.88 8.56 8.11 7.78 7.71 7.52 14.00 6.67 6.62 6.26 5.95 5.92 2.54 4.86 4.65 9.07 3.66 1.75 7.00 1.74 3.46 For the record, here’s the formula used to generate this field: =IF(AF2\<599,0,((AI2_L2_AH2\*AG2)-AA2)) For confidentiality reasons, I can’t disclose what I’m actually counting, forecasting, etc here. ---
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [July 29, 2011, 6:44am UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/2 "2011-07-29T06:44:54Z")
Well, found a temporary work-around. Selected the entire column. Then copied. Selected an unused column. Then pasted, selecting the option to paste the values (the type of paste where it shows (1,2,3) in Excel.) Then sorted based on the new column. That sort worked logically, and perfectly. Still like feedback, though. ---
**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\ **Post date:** [July 29, 2011, 8:14am UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/3 "2011-07-29T08:14:46Z")
When you sort based on that column of numbers (139.00, 108.50, etc.) are you including in the sort range the other columns that have values for the formula? I suspect you’re not. If you are trying to sort just that one column it won’t work since the formula will just recalculate the values again. And of course if you copy the values of that column of number somewhere else and sort it, it will work since you’re sorting values and not the formulas. That’s all I can think of without seeing your worksheet. ---
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [July 29, 2011, 8:16am UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/4 "2011-07-29T08:16:46Z")
I selected the entire dad-gummed spreadsheet before sorting, I promise. ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [July 29, 2011, 12:12pm UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/5 "2011-07-29T12:12:38Z")
I made up a bunch of numbers and used your formula and my spreadsheet is sorting fine. Did you try copying the entire contents of the sheet and pasting them into a New worksheet? I’ve had a particular spreadsheet get wanky on me and not even do multiplication correctly in some cells, but if I just put the exact copy into a new worksheet it goes fine. ---
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [July 29, 2011, 4:48pm UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/6 "2011-07-29T16:48:08Z")
This Excel file is actually one big export from Access, then I paste columns AA and the like to the right of columns A-Z. I’ll try your suggestion. ---
**Author:** ![nivlac](https://avatars.discourse-cdn.com/v4/letter/n/3bc359/32.png) [@nivlac](https://boards.straightdope.com/u/nivlac)\ **Post date:** [July 29, 2011, 5:08pm UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/7 "2011-07-29T17:08:53Z")
If you still can’t figure it out, post the worksheet somewhere and we can look at it. It’s pretty tough to figure out what’s wrong from just your description. ---
**Author:** ![Mr.Slant](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Mr.Slant](https://boards.straightdope.com/u/Mr.Slant)\ **Post date:** [July 29, 2011, 5:10pm UTC](https://boards.straightdope.com/t/excel-sorting-weirdness/590709/8 "2011-07-29T17:10:26Z")
Will do. Haven’t had a chance to fiddle with it yet.