Data Groups & Formulas:
Sorting & Formulas

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

Sorting data will usually 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 all 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 refers to cells that are not sorted.
     
  • Sort first and create formulas afterwards.

 


Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & Formulas To subtopics
    SubtotalsTo subtopics
    Images & DrawingsTo subtopics
    Changes & Formulas To subtopics
    FootprintCopies & Formulas
    FootprintMerges & Formulas
    FootprintSorting & Formulas
    FootprintInserting & Formulas
    Summary
    Quiz
    ExercisesTo subtopics

Project 5: DesignTo subtopics

Search 
Glossary  
Appendix
Icon Step-by-Step

Step-by-Step: Sorting & Formulas

  Icon Step-by-Step

What you will learn:

 how sorting breaks formulas

Start with: Class disk trips28.xls (saved in previous lesson)

Add Formula

  1. On sheet Specials create a new column label  Change from last week  in cell G28 at the right of the lower table.
     
  2. Resize the row and column as needed.
     
  3. Move the arrow over cell G34 to cell H34.
     
  4. In cell G30 type the formula  =F30-F29 . This subtracts last week's total from this week's total.
     
    G29 is blank since that row is for the first week. There is nothing to compare it to.
     
  5. Use AutoFill to copy this formula down the rest of the column to row 36.

Add column: Change from last week

Sort

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:G36.
     
  2. Sort based on the Total column only. The values in the last column change, not just move with the sort!
     
    Inspect the cells the the last column. The formulas remained the same. They did not change to match the new locations of the cells they refer to. There is quite a difference in the values.

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

  3. Undo the sort, since the values in the last column are just plain wrong.

    Table sorted on Total; Change from last week has changed

    Sorting rows with formulas that use values
    in other rows gives false values! You must undo!
     

  4. Class diskSave as  trips29.xls 
     Full floppy disk How to handle a full disk