Sorting, filtering & finding with Excel 2003 and 2007

Excel provides a number of useful capabilities for sorting or filtering a table. The guidance notes here do not aim to provide Excel training but to provide some simple hints for users who are not familiar with Excel; there are plenty of other sources of additional information. Two versions are covered.

Choose from the links below:

Excel 2003

This page has been developed for users of Excel 2003, users of Excel 2007 can find an equivalent description here.

Sorting

The lists have been ordered by P500 Family Order (Smallest to Largest) and then by Height (Largest to Smallest) criteria. Of course, a list user may be interested in a different order and Excel provides flexibility. Let’s consider two common examples:

Suppose a user wishes to order the list by prominence rather than P500 family order. The following example shows how to re-sort the list using Excel 2003. If the Excel "data" topbar menu is chosen and "sort" is selected, then a sort popup appears.

Screenshot - Excel sort dialog for prominence

To obtain the desired sort, it is necessary to click on a cell then either to select Prominence Rank (col A) with ascending sort, or Prominence (m) (col B) with descending sort as the first category. Since this sort is going away from geographic location, a sensible second category would be peak height with a descending sort.

Taking another example, suppose a user wants to see which peaks are the highest P100+s in the UK. All peaks in the UK Prominent Peak database have a prominence of 100 metres or more so the whole database should be searched.

Screenshot - Excel sort dialog for height

To obtain this sort, click on a cell then choose Height (col C) with descending sort, then Prominence (col B) with Descending.

Other columns can be used for sorting too.

Filtering

Another way to manipulate the lists is to use Excel's filtering capability.

Screenshot - Excel filtering - column headings

When downloaded, columns N and O are configured to have a sort button (grey box with down pointing triangle). If you click on the grey box in col N, you will have a dropdown menu with check boxes, allowing you to choose between, 'P100', 'P1000', 'P200', 'P500' and 'blank' cells.

Screenshot - Excel filtering - column headings

If you click on P200, only the P200 peaks will be filtered from the list and the other rows will be hidden.

You can use filtering on any contiguous set of columns. For example, if you select columns G to O and choose "sort" from the data menu you can set a filter on all the selected columns.

Screenshot - Excel filtering - example of result

Finding

If you want to find a particular peak, e.g. Slieve Donard, you can bring up the find box using CTRL+F keys.

Screenshot - Excel find dialog

If you click on "find next" you will be taken to Slieve Donard in the list. The list will then scroll to show the cell with ‘Slieve Donard’ selected.

For further manipulation capabilities, look at Microsoft’s Excel documentation.

Excel 2007

This page has been developed for users of Excel 2007, users of Excel 2003 can find an equivalent description here.

Sorting

The lists have been ordered by P500 Family Order (Smallest to Largest) and then by Height (Largest to Smallest) criteria. Of course, a list user may be interested in a different order and Excel provides flexibility. Let’s consider two common examples:

Suppose a user wishes to order the list by prominence rather than P500 family order. The following example shows how to re-sort the list using Excel 2007. If the Excel "data" topbar menu is chosen and "sort" is selected, then a sort popup appears.

Screenshot - Excel sort dialog for prominence

To obtain the desired sort, it is necessary to click on a cell then either to select Prominence Rank (col A) with ascending sort, or Prominence (m) (col B) with descending sort as the first category. Since this sort is going away from geographic location, a sensible second category would be peak height with a descending sort.

Taking another example, suppose a user wants to see which peaks are the highest P100+s in the UK. All peaks in the UK Prominent Peak database have a prominence of 100 metres or more so the whole database should be searched.

Screenshot - Excel sort dialog for height

To obtain this sort, click on a cell then choose Height (col C) with descending sort, then Prominence (col B) with Descending.

Other columns can be used for sorting too.

Filtering

Another way to manipulate the lists is to use Excel's filtering capability.

Screenshot - Excel filtering - column headings

When downloaded, columns N and O are configured to have a sort button (grey box with down pointing triangle). If you click on the grey box in col N, you will have a dropdown menu with check boxes, allowing you to choose between, 'P100', 'P1000', 'P200', 'P500' and 'blank' cells.

Screenshot - Excel filtering - column headings

If you click on P200, only the P200 peaks will be filtered from the list and the other rows will be hidden.

You can use filtering on any contiguous set of columns. For example, if you select columns G to O and choose "sort" from the data menu you can set a filter on all the selected columns.

Screenshot - Excel filtering - example of result

Finding

If you want to find a particular peak, e.g. Slieve Donard, you can bring up the find box using CTRL+F keys.

Screenshot - Excel find dialog

If you click on "find next" you will be taken to Slieve Donard in the list. The list will then scroll to show the cell with ‘Slieve Donard’ selected.

For further manipulation capabilities, look at Microsoft’s Excel documentation.