Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Lessons > Jan's CompLit 101 > Working with Numbers > Formulas > Changes > Sorting

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.

Icon Step-by-Step

Step-by-Step: Sorting & Formulas

 Icon Step-by-Step

What you will learn: how merged cells prevent sorting
how sorting breaks formulas

Start with: Icon: Class disk trips27-Firstname-Lastname.xls (saved in previous lesson)

Sorting with Merged Cells

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

  1. Icon: Class diskOpen trips27-Firstname-Lastname.xlsx to the Specials sheet.
  2. Icon: Class diskSave As trips28-Firstname-Lastname.xlsx in the excel project4 folder of your Class disk.
    Icon: Full disk How to handle a full disk 
  3. Select the upper table, A4:F23.

  4. Dialog: Sort > Travel Agent, Values, A to Z (Excel 2010)On the Data tab, click the large Sort button Button: Sort dialog (Excel 2010).
    The Sort dialog appears.

  5. Select Travel Agent and Values and A to Z, then click on OK.

    Message: This operation requires the merged cells to be identically sized. A message box appears. Apparently now that you have merged cells in the Cost each column, you cannot sort these rows any more.

    Icon: TipSort first then format and merge data cells.

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

Add Column Containing Formulas

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. Add column: Change from last weekAutoFit 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.

    G29 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. Icon: Class diskSave.
    [trips28-Firstname-Lastname.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. Dialog: Sort > Total, Values, Smallest to Largest (Excel 2010)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.
     
    Table sorted on Total; Change from last week has changedThe 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?
    $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. Icon: Class diskSave.
    [trips28-Firstname-Lastname.xlsx]