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
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.
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:
Project 1: Excel Intro
Select & Navigate
Project 2: Excel Basics
Project 3: Format & Arrange
Project 4: Groups & Formulas
Project 5: Design
What you will learn:
to sort cells
to sort rows
why sorting cells containing formulas is a bad idea
(Excel open to budget.xls from resource
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.
- Select cells A44, A45, and A46. Carefully
note the values in column B for these rows.
- Click on
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!
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.
message does not appear, even when you are making a big mistake with
- 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.
- Select whole rows 44, 45 and 46. (Hint: Click on the row headers
at the left.)
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
- 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.
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.
You should do your sorting BEFORE creating formulas.