Written by Jane Hames
OK, hands up who thought they knew how to sort a list in Excel and then totally messed up their data? My hand is up! I ask my workshop participants that question and I can’t think of an occasion when no-one has admitted to making the same mistake. Many of Excel’s most useful features are not obvious. Below are some hints for sorting data in Excel:
General Sort
In Excel 2010, you can sort on cell contents, font colour, cell colour or cell icon (conditional formatting).
- Click into any cell in the COLUMN you want to sort by within your list. (DO NOT highlight that column as this will sort that column only and leave the rest of your data where it is.)
- Click on the DATA tab.
- Click on either the Sort Ascending or Sort Descending button.
Performing a multi-level sort
Click into any cell in the column you want to sort by within your list and from the Data tab, click on Sort
This will open the following sort dialog box:
key to terms:
Sort By
Select the column heading for the column to be sorted by.
Sort On
Select whether to sort on Values (Text, Numbers or Dates/Time), Cell Colour, Font Colour or Cell Icon.
Order
Text values will be A to Z or Z to A.
Numbers will be Smallest to Largest or Largest to Smallest.
Date/Time values will be Oldest to Newest or Newest to Oldest.
Cell Colour/Font Colour or Cell Icon will be On Top or On Bottom.
Add Level
Click on Add Level to sort on additional fields.
Then By
Add a then by field to sort by the first column specified and then by another column.
Delete Level
Select the level to be removed and click on Delete Level to remove it from the sort options.
Copy Level
Click on Copy Level to copy the field options from the row above.
Move Up / Move Down
Select a field and click on Move Up or Move Down to move it up or down the list.
My Data Has Headers
Select My Data Has Headers if your database has a header row above the data
Options
Use the Options button to make the sort case sensitive or to change the orientation from top to bottom to left to right.
Click on OK.