Excel2007:Advanced Filter

From virtualMV2015wiki
Jump to: navigation, search
MS-Excel 2007 <


Icon Introduction.png Introduction:

Overview

Excel allow you to extract data from a table based on a filter you create. In this example rows containing a specified Manufacturer are extracted and placed in another part of the spreadsheet. You need to know how to name ranges to do this.

Icon Objective.png

Objective Creating an advanced filter

By the end of this page you will be able to:

  • Use an advanced filer in MS-Excel

Creating an Advanced Filter

Excel 2007: Advanced Filter > click to zoom

Example :Click here to download vmvXL07_Eg01.xls.

A simple named cell

This short video explains how to create an advanced filter.

Sniplet:Excel2007:Advanced Filter


Click here to download Excel07_01NamedReference.wmv (6MB @ 02:14 min)
.

Icon Activity.png Activity:

Excel 2007: Spreadsheet for Named range example

What we are going to do is to find records in a list (we will call our database), based on a filter, and place them onto the same sheet below the filter.

  1. Create the spreadsheet as shown (it is the same one used for the Named reference)
  2. Highlight the database (A6:H11) note that this includes the table headings. Name this database
  3. Create the filter by
    1. Copy the database headings (row 6 (A6:H6)) to row 13 - use [Control C to copy > Control V to paste.
    2. In the row below the filter heading type in the criteria, here enter Samsung to find all the Samsung drives (see notes below for other options)
  4. Name the filter by
    1. Highlight the filter heading and criteria (A13:H14) and name as filter
  5. Display results starting in Row 16
    1. Click in A16
    2. Select the Data tab, then in the Sort&Filter group choose Advanced
    3. With the list range box highlighted, click F3 to get the named ranges and choose database, then [Ok]
    4. Click on the Criteria Range box, then [F3] and choose criteria, then [Ok]
    5. In the Action area on the radio button to Copy to another location
    6. Click in the Copy to: box, then click on the button at the right, a small dialog box appears.
      1. Click on A16 and this should appear in the box.
      2. Click on the button on the right of the text box and you should return back to the Advanced filter dialog box
    7. Click [Ok] to apply the filter

.. and thats all!!

Notes

Example criteria for Manufacturer (Note: you need the = sign before the string in quotes)

  • Samsung : Displays all those records with "Samsung"
  • =">Samsung" : Displays all records that alphabetically come after "Samsung"
  • =">S*" : Displays all records that alphabetically come after "S"
  • ="<>Samsung" : Displays all records that are not "Samsung"

References

 

Verhaart, M. (2017). Excel2007:Advanced Filter. Retrieved December 13, 2017, from http://www.virtualmv.com/wiki/index.php?title=Excel2007%3AAdvanced_Filter