Filter
To narrow down (filter) the data displayed in the sheet, follow the procedure below.
Click the header menu for the column you want to filter on and select “Filter” from the menu.
A dialog for setting the filter is displayed.
Filters by Data Type
Depending on the data type of the Salesforce field corresponding to the displayed column, the type of the filter displayed in the dialog will change. The following describes filter settings for each data type.
Text
If the corresponding Salesforce field is a text field, the following filter screen will be displayed.
Select the comparison method from the pull-down menu [A]
Equals To – Exactly matches the specified string in the filter
Not Equals To – Does not exactly match the character string specified in the filter
Includes – Includes part of the character string specified in the filter
Not Includes – Does not include any character string specified in the filter
Starts With – Starts with the character string specified in the filter
Ends With – Ends with the specified character string in the filter
Enter a character string to be a filter in the text input [B]
To enter multiple values, input strings separated by commas (,)
Click the "Apply" button at the bottom right of the dialog.
When the filter comparison method is "Includes" or "Not Includes", you can select the filter match determination method (match mode) when multiple values are set as condition comparison values from the menu [D] next to the text input.
Picklist
If the corresponding Salesforce field is a picklist field, the following filter screen will be displayed.
Select the value you want to apply as a filter from the selection list displayed in the dialog and check the check boxes [A]. Multiple entries can be checked.
Click the "Apply" button displayed at the bottom right of the dialog.
You can also add, edit, and delete the values of choices that can be selected as a filter by clicking the "Edit Options" [B] button.
For editing options, please refer to "Picklist Editor Setting" in "Changing Column Setting / Options".
Number
If the corresponding Salesforce field is a number field (including currency or percent field), the following filter screen will be displayed.
Select the comparison method for the lower limit of the filter from the pull-down menu [A].
Greater than or equal to – Includes the lower limit value in the filter condition
Greater than – Does not include the lower limit value in the filter condition
Enter the lower limit of the filter in the text box [B]. If you do not enter it, the lower limit is not applied as a filter.
Select the comparison method for the upper limit of the filter from the pull-down menu [C].
Less than or equal to – Includes the upper limit value in the filter condition
Less than – Does not include the upper limit value in the filter condition
Enter the upper limit of the filter in the text box [D]. If you do not enter it, the upper limit is not applied as a filter.
Click the "Apply" button displayed at the bottom right of the dialog
ID
If the corresponding Salesforce field is an ID field, the following filter screen will be displayed.
From the pull-down menu [A], select a comparison method.
Equals to – Matches exactly the ID specified in the filter.
Not equals to – Does not exactly match the ID specified in the filter.
Enter the ID value for the filter in the text input [B].
Click on the "Apply" button at the bottom right of the dialog.
Date
If the corresponding Salesforce field is a date or datetime field, the following filter screen will be displayed.
Select the date comparison method from the pull-down menu [A].
Starts from – Let the lower limit of the filter condition be the date period specified in the date input [B]. The date period of [B] is included in the filter condition.
Later than – Let the lower limit of the filter condition be the date period specified in the date input [B]. However, the date period of (B) is not included in the condition.
In – Matches the date period specified in the date input [B]
Enter and set the date in the date input [B]. If you do not enter it, it is not applied as a filter condition.
To specify a specific date value (custom date) - Click the calendar icon and select a date from the displayed calendar.
To specify a relative date value (this month, tomorrow, last N months etc.) - Click the "Relative Date" button in the date picker. A pulldown to select the relative date value is displayed, so select the appropriate relative date value. If it shows an additional number input like 'last N months', specify the number as well.
(If you selected other than "In" in step 1.) Select the date comparison method from the pull-down menu [C].
Ends with – Let the upper limit of the filter condition be the date period specified in the date input [D]. The date period of [D] is included in the filter condition.
Earlier than – Let the upper limit of the filter condition be the date period specified in the date input [D]. However, the date period of [D] is not included in the condition.
Enter and set the date in the date input [D]. If you do not enter it, it is not applied as a filter condition.
To specify a specific date value (custom date) - Click the calendar icon and select a date from the displayed calendar.
To specify a relative date value (this month, tomorrow, last N months etc.) - Click the "Relative Date" button in the date picker. A pulldown to select the relative date value is displayed, so select the appropriate relative date value. If it shows an additional number input like 'last N months', specify the number as well.
Click the "Apply" button displayed at the bottom right of the dialog.
In the date filter for formula columns, a relative date period that depends on the fiscal period (e.g. Last Fiscal Quarter, This Fiscal Year) cannot currently be specified.
Time
If the corresponding Salesforce field is a time field, the following filter screen will be displayed.
Select the date comparison method from the pull-down menu [A].
Starts from – Let the lower limit of the filter condition be the time specified in the time input [B]. The time value of [B] is included in the filter condition.
Later than – Let the lower limit of the filter condition be the time specified in the time input [B]. However, the time value of [B] is not included in the condition.
Enter and set the time in the time input [B]. If you do not enter it, it is not applied as a filter condition.
Select the date comparison method from the pull-down menu [C].
Ends with – Let the upper limit of the filter condition be the time specified in the time input [D]. The time value of [D] is included in the filter condition.
Earlier than – Let the upper limit of the filter condition be the time specified in the time input [D]. However, the time value of [D] is not included in the condition.
Enter and set the time in the time input [D]. If you do not enter it, it is not applied as a filter condition.
Click the "Apply" button displayed at the bottom right of the dialog.
Advanced Filter
Apart from the individual filter by data type, it is also possible to select "Advanced Filter" when filtering.
By selecting the "Use Advanced Filter" menu from the menu in the upper right of the filter dialog, you will see a screen for setting advanced filter conditions.
From pulldown menu [A], select the composition method when multiple filter conditions are specified
Match ALL of conditions – Include in filter result only when all filter conditions are satisfied
Match ANY of conditions – Include in filter result when any one of the filter conditions is satisfied
From pulldown menu [B] select the comparison operator for the field value
Equals to (=) – Exactly matches the specified value in the filter
Not equals to (!=) – Does not exactly match the value specified in the filter
Greater than/Equals to (>=) – Set the value as the lower limit, which includes the limit value itself (Only selectable when the field data type is Number, Date, Datetime, Time, or ID)
Greater than (>) – Set the value as the lower limit, which does not include the limit value itself (Only selectable when the field data type is Number, Date, Datetime, Time, or ID)
Less than/Equals to(<=) – Set the value as the upper limit, which includes the limit value itself (Only selectable when the field data type is Number, Date, Datetime, Time, or ID)
Less than (<) – Set the value as the upper limit, which does not include the limit value itself (Only selectable when the field data type is Number, Date, Datetime, Time, or ID)
Includes – Includes part of the character string specified in the filter (When the field data type is Text or Picklist), or contains the value in the selection (When the field data type is Multi Picklist)
Not includes – Does not include any character string specified in the filter (When the field data type is Text or Picklist), or does not contain the value in the selection (When the field data type is Multi Picklist)
Starts with – Starts with the character string specified in the filter (Only selectable when the field data type is Text or Picklist)
Ends with – Ends with the specified character string in the filter
(Only selectable when the field data type is Text or Picklist)
Is empty – The field has no value or has empty string
Is not empty – The field has some value other than empty string
Input a condition value in the text input [C]
If you have filter conditions to be set, add input row by clicking "Add Condition" [E] link.
Click "Apply" button in the right bottom of the dialog
When the filter comparison method is "Includes" or "Not Includes", you can select the filter match determination method (match mode) when multiple values are set as condition comparison values from the menu [F] next to the text input.
Specify Date/Datetime Value in Advanced Filter
To specify a date value as a filter condition in advanced filter, use the format YYYY-MM-DD. For example, for "January 4, 2017", specify 2017-01-04. To specify a datetime value, use the format YYYY-MM-DDThh:mm:ssZ. For example, for "April 8, 2017 at 9:30 am in Japan standard time", specify 2017-04-08T09:30:00+0900.
If you want to specify a relative date, you can use date literal notation defined by Salesforce. The following are available:
YESTERDAY - Starts 00:00:00 the day before and continues for 24 hours.
TODAY - Starts 00:00:00 of the current day and continues for 24 hours.
TOMORROW - Starts 00:00:00 after the current day and continues for 24 hours.
LAST_WEEK - Starts 00:00:00 on the first day of the week before the most recent first day of the week and continues for seven full days. Your locale determines the first day of the week.
THIS_WEEK - Starts 00:00:00 on the most recent first day of the week before the current day and continues for seven full days. Your locale determines the first day of the week.
NEXT_WEEK - Starts 00:00:00 on the most recent first day of the week after the current day and continues for seven full days. Your locale determines the first day of the week.
LAST_MONTH - Starts 00:00:00 on the first day of the month before the current day and continues for all the days of that month.
THIS_MONTH - Starts 00:00:00 on the first day of the month that the current day is in and continues for all the days of that month.
NEXT_MONTH - Starts 00:00:00 on the first day of the month after the month that the current day is in and continues for all the days of that month.
LAST_90_DAYS - Starts with the current day and continues for the past 90 days. This includes the current day, not just previous days. So it includes 91 days in total.
NEXT_90_DAYS - Starts 00:00:00 of the next day and continues for the next 90 days.
LAST_N_DAYS:n - For the number n provided, starts with the current day and continues for the past n days. This includes the current day, not just previous days. For example, LAST_N_DAYS:1 includes yesterday and today.
NEXT_N_DAYS:n - For the number n provided, starts 00:00:00 of the next day and continues for the next n days. This does not include the current day. For example, NEXT_N_DAYS:1 is equivalent to TOMORROW.
N_DAYS_AGO:n - For the number n provided, starts 00:00:00 of the day n days ago and continues for 24 hours after that.
NEXT_N_WEEKS:n - For the number n provided, starts 00:00:00 of the first day of the next week and continues for the next n weeks.
LAST_N_WEEKS:n - For the number n provided, starts 00:00:00 of the last day of the previous week and continues for the past n weeks.
N_WEEKS_AGO:n - For the number n provided, starts 00:00:00 of the first day of the week n weeks ago and continues for 7 days.
NEXT_N_MONTHS:n - For the number n provided, starts 00:00:00 of the first day of the next month and continues for the next n months.
LAST_N_MONTHS:n - For the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n months.
N_MONTHS_AGO:n - For the number n provided, starts 00:00:00 of the first day of the month n months ago and continues for all days of that month.
THIS_QUARTER - Starts 00:00:00 of the current quarter and continues to the end of the current quarter.
LAST_QUARTER - Starts 00:00:00 of the previous quarter and continues to the end of that quarter.
NEXT_QUARTER - Starts 00:00:00 of the next quarter and continues to the end of that quarter.
NEXT_N_QUARTERS:n - Starts 00:00:00 of the next quarter and continues to the end of the nth quarter.
LAST_N_QUARTERS:n - Starts 00:00:00 of the previous quarter and continues to the end of the previous nth quarter.
N_QUARTERS_AGO:n - Starts 00:00:00 of the first day of the quarter n quarters ago and continues to the end of the last day of that quarter.
THIS_YEAR - Starts 00:00:00 on January 1 of the current year and continues through the end of December 31 of the current year.
LAST_YEAR - Starts 00:00:00 on January 1 of the previous year and continues through the end of December 31 of that year.
NEXT_YEAR - Starts 00:00:00 on January 1 of the following year and continues through the end of December 31 of that year.
NEXT_N_YEARS:n - Starts 00:00:00 on January 1 of the following year and continues through the end of December 31 of the nth year.
LAST_N_YEARS:n - Starts 00:00:00 on January 1 of the previous year and continues through the end of December 31 of the previous nth year.
N_YEARS_AGO:n - Starts 00:00:00 on January 1 of the year n years ago and continues through the end of December 31 of that year.
THIS_FISCAL_QUARTER - Starts 00:00:00 on the first day of the current fiscal quarter and continues through the end of the last day of the fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.
LAST_FISCAL_QUARTER - Starts 00:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.
NEXT_FISCAL_QUARTER - Starts 00:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.
NEXT_N_FISCAL_QUARTERS:n - Starts 00:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of the nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.
LAST_N_FISCAL_QUARTERS:n - Starts 00:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of the previous nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.
N_FISCAL_QUARTERS_AGO:n - Starts 00:00:00 on the first day of the fiscal quarter n quarters ago and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.
THIS_FISCAL_YEAR - Starts 00:00:00 on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.
LAST_FISCAL_YEAR - Starts 00:00:00 on the first day of the last fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.
NEXT_FISCAL_YEAR - Starts 00:00:00 on the first day of the next fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.
NEXT_N_FISCAL_YEARS:n - Starts 00:00:00 on the first day of the next fiscal year and continues through the end of the last day of the nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.
LAST_N_FISCAL_YEARS:n - Starts 00:00:00 on the first day of the last fiscal year and continues through the end of the last day of the previous nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.
N_FISCAL_YEARS_AGO:n - Starts 00:00:00 on the first day of the fiscal year n years ago and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.
In the advanced filter for formula columns, a relative date period that depends on the fiscal period (e.g. LAST_FISCAL_YEAR, THIS_FISCAL_QUARTER) cannot currently be specified.
Using Reference Value in Filter
Filters for text-type and ID-type fields, or advanced filters, have a "Reference Value Selection button" located adjacent to the text input of the value to be specified in the filter. This allows you to reference information that is specific to the runtime context, such as environment variables, or values displayed on other sheets as a condition value for the filter. For details, please refer to "Reference Value".
Disable Filter
When a filter is applied, a filter icon is displayed at the right end of the display label of the column header. When you click on the filter icon, it becomes an outline display and the filter is temporarily disabled. Click the icon again to enable the filter again.
Reset Filter
To reset the filter applied to a column, follow these steps:
Click the header menu of the column you want to reset filter and select "Filter" from the menu.
A dialog for filter setting is displayed.
Click the "Reset Filter" button at the bottom of the dialog.
Group Filters
By grouping filters applied to different columns, it is possible to extract records under conditions that satisfy one of the filters across multiple columns. For details, please refer to "Change Sheet Setting" > "Filter Group".
Filtering by Record Color
When colors (text color/background color) are set on records through Changing Record Colors or Sheet Format Settings, you can apply filters based on the configured colors.
To enable filtering by record color, you need to check "Enable Filtering by Record Color" in the Sheet Option Settings.
When "Enable Filtering by Record Color" is checked, a Color Filter Menu [A] is displayed in the sheet's top toolbar.
When record color filtering is not configured, select the target color type (text color or background color) to apply the filter from the menu, and select the target color to filter from the color palette.
When no color (🚫) is set as the filter target color, only records without any color settings will be filtered and displayed in the sheet.
When record color filtering is configured, an icon representing the applicable color type (text color or background color) and the color set as the filter target are displayed as a preview in the menu button. You can change the filter target from the menu. You can also remove the filter application by selecting "Clear Color Filter" from the menu.
The colors targeted for filtering are only colors set individually on records or colors set in the sheet's format settings. Colors applied to cells (those set in column format settings) are not included as filtering targets.
Target Records for Filtering
When all columns with filters are field columns referencing Salesforce fields (including Salesforce formula fields) that support filtering on the Salesforce platform, the filter will be applied to the entire records stored in the Salesforce object.
On the other hand, filtering is applied only to the records retrieved from Salesforce in the following cases:
When filters are applied to field columns that do not support filtering on the Salesforce platform (e.g., Long Text Area field)
When filters are applied to formula columns added within the sheet
When record color filtering is configured and the target color matches any color set in any condition of the sheet's format settings
When these conditions apply and the sheet is set to fetch a small number of records, the data displayed in the sheet after the application of the filter may be less than the actual matching data (a warning will be displayed in the footer of the sheet).
To increase the number of records to fetch, change the "Max Fetch Record Num" setting from sheet setting.
Last updated