Excel Intro:
Sort

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

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

Before you start...

Project 1: Excel Intro
    InterfaceTo subtopics
    Select & Navigate To subtopics
    Common Tasks To subtopics
     footprintAutoSum
     footprintSort
     footprintChart Wizard
     footprintNumber Formats
    Summary
    Quiz
    ExercisesTo subtopics

Project 2: Excel BasicsTo subtopics

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics

Search
Glossary
Appendix
Icon Step-by-Step

Step-by-Step: Sort

  Icon Step-by-Step

What you will learn:

to sort cells
to sort rows
why sorting cells containing formulas is a bad idea

Start with: Icon - Excel (Excel open to budget.xls from resource files)

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 Button Sort Ascending 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!

    Message: Sort WarningIcon: Excel 2002Icon: Excel 2003 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.
     

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

    Original range A44 to A46

    Cells A44:A46 sorted

    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. Sorting by rowsClick on Button Sort Ascending 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.

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

    TipYou should do your sorting BEFORE creating formulas.
     

  5. Undo.