# Excel Intro: Sort

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

One of technology's blessings is the ability to put a lot of data into alphabetical or numerical order with the click of a mouse. Excel makes this easy to do, perhaps too easy. There are dangers in sorting data without thinking carefully about what you have selected to sort.

### Dangers

1. Separating Data:  If you have related data in rows and you sort just some cells, you may separate parts that belong together. Mr. Green's phone number may wind up by Miss Featherstone's name! Mr. Cartwright's Amount Due might wind up as part of Mr. Garcia's bill.

2. Changing Formulas: When you move cells around, formulas that use those cells will change to match. That's good. If you move the cell with the formula in it, the formula will change, too. That's not just bad, it can be a total disaster!

Where you are:
JegsWorks > Lessons > Numbers

Project 3: Format & Arrange

Project 4: Groups & Formulas

Project 5: Design

Search
Glossary
Appendix

## Step-by-Step: Sort

 What you will learn: to sort cells to sort rows why sorting cells containing formulas is a bad idea

### Sort: Cells

Most of the categories in budget.xls are already sorted into alphabetical order. But you can still play with sorting a little bit. Sorting cells is a problem if cells in a row must stay together.

1. Select cells A44, A45, and A46. Carefully note the values in column B for these rows.

2. Click on Sort Ascending.

The three cells are sorted alphabetically. But the rest of each row remained the same. Cells A44, A45, and A46 are now not labeling the values correctly. This was not such a good idea!

In Excel 2002/2003 you may get a friendly warning and an opportunity to change your selection. Select Continue with the current selection and then click on Sort.

Sometimes this message does not appear, even when you are making a big mistake with your sorting.

 Original order Sort Ascending
3. Undo.

### Sort: Rows

1. Click in cell B44 and note the formula used =SUM(B7:B13), which adds up the Inflows. You will need to compare it to the formula after sorting.

2. Select whole rows 44, 45 and 46. (Hint: Click on the row headers at the left.)

3. Click on Sort Ascending. The rows are sorted based on the values in Column A. This time the whole row is moved, so the values are kept together. But the values changed! What happened??

4. Click on cell B45 and note the formula that is supposed to be for Total Inflows. The Formula bar shows =SUM(B8:B14) That is not what we started with. Whoops!

Source of Error: When you sorted, the original cell B44 (Total Inflows)  moved down one row to B45. The cell references in the formula changed by 1 also! That's what relative cell references do. Since you did not move the cells that the formula refers to, you have a major error here.

Cells with Formulas: Sort rows that have formulas only if ALL the cells used in each formula are on the same row as the formula. Otherwise, after sorting, formulas will not refer to the correct cells anymore. Disaster!!

You should do your sorting BEFORE creating formulas.

5. Undo.