Excel Tricks: Filter & Advanced Filter
One of the most frequent and useful ways to analyze data is by filtering and partitioning. In the last post in this series, there was a very nice description of the use of pivot tables. In this post, we will be discussing the use of another very useful ability in excel- the “filter” & “advanced filter” options under the top ‘Data’ menu for an excel file.
Lets use a single sample’s immunoSEQ data for the illustration. Once you have opened the file in excel, select the “filter” option from the top ‘Data’ menu as shown:
A drop down like button, such as the ones shown below, appears for each column. Once you click on this button for a column, a menu appears such as the following:
Once the data is filtered, the rows are hidden (the row numbers highlight in blue font when rows are hidden) and only the filtered data is displayed. This can then be copied to a new worksheet or text file.
Some of the main things one can do with this menu are:
1. Easily sort the whole table based on a particular column’s values or color
2. Filter or partition and display only the data for certain rows based on certain entries for this column
3. Filter or partition the data real time based on entry in the ‘search field’. This, as shown below, is very useful for looking at data for a set of amino acid or nucleotide sequences
There are several uses of this feature in looking at the immunoSEQ CDR3 sequence data for a sample. Some of those are in partitioning, displaying and obtaining data in all or some of the column fields based on :
i. filters for V and J genes to look at particular VJ usage
ii. filters for CDR3 sequence of particular length for all genes or particular gene of V or J or VJ combination
iii. filters on the copy counts to obtain CDR3 sequences that meet a particular threshold for clonal sequence
There are definitely more combinations in which one could use the filters to obtain the information one would need from our immunoSEQ CDR3 sequence data. Please do play around with your data and don’t hesitate to contact us if you have any questions.