Sorting and Filtering

By | February 27, 2017

Sorting and filtering are two ways or organisation data on a spreadsheet or database in order to make it easier to analyse and use. Sorting is the process of putting all the data in a particular order, whereas filtering allows you to turn your headings into drop down menus to display only the information you want to see.

Sorting Exercise

Before starting this exercise, explore the “sort” function, note how you can sort by multiple different layers.

Open the League data worksheet

  1. Move the title from the first row, to a Header. Delete the first two rows to tidy up the formatting.
  2. Format all data to Tahoma font size 11 but italic.  Ensure all data is visible.
  3. Freeze panes on the first row including the titles.
  4. Total the Games Played and the Point columns on row 12 or below
  5. Use a function to find the average Goals For and Against
  6. Sort the teams into alphabetic order of name
  7. Sort the teams into descending order of points.  If the points are equal then sort on the goal difference, again in descending order.  Then sort on the number of goals-for (descending).
  8. Which team is bottom, which team is top?

Filtering Exercise

Before starting this exercise, explore the “filter” function; highlight the headings you want to add a filter to then click on “filter”.

Open the workbook called Pivot Data.

  1. Select the data and apply filters
  2. Filter on Month 2 – how many records are there?
  3. Filter the records for the Cheshire region.  On top of this apply a second filter to find those in Cheshire who have enrolments greater than 30 – who and what number of enrolments?  Clear filters.
  4. Apply a filter to find the records of all colleges that begin with the letter B.  Clear filters.
  5. Apply filters to find the records of those who have above average enrolments – how many?  And where the funding is above 200 – how many now?  Clear filters.
  6. Sort your data by Month ascending
  7. Click anywhere in your data and apply subtotals giving the sum of funding for each change of Month, what are the three monthly funding totals?
  8. Remove subtotals.  Now sort your data by Region ascending.
  9. Now apply subtotals to show the largest single amount of funding per month for each region.  What region has the largest and what is the amount?

For more information about using the Subtotal function when using filters see: