my formula always messsed up while sorting in excel what should i do ??

Copper Contributor

it is my datait is my datait is after when i sort my data has mixed upit is after when i sort my data has mixed up

9 Replies

@kakashi890 What are you sorting on and what do you have in the PRICE column?

right, let me explain, I have 3 column and in price column I have the cost of each product customer bought, the formula in the price column is 300 in first cell, next cell is =300+256 which is 556, next cell is 556+256= 812 so the series is like top cell +256 is the next cell (I hope I am clear :P) The issue is when i try to sort it A To Z the formula got messed up and the numbers gets shuffled and shift from their original place.

@kakashi890 OK! I'm not surprised that it doesn't work but still don't understand what you have done exactly. Please include some screenshots that show the formulas/numbers that you have in the PRICE column. Or even better, provide a link to your file on OneDrive or similar.

@Riny_van_Eekelen ok! I had some more screenshots. but this time it shows error when I sort. I don't have any idea what I'm doing wrong.   


Something like in the attached file?


It is my dataIt is my datatis is the formula i had applied in the price columntis is the formula i had applied in the price columnI sort it  A TO ZI sort it A TO Zthe formula has messed up, after I sort itthe formula has messed up, after I sort it

here I had post some more screenshots,, sorry i did a mistake posting these screenshots last time, I think you'll understand more clearly now . i don't have a clue where I'm wrong. after I sort excel show this

@kakashi890 Well, you can see for yourself why it doesn't work. Click in the formula in C2 after the sort. It refers to the cell above (C1) and adds 256, resulting in the error. But then, when you come to naruto knife, the value in C9 is just the number 300. C10 seems to work again as it refers to the cell above (which now has the number 300) and adds 256. But obviously, that is not the correct value.


You simply can't sort ranges that contain formulas like that. The references are relative and do not 'stick' to the original "cell above". And if you make all reference absolute, they will not 'stick' either.


Depending on what you want to achieve, consider a copy/paste values of the PRICE column before sorting. That will fix the prices with the names and the items on the same row.

I guess you're right, in short you are saying that it won't work until I put an absolute value it the price column, I get it now. Thanks It was helpful