Filter Data

Introduction

Filtering data in Gigasheet is a fundamental process that enables users to extract specific subsets of information based on defined criteria. By applying filters, users can narrow down massive sheets to focus on relevant records, improving data clarity and efficiency in analysis. Filters can be simple, such as retrieving all entries from a specific date, or complex, involving multiple conditions across various fields. This documentation will guide you through the principles, methods, and best practices for implementing data filtering in a database, ensuring accurate and optimized query results.

Please note: Data with filters applied will appear in exported data. Filters will only filter out up to 10,000 unique values. If more values exist, additional filters may need to be added

How to get there

Advanced Filters

Login to Gigasheet > Select a Sheet > Filters

OR 

Quick Filters

Select the Column Header > Filter By > Select Apply

Step 1: Once the filters box appears, select the column you would like to filter data from. If you would like to search any column, type and select "Any Column". This will autocomplete your column search. 

Step 2: Select the Operator.

Currently Gigasheet supports the following filters: 

• Equals 
• Does not equals 
• Is empty
• Is not empty 
• Contains
• Does not contain
• Starts with 
• Does not start with
• Ends with
• Does not End with
• Regex Match
• Found in sheet

Step 3: Type/select the value(s) 

Type and choose the values you would to search on each row. Press ENTER to add values. You can then choose to add additional filters by selecting "ADD RULE". Once you are done, select APPLY to populate the sheet with the filter. You can add multiple values and this field will autocomplete your value search. 

Note: You can select Match Case to allow filters to be case sensitive. 

Additional Filter Features

Filter by Group

Filtering by group requires two things: At minimum one group and a aggregation:

Once your data is grouped, go to Filters > Toggle Groups. Similar to the Advanced Filters, you can filter on your specific groups: 

Multi-Value Filters 

Multi-Value filters allow you to filter data with multiple filter values. Having multiple values works essentially like several “or” filters within one column. Say, for example, you want email addresses that contain either the @gmail.com domain, or the @outlook.com domain. To do this, we simply filter the email column to only contain values that have these domains. We would do this by entering these two domain strings separately, by hitting enter to separate the values.

AND/OR 

You can use AND and OR functions to create nested queries. This means you can apply more specific filters for your dataset. You can include data points that follow several “and” statements to really refine your search. Or, you could use “or '' statements, to widen your search.

Date/Time Filtering 

Gigasheet allows you to filter your date-time formatted data. This means you can filter dates by before a certain date, after a certain date, or between a range of dates. This can allow users to filter out data that is too old, or get data from a specific range of time. To do this, simply click the filter button, select the column that contains your date-time formatted data, determine whether you want before, after, or a range of dates. Then input your desired date to filter by. 

Gigasheet also allows you to explode a date. Meaning, you can pull the day of the week from any date-time formatted date. For more information on exploding data, see this article. Once you have done this, you can filter to only include certain days of the week.

Filter Row Flags

Row flags are identified by right clicking a value and selected "Good, Suspicious, or Bad". Once rows are flagged, they can be filtered by selecting "Row Flag" in the column field:

Wildcard Match

In Gigasheet, a wild card match refers to a technique used to find and extract data that matches a specific pattern. A wild card character is a symbol used to represent one or more characters in a text string. For example, the asterisk (*) is a common wildcard character used in Gigasheet, and it represents any number of characters in a text string. Using wildcards in combination with the "MATCH" function, Gigasheet can search for and return the position of a specific value or pattern within a range of cells. This can be particularly useful when working with large data sets or when searching for data that does not match exact values. For example, if you have a list of customer names, you can use a wildcard match to find all the names that contain a specific set of characters, such as all names that contain "Jones" or "Johnson".

Regex Match

You can filter with Regex Match by selecting the filter icon, then select the column you wish to filter. Next, select “Regex Match”, and input your desired search string. After applying the filter, you will see only data points with the desired string. Please note, if the string is within a larger string, the data will be shown. Say you type “street”, all addresses with the word “street” will show.

Found in Sheet

Using Found in Sheet allows you to search one sheet using the values in another.

This option allows you to use the list of values found in a column in one sheet as the criteria to be used in the filter condition in another. Using the above screenshot as an example, if the "Pediatrics Payer Report" sheet had the values "steve@gigasheet.com", "will@gigasheet.com", and "ryan@gigasheet.com" in the column "Payer", the sheet being filtered would then use those values to isolate to the rows with those values in the Email column. In effect, the filter here would be Email equals "steve@gigasheet.com", "will@gigasheet.com", or "ryan@gigasheet.com".