How to Sort Data in Excel Without Messing Up Formulas

If you have a formula that involves multiple sheets and you later sort that data you may notice your cell references are now out of order and need to be corrected. It can be frustrating and dangerous because you may not realize your formulas are now calculating different cells.

For example, I have created a sheet called InventoryCount and another called Total. The InventoryCount sheet acts as a tally of all the locations a certain product number is found:

On the totals sheet, I have a summary of these product numbers using a SUMIF formula that multiplies by the price.

The formula in the highlighted cell above is as follows:

=SUMIF(InventoryCount!A:A,Total!A2,InventoryCount!B:B)*B2

The formula in the price column for product 1 is:

=VLOOKUP(A2,InventoryCount!A:C,3,FALSE)

Both formulas are referencing the InventoryCount sheet. The formulas in the value column are correct. But let’s say that I want to sort that column by values. If I sort in descending order, this is what I get:

The values are not only not correctly sorted  (product 1 is lower than product 2’s value) but the values have changed. If you look at the values before the sort product 3 dropped from 10,956.16 to 4,547.84. There is nothing glaringly obvious that the calculation is now completely incorrect so you can imagine the danger when dealing with lots of data that such a sort could make your data get altered. The formula for the value in product 1 now looks like this:

=SUMIF(InventoryCount!A:A,Total!A2,InventoryCount!B:B)*B3

It is still referencing A2. So technically the formula is still correct, the problem instead is product 1 is no longer on row 2, it is on row 3. The assumption that the value in row 3 relates to the product on row 3 will now be incorrect.

So why did this happen? Notice that the price has not changed for any of these products, Product 1 was $3.45 before the sort, as well as after. This column still is correct in relation to its corresponding products.

If you go back and look at the two formulas (price and value) you will notice on key difference: the cell referenced on the current sheet (Total) for the price does not mention the sheet name, however on the value formula it does.

This is the original formula to calculate the value:

=SUMIF(InventoryCount!A:A,Total!A2,InventoryCount!B:B)*B2

The Total! reference is causing the sorting issue and needs to be removed. The updated formula becomes:

=SUMIF(InventoryCount!A:A,A2,InventoryCount!B:B)*B2

Now if I apply the same logic to the other formulas in the value calculation and sort the data in descending order, below is the result I get:

The values are back to what they were at the start and product 3 is the highest as it was initially. Except now they are correctly sorted in descending order with the product number correctly being referenced in column A.

So the lesson here is that if you are using formulas with multiple sheets get rid of the sheet reference when referencing a cell on the same sheet as the formula is on (Excel inserts this reference automatically as you switch from one sheet to the next). Otherwise, if you or anyone else sorts the data the calculations will not be on the correct rows.

If you liked this post on How to Sort Data in Excel Without Messing Up Formulas, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.