Advanced Filter

Advanced Filter in Excel 2007
One excellent method to get information from your Excel data is to use the advanced filter feature. With the advanced filter you can apply multiple criteria to filter your data. For example, you may wish to know the name of the salesman who performed best in the first quarter or which student scored greater than 80% marks in Maths and also achieved a total of greater than or equal to 400 marks out of a total of 500. The training video below demonstrates the concept quite clearly.

The steps involved in the advanced filter implementation are as follows:

  • Select the headers
  • Copy them.
  • Paste the headers, leaving 2-3 or columns rows free.
  • Now write the criteria under the header according to your decision to extract certain data, for example, ‘>=80’ under ‘Marks in Maths’ header and ‘=”A*” under the header Name to extract the names of all students whose name begins with A. The ‘*’ is called a wild card character and in our example, it only means that the name should start with A and it does’t matter how many characters come after that..
  • Now click anywhere inside your data
  • Then click on the ‘Data’ tab, and under the group ‘sort & filter’ select ‘advanced’
    A new pop-up window with the tilte ‘Advanced Filter’ appears and the list range is automatically selected in the worksheet and entered in the text box next to the ‘list range’ in the pop-up window. •In the textbox next to the the ‘criteria range’, you can either enter the range manually or select ‘A26:A27’.
  • Finally click ‘OK’ and you observe that your data has been filtered according to your specified conditions.
    Tip: If you find the size of the video small, you can increase the ‘zoom’ level to 150% or 200%!.

Further reading:

Filter by using advanced criteria