# Need help sorting in Excel **URL:** \ **Category:** Factual Questions\ **Created:** [December 9, 2003, 3:00am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841 "2003-12-09T03:00:55Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [December 9, 2003, 3:00am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/1 "2003-12-09T03:00:55Z")
I have some rows in Excel with formulas that simply refer to values in other rows: =A1 =A2 =A3 The original data is something like Smith, Snuffy Jones, Barnaby Doe, Bambi I want to leave the original data alone, but sort the rows with the formulas according to the data displayed by those formulas. I want the rows with formulas to show Doe, Bambi Jones, Barnaby Smith, Snuffy and therefore have formulas =A3 =A1 =A2 But when I sort, the formulas are still intact, showing the same data as before. I am trying different things to narrow down the problem and getting even more bizarre results. Like switching a couple of rows around, and then sorting, and then those rows get completely different formulas. I have scoured Excel Help but Excel Help is only good for the most straightforward issues. I tried MS Knowledgebase, but the closest I came was how to set up the formulas so the data displayed will not change if you sort the original data, kind of the converse of what I want. ---
**Author:** ![splatterpunk](https://avatars.discourse-cdn.com/v4/letter/s/3be4f8/32.png) [@splatterpunk](https://boards.straightdope.com/u/splatterpunk)\ **Post date:** [December 9, 2003, 3:07am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/2 "2003-12-09T03:07:43Z")
I’m not getting it. Why don’t you just sort column A along with the rest of the data? ---
**Author:** ![interface2x](https://avatars.discourse-cdn.com/v4/letter/i/c0e974/32.png) [@interface2x](https://boards.straightdope.com/u/interface2x)\ **Post date:** [December 9, 2003, 3:25am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/3 "2003-12-09T03:25:39Z")
I just cut and pasted your exact names and used the links you listed - no problems resorting here. I just highlighted the cells with the links, sorted alphabetically and it worked like a charm (the formulas switched from A1, A2, A3 to A3, A2, A1). ---
**Author:** ![splatterpunk](https://avatars.discourse-cdn.com/v4/letter/s/3be4f8/32.png) [@splatterpunk](https://boards.straightdope.com/u/splatterpunk)\ **Post date:** [December 9, 2003, 3:34am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/4 "2003-12-09T03:34:52Z")
Never mind. I see what you’re saying. It works for me if I change the references to absolute references (=A1 becomes =$A$1, etc.). Then the results of the formulas sort correctly. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [December 10, 2003, 3:27am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/5 "2003-12-10T03:27:29Z")
> [@](#): > > \*Originally posted by interface2x \* > \*\*I just cut and pasted your exact names and used the links you listed - no problems resorting here. I just highlighted the cells with the links, sorted alphabetically and it worked like a charm (the formulas switched from A1, A2, A3 to A3, A2, A1). \*\* Hmmm. I wonder if I have some option set that is affecting this. I know that find & replace has an option to look at values vs. formulas but I didn’t see anything like that for sorting. Back to the drawing board. Thanks for the effort! ---
**Author:** ![interface2x](https://avatars.discourse-cdn.com/v4/letter/i/c0e974/32.png) [@interface2x](https://boards.straightdope.com/u/interface2x)\ **Post date:** [December 10, 2003, 3:55am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/6 "2003-12-10T03:55:38Z")
Sorry I couldn’t help more. ---
**Author:** ![Cyrokk](https://avatars.discourse-cdn.com/v4/letter/c/f08c70/32.png) [@Cyrokk](https://boards.straightdope.com/u/Cyrokk)\ **Post date:** [December 10, 2003, 11:17pm UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/7 "2003-12-10T23:17:09Z")
I think changing the formulas to be fixed (with a $ before the column letter and row number) as splatterpunk described is the only way to sort the formulas without the cell-referencing adjusting to the original data. The fastest way to change all the formulas to fixed references is to hilight the entire column and doing a replace, with “A” in the “find” parameter and “A”, then selecting “Replace All”. Unfortunately, each time you insert a new record you will have to resort the column with the formulas, but that’s not too much work since you will have to add a forumula in the new cell to pick up the new data. ---
**Author:** ![Cyrokk](https://avatars.discourse-cdn.com/v4/letter/c/f08c70/32.png) [@Cyrokk](https://boards.straightdope.com/u/Cyrokk)\ **Post date:** [December 10, 2003, 11:19pm UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/8 "2003-12-10T23:19:41Z")
That should read with “A” in the “find” parameter and “A” in the “Replace” parameter, then selecting “Replace All”. Sorry 'bout that. Cy ---
**Author:** ![Lord\_Ashtar](https://avatars.discourse-cdn.com/v4/letter/l/7ea924/32.png) [@Lord\_Ashtar](https://boards.straightdope.com/u/Lord_Ashtar)\ **Post date:** [December 11, 2003, 12:32am UTC](https://boards.straightdope.com/t/need-help-sorting-in-excel/217841/9 "2003-12-11T00:32:54Z")
In the formula where you have formulas, why not just Copy-Paste Special-Values. Then you can sort them without messing with the original column.