# Thread: Excel - effect on formulas when sorting columns

1. ## Excel - effect on formulas when sorting columns

I am needing to sort by the data in a column in an Excel spreadsheet and I need know the effect it will have on formulas.

I've done a few tests, and it seems like if the formulas only refer to other cells that are on the same line as the cell with the formula, and only use relative cell references, that the formulas will still return correct results after sorting by the data in a column.

Am I correct in this assumption?

Thanks

2. Sorting formulas is essentially the same as copying formulas. If a formula goes from row 2 to row 4 and that would be the same as if the formula were copied from row 2 to row 4 then there is not problem with the formulas. if copying the formulas would result in different results the sort would not work as desired.

Steve

3. I'm not following what you are saying. Seems like you are talking about copying just a formula, as opposed to moving an entire row and its data. Seems like what applies in one case would not necessarily apply in the other.

If a formula is copied from one cell to another, my understanding is that it would then be using new data in its calculation, that new data being the data in the newly referenced cells that are called for by the formula when it is in its new location.

But what I am hoping to confirm is that the same data would be used by the formula, since the cells that are referenced by the formula are on the same row and as such are being moved with the formula when the sort is done.

4. ## Sort Formula Example

Vincenzo,

Maybe this will help explain the results of full row sorts with formulas.

5. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Vincenzo (2011-02-22)

6. The effect on the formula after a sort is the same as copying. A sort does not move the row contents, it copies them. The formula may (or may not depending on the abs/relative references) use new data, just as a copied formula does.

My point is that if the formula can be copied from 1 row to another and the effects don't change, the sorting will not affect the formula results. If the formulas are so complex and so dependent on position that copying them changes the relative contents, then don't sort them.

The sorted formulas are not moved. A formula that starts in A3 and ends up in A5 would be the same as if copied from A3 to A5. So if it refered to a cell in B3 and C3 and they were included in the sort, it will use the same values but it would not be using B5 and C5. It will use the same absolute references and the relative row references will be changed.

Note though that a formula not in the sort that used A3 before the sort will still use A3 after the sort, it will not point to A5 (as MOVING would do).

Steve

7. ## The Following User Says Thank You to sdckapr For This Useful Post:

Vincenzo (2011-02-22)

8. sdckapr,

Thanks for the info, it helped clear things up. But one point still confused me.

You said:

A formula that starts in A3 and ends up in A5 would be the same as if copied from A3 to A5. So if it refered to a cell in B3 and C3 and they were included in the sort, it will use the same values but it would not be using B5 and C5. It will use the same absolute references and the relative row references will be changed.

This does not make sense to me. If the formula was essentially copied from A3 to A5, then it would then be using B5 and C5, as implied when you said the relative row references would be changed.

Anyway, it seems like I can conclude that a sort as specified in my initial post (the formulas only refer to other cells that are on the same line as the cell with the formula, and only use relative cell references) would not have any negative effect on the results of the formulas.

I think that is what RetiredGeek was alluding to in his post, but since he just posted it and ran without any insightful conclusions, leaving it as an exercise for the student, I cannot be sure

9. Sorry I wrote it wrong (poor proofreading). I meant it would NO longer be using the cells B3 and C3, but instead would be using B5 and C5. The copied formula would return the same numbers since the values that were in B3 and C3 would now be in B5 and C5.

Again I apologize for the confusion. I think you understand the nature of the sort as copy as opposed to move.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•