# Jan's Working with Numbers

## Formulas: Changes: Sorting

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!

### How to fix sort errors?

• Rearrange the sheet so that the formula refers only to cell's in its own row
• Rearrange the sheet so that the formula uses absolute references to cells that are not sorted.
• Sort first and create formulas afterwards.

## 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

### 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]