ALL ABOUT OFFICE

EXCEL

HOME

ACCESS

EXCEL

WORD

OUTLOOK

POWERPOINT

Sorting Data

If you click in a column in a spreadsheet, you can then click on the Ascending Sort or Descending Sort icon to sort the spreadsheet by that column.  Excel will normally ignore the first heading row above your data and sort the data by the column your cursor is in.  If you have used more than one row for your column headings this may cause problems when you sort, wrap the text in one cell to make your column heading more than one line high.  The example below shows a spreadsheet before and after a simple descending sort on column D.

Notice the way that rows 2 and 3 have also been sorted.  To avoid this, the column heading should be entered in one cell and wrapped onto several lines using the Alignment section of the Format Cells window.

If your spreadsheet includes a summary row at the bottom you will need to leave a blank row before the summary row so that it is not sorted with the rest of the data.
For a more complicated sort you should click on the Data drop-down menu and choose Sort, the Sort window appears as shown below.

If you place a bullet in the section to show that your list has a header row, Excel will use your column headings as names to sort by.  In this example we are sorting by descending height, then by descending weight and then by descending body mass index.   The result is shown below.

Notice how people with the same height are then further sorted by their weight.  Be aware that if formulas in your sorted data refer to values in other rows then they will not give the right answer when sorted.

If you have set up a custom list you can also use this to sort by.  Suppose we wanted to sort the days of the week in order, we could select Excel's in-built custom weekday list from the window shown below.  When selecting the first column to sort by, click the Options button at the bottom of the window and you will be able to choose a special sort order for the first key from the window shown below.  Note that you can only set this for the first sort key, not for the subsequent sorts within the main sort.

   

Click here to return to the Excel index.

webmaster@allaboutoffice.co.uk