# Data Groups & Formulas: Sorting & Formulas

Did you want Working with Numbers: 2007,2010,2013,2016  or español

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

Project 2: Excel Basics

Project 5: Design

Search
Glossary

Appendix

## Step-by-Step: Sorting & Formulas

 What you will learn: how sorting breaks formulas

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.

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

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

4. Save as  trips29.xls
How to handle a full disk