Sorting data will almost certainly break a formula that refers to cells on other rows. The rows get rearranged in the sort, so the values are not in the same place.
If the formula uses only cells in the same row as the formula's own cell, then a sort will move them together. No problem!
Step-by-Step: Sorting & Formulas |
What you will learn: | how sorting fails with merged cells to write formulas using values from two rows create false calculations by sorting rows with the new formulas |
Start with: trips27-Firstname-Lastname.xls (saved in previous lesson)
Nothing teaches like experience, so let's do some sorting that will fail.
On the Specials sheet, select the upper table, A4:F23.
On
the Data tab, click the large Sort button .
The Sort dialog appears.
Select Travel Agent and Values and A to Z, then click on
OK.
A message box appears. Apparently now that you have merged
cells in the Cost each column, you cannot sort these rows any more.
Sort first then format and merge data cells.
The formulas already in this sheet were created correctly using absolute references. They will sort just fine when merged cells are not an issue. Let's create a new column that uses only relative cell references to see what problems that causes with sorting.
In cell H30 type the formula =F30-F29 .
This subtracts last week's total from this week's total.
You now
have a formula that uses cells from two different rows.
H29 must be left blank since that row is for the first week. There is nothing to
compare it to.
Save.
[trips28-Lastname-Firstname.xlsx]
The formulas you just created use cell references from two different rows. Excel does not expect you to sort after creating this kind of formula!
Sort based on the Total column only from Smallest to Largest.
The values in the last column change, not just move with the sort!
Find the row for week 2, which showed a
change of $19,000 in column H before the sort.
What does that row
show now, after the sort?
Only $9,000! Your sorting has made a liar out
of the table!
For week 2, the correct formula to calculate the change from week 1 should be =F32-F29, but cell H32 still contains the original formula, =F32-F31!
All the weeks have the same problem, except week 1 which did not move and did not have a formula in column H.
Conclusion: You just cannot sort after creating formulas that reach out to other rows.