Filtering spreadsheet data can be very useful when trying to select data from a large set that responds to certain criteria. For example, if there are three columns of data imported from a keyword research tool (such as the Google AdWords Keyword Tool), containing the Keyword, CPC (cost per click, a good indication of proportional revenue) and the Search Volume, and the user wishes to extract those keywords with a good CPC/Volume ratio, then a combination of searching and sorting can be used.
The underlying principle of the Google Spreadsheets Filter function is to take an array of data and return a modified version of that data as a like array. The arrays in this case are spreadsheet ranges – columns and rows of data that can be operated on in within the spreadsheet.
The Google Spreadsheet Filter Function
There are two possibilities when using the Filter function:
filter on a single condition
filter on multiple conditions
In each case, the filter requires a range to operate on in the first argument. Assuming that the columns A, B and C contain the data to operate on (Keyword, CPC and Volume), then a range can be constructed that contains data from columns A, A and B, B and C, or A, B and C. If the user needs to construct a range using columns A and C, then they will need to move the columns around as the Filter function can only operate on adjacent data.
Filter How-To Example : High CPC Keywords Filter
Assuming that the CPC is in column B, and there are 100 rows, then the filter formula can be constructed as:
=Filter(A2:B100, B2:B100 > Average(B2:B100))
This will return an array (range of two columns wide, and x rows deep), that contains all the keywords and CPC figures that are above the average CPC value. If the user also wishes to filter based on the Search Volume, then the additional condition can be added:
=Filter(A2:B100, B2:B100 > Average(B2:B100), C2:C100 > Average(C2:C100))
The reader will note that the range remains the same, and so the column C values are not returned.
It is vital that the Filter function be used in a separate column (i.e. D, in this case) to avoid overwriting the contents of the existing range.
Using these filters it is possible to return ranges or data that contains only the data that responds to certain criteria. The Google Docs Help System contains more detailed information relating to complex filtering and sorting of spreadsheet data, but the above should be enough to help any readers with their spreadsheets.
