Sorting and Filtering in Excel

Document Sample
Sorting and Filtering in Excel Powered By Docstoc
					Sorting and Filtering in Excel
The use of sorting and filtering in Excel allows it to perform as a simple database and for many tasks its
relative simplicity makes it a preferable option to a full relational database.

                                                                       Data Menu

                Click here to
                select all the data
                                                                                      Filter drop
                                                                 Select Column        downs
                                                                 to sort by

                                                                    Set Sort Order

      Select whether data has
      a header row or not

To Sort
Sorting can be done by up to three fields; it can be done alphabetically, numerically or by date/time
(provided the cells are set to the correct format). Records can be sorted in either ascending or
descending order.

    Ensure that all data is selected
    From the menu bar select Data and from the options select Sort
    Select the option as to whether your data has a header row or not
    Using the drop down lists select the columns you wish to sort by, top to bottom, in order of
     priority. (If your data does not have a header row you will be offered a choice of column letters)
    Select whether the sort should be in ascending or descending order
    Click OK

Tips When Sorting
  Do not leave any blank rows either between the data you wish to sort or between the headings
     and the data
  Ensure you select all the columns
  If you accidentally sort without selecting all the data use the undo button immediately and start
  If your data is the wrong way round to sort by column you can set the sort to be done by row. To
     do this click the Options button of the Sort Dialogue box.
  The Options button will also allow you to select a sort of days of the week or months of the year
     in chronological rather than alphabetical order
  Having a sequential record number (in the example above the ‘ID’ column) will allow you to
     return your data to its original order without difficulty.
To Filter
Filtering can be done by as many fields as you desire eg in the example above by filtering on a number
of fields you could show only those houses in Redwood, with a garage, costing less than 100,000

    From the Data menu select Filter
    From the options select Autofilter
    Each column heading will now have an arrow beside it. Clicking on the arrow will give a list of
     filtering options.
    You may then choose a specific value to filter on eg in the Garage Column you would have the
     option to filter ‘Yes’ values.
    Alternatively you could choose a ‘Top 10’ filter (this is not as limiting as it sounds This type of
     filter allows you to filter by top or bottom values, 10 can be can be altered to any number and you
     can show either the top 10 records or the top ten percent of records)
    To turn off the filter either select ‘All’ from the Filter Options or from the Data menu select Filter
     and the Show All

                                            Select amount
                                                                             Select Items or
             Select Top
             or Bottom

To Custom Filter
Custom filters can be used to set more complex criteria for filtering eg you could set a filter to show
only those houses valued at more than 200,000 or those which had a date of instruction between the 1 st
and 18th of May

                                                       Select Conditional
                                                                                 Select or
                                                                                 type criteria

              Add additional

    From the Filter Options list select Custom
    In the Custom Filter Dialogue box the box allows you to set the type of criteria, clicking on the
     dropdown list will present a list of options such as ‘greater than’ or ‘contains’
    Moving to the second box you may either use the dropdown list to select a value from the list or
     type your own value.
    You may then if you wish set a second criteria by setting the ‘And’ or ‘Or’ option and repeating
     the process above

Tips When Filtering
  Sorting on a Filtered record set will only sort the filtered records and not the entire record set.
  To further limit the information on view you can hide columns as required
  Copying from a filtered spreadsheet will only copy the filtered records and not the entire record
  When using a Custom Filter wildcards can be used to extend the range of options
  You can tell which fields have an active filter because the arrow on the Filter Option dropdown
     box will turn blue

Shared By: