Glide Training Logo

Excel How-To Guide: Sorting (without messing up your data!)

  

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).

  1. 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.)
  2. Click on the DATA tab.
  3. Click on either the Sort Ascending sort ascending or Sort Descending 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 sort button

This will open the following sort dialog box:

sort dialog

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.


previous post: Excel How-To Guide: PivotTable Slicers

next post: How-To Guide: Reduce document size by compressing pictures