Posts Tagged ‘Wildcard’

Most of us who have worked with data in MS Excel have had to use Filter option every now and then. It helps when sifting through huge amounts of data and when one wants to look at only part of it at a time. e.g. looking at one region’s data from the national database or filtering on values that are greater than (or smaller than) a particular number and so on.

While Filter is a mighty useful feature in itself, there are certain limitations. e.g. if one wishes to filter on two columns but want records that satisfy “either” column criteria then one cannot do so using simple filters. Neither, can one apply multiple combinations of criteria. This is where Advanced Filter option comes into picture. It can do most of what one would need from an Excel application. Advanced filtering, similar to filtering, displays a subset of the rows in a table or range of data.

Using Advanced Filtering

Step 1: Set up the database such that it satisfies all the conditions below:

  • Should have headings
  • No blank rows in between
  • Blank row at end & blank column at right

Step 2 – Set up the criteria range. The criteria range headings should match exactly with the headings of the database

Step 3 – Set up the extract range

  • If one plans to copy the data to another location, one can specify the columns that he wants to extract. By default, ALL columns are extracted.
  • Select the cell at the top left of the range for the extracted data.
  • Type the exact headings for the columns that you want to extract

Step 4 – Apply the filter

On launching the Advanced Filter (Data tab -> Sort & Filter section -> Advanced) a dialog box appears
Continue Reading