Jan's Working with Numbers

Step-by-Step: Sort

Start with: budget-2010-Lastname-Firstname.xlsx at Budget sheet from previous lesson

You will not be saving any changes in this lesson but your instructor may want you to capture some screen shots to prove you did the lesson. Ask.

Sort: Cells

Most of the categories in budget-2010-Lastname-Firstname.xlsx 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.
   These are the labels for the totals rows.
2. Carefully note the values in column B for these rows.
    
3. On the Data tab, click on Sort A-Z.

   You get a friendly warning and an opportunity to change your selection.

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

   Alternate Method: On the Home tab, click the button Sort & Filter to drop its menu and then on Sort A to Z.

4. Select 'Continue with the current selection' and then click on Sort.
   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!
   
5. Undo.
   

Sort: Rows

1. Click in cell B44.
   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: Drag across the row headers at the left.)  
3. On the Data tab, click on Sort A-Z.
   The rows are kept together and sorted based on the values in Column A.
   But the values changed! And there are lots of green error triangles! What happened?? Let's check those formulas.
   
4. Click on cell B45
   This is the formula that is for Total Inflows that was in B44 originally.
   The Formula bar shows =SUM(B8:B14) That is not the range that adds up to Total Inflows. 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. So B7 became B8 and B13 became B14. But you did not move the cells that the formula refers to. You have a major error here.

   Sorting 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!!

   Do your sorting BEFORE creating formulas. 
   

5. Hover over the Trace Error icon to see what Excel thinks the problem is.
   Excel noticed that there are now adjacent cells that were not included in several formulas.
   Will adding those cells fix things?
    
6. Click on Update Formula to Include Cells.
   Whoops again! This new formula includes far too many rows!!
7. Undo until the sheet is back to the original formulas and values.
   
8. If you need to stop for now, close the spreadsheet:
   Excel 2007, 2010: Click the Close button for the worksheet.
   Excel 2013, 2016: File > Close
   

   Do not save changes.