Jan's Working with Numbers

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)

Sorting Fails with Merged Cells

Nothing teaches like experience, so let's do some sorting that will fail.

1. Open trips27-Lastname-Firstname.xlsx.
2. Save As trips28-Lastname-Firstname.xlsx in the excel project4 folder of your Class disk.
   

3. On the Specials sheet, select the upper table, A4:F23.
   

4. On the Data tab, click the large Sort button .
   The Sort dialog appears.
   

5. 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.

6. Click on OK to close the dialog and cancel your sort.

---

Write Formulas Using Values from Two Rows

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.

1. On sheet Specials create a new column label  Change from last week  in cell H28 at the right of the lower table.
   The label formatting is automatically applied. Sweet!
2. On the Home tab, click the Wrap Text button.
   The row automatically resizes.
3. Resize the column's width so that the label fits on 2 lines.
4. AutoFit the row height for row 28.
   

5. 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.
   

6. Use AutoFill to copy this formula down the rest of the column to row 36.

   

7. Save.
   [trips28-Lastname-Firstname.xlsx]
    

---

Sorting Creates False Calculations

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!

1. Select the column labels and data for the lower table, range C28:H36.

2. Sort based on the Total column only from Smallest to Largest.

   The values in the last column change, not just move with the sort!

   

3. 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.

4. Conclusion: You just cannot sort after creating formulas that reach out to other rows.

5. Undo the sort, since the values in the last column are just plain wrong.
6. Save.
   [trips28-Lastname-Firstname.xlsx]