Saturday, August 27, 2005

Sorting a list in Excel


There are several ways to sort a list in Excel.

On the tool bar there are some short cut buttons A to Z and Z to A.
These are context sensitive. That is they will sort the column of the selected cell(s) aplha numerically ascending and descending respectively. They will also by default sort the surrounding (connected) columns. If you have formulas in these cells that are relative to the row they are in this is not good.
Unless you are sorting simple lists this is probably not the best way to sort.

Clicking Data Sort on the Menu is also context sensitve. Excel attempts to guess what your sorting requirements are. If there are unique headers it will generally determine this is a header row and identify these values as the columns headers. Else it will provide columns labels; Column A, Column B etc.
This sort option gives you a number of additonal options.
  • You can choose to sort by columns or rows by clicking Options and choosing the Orientation you need.

  • You can select up to 3 sort options and choose ascending or descending for each.

  • You can turn on or off the header row(column) option.


  • It is always a good idea to follow some simple rules when using or planning to use the Sort function.

  • Ensure that there is a header row for the data to be sorted.

  • Ensure that there is a clear space around the area to be sorted, at least one row at top and bottom and at least one column left and right. Alternatively select the area to be sorted first.

  • Don't use cell relative formulas in adjacent columns as these will be rearranged and return incorrect results after sorting changes orders.


  • Happy sorting!