Advanced Filtering

Contents 1 Advanced filtering allows you to extract a subset of rows from a worksheet based on a list. 2 Open http://rous.mit.edu/~charliew/Biol2Bioinfo_Materials/ExampleDataPart.xlsx ExampleDataPart.xlsx] 2.1 Create a Criteria List 2.2 Criteria List should look like this 2.3 Paste it a few cells beneath the column being used to filter the data 2.4 Select "Advanced" in the Data menu to activate a dialog box asking for list and criteria ranges. 2.5 Select them appropriately and click OK 2.6 Only filtered rows will be displayed. Successful filtering is indicated by blue row numbers. 3 WARNING 3.1 When searching with words (rather than numbers), the Advanced Filtering function will simply match by beginning-of-string identity. Thus, searching e.g. for 'MARCH1' with Advanced Filtering will find not only 'MARCH1' but also 'MARCH10' and 'MARCH11'!

Advanced filtering allows you to extract a subset of rows from a worksheet based on a list.

Open http://rous.mit.edu/~charliew/Biol2Bioinfo_Materials/ExampleDataPart.xlsx ExampleDataPart.xlsx]

Create a Criteria List

Criteria List should look like this

Gene Symbol
Epcam
Esco1

Paste it a few cells beneath the column being used to filter the data

• NOTE: Be sure this sub-list has the same column name as the list being filtered.

Select "Advanced" in the Data menu to activate a dialog box asking for list and criteria ranges.

• Note activation is done using Data-->Filter-->AdvancedFilter

Select them appropriately and click OK

Only filtered rows will be displayed. Successful filtering is indicated by blue row numbers.

WARNING

When searching with words (rather than numbers), the Advanced Filtering function will simply match by beginning-of-string identity. Thus, searching e.g. for 'MARCH1' with Advanced Filtering will find not only 'MARCH1' but also 'MARCH10' and 'MARCH11'!

SOLUTION: For both search items and to-be-searched items, use the Excel function "=concatenate(..." 
to create a new column by appending a delimiting character, e.g. '^'. 
In this example, the resulting string 'MARCH1^' will no longer match 'MARCH10^' and 'MARCH11^'.