Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Filter Sheets

Explains how to suppress zeros and blanks and walks you through how to filter sheets by time spans, content,  and dimensions and attributes.

Contains preview content for the upcoming 2019.3 release.

Filters get you to the cells you want to see and edit.

  • Filter by time in any sheet with a time span.
  • Filter by content in modeled sheets.
  • Use dimensions and attributes in cube sheets to filter rows.
  • Suppress rows that have all zeros and blanks. In cube sheets, you can then choose dimensions that reveal specific rows one at a time. 

When to Use Filters

  • To get to editable cells, reduce the size of your sheet, and eliminate scrolling.
  • To hide rows of blank data.
  • To see different sets of data if your admin has limited the amount of rows a modeled sheet can display.

Before You Begin

How You Get There

Compass.png From the nav menu select Sheets, Assumptions, or Processes. Once you open a sheet, select a version and choose a level. You must choose a leaf level to edit cells.

See Open Sheets and Get to Editable Cells.

Filter Time Spans in All Sheets

Available for any sheet that has time spans. 

  1. From the toolbar, select Display Options displayOptions.png.
  2. Select the Time tab.
  3. Select the Start and End of the time range you want to view.
  4. Select the time periods to display for each year listed (based on the options selected for Start and End). You can make different selections for each year listed. 

Examples of Time Span Displays

The table shows different results (in the final column) for each sample set of Time Display options, based on a current date of May 2017. 

Choose for Start: Choose for End: Choose for Display Options: Sheet Results:

Current Year

Offset: 0

Current Quarter

Offset: 0

Months for all years

Start: January 2017

End: June 2017

Current Year

Offset: -1

Current Quarter

Offset: 2

Months for all years

Start: January 2016

End: December 2017

Month and select April 2016 Current Month Months for all years

Start: April 2016 

End: May 2017

Month and select April 2016 Current Month

For 2016, select quarters

For 2017, select months

Q2, Q3, and Q4 for 2016, followed by January, February, March, April and May of 2017

Filter by Content in Modeled Sheets

Your modeled sheets may have a maximum number of viewable rows set up by your admin. If this is true, you see an information note at the bottom of the sheet: 

truncated modeled sheet.png

Use filters to see different sets of data or to minimize the size of the viewable sheet.

Filter with Display Options

  1. From the toolbar, select Display Options displayOptions.png.
  2. Select the Filter tab.
  3. Select a column to filter.
    If your sheet lists employees, select Name to filter by name.
     
  4. Select the operator for the filter: begins with, ends with, contains, does not contain, and so on. 
    Select begins with.
  5. Enter the letters, words, or numbers in the empty field.
    Enter T. The sheet shows only employees with names that begin with T.
     
  6. Select the plus icon to add more rules to the filter. You can select a different column or the same column and a different operator to further filter the sheet.
    Select Nameends with and enter E. Now the sheet shows only employees with names that begin with T and end with E.
     
  7. Select Add Group to add OR rules to your criteria.
    Select Name and begins with again. Then, enter R. Now the sheet shows employees whose names begin with T and end with E as well as employees whose name begins with R. 
  8. Select Apply.

When filters are active in a modeled sheet, the Display Option button appears a darker blue. 

Add AND rules with the plus icon and OR rules with Add Group button.

Filter with Keywords 

From the toolbar, click the large filter FilterInFilterSheets.png. Enter words, portions of words, or numbers in the filter field in the toolbar and press enter.

  • Searches for matches in all the columns.
  • Only shows records that match, removing all others from view.
  • If you have active filters in the Display Options your keyword filter finds only matches within those parameters.
  • If your modeled sheet is truncated to limit viewable records and you filter for keywords, the filter searches for matches beyond the viewable sheet. For example, if your sheet shows 100 of 1,000 records, your keyword filter searches all 1,000 records for matches.

To remove the filters, select the X next to the keyword, and press enter.

Suppress Zeros and Blanks in Standard and Cube Sheets

Show or Hide Rows with Blanks and Zeros

Available for standard and cube sheets.

 To suppress or reveal all zeros and blanks: 

  1. From the toolbar, select Display Options displayOptions.png.
  2. At the bottom of the window, select or clear Suppress rows if all zeros or blanks. If zeros and blanks are suppressed, you won't see rows that have blank cells or cells that equal zero and you won't be able to enter new data in them.

Reveal a Suppressed Row

Suppress zeros and blanks and add a specific row while keeping the irrelevant rows hidden. See Add Splits and Rows to Sheets > Add Rows in Cube Sheets.

Filter by Dimension and Attributes in Cube Sheets

On cube sheets, time, accounts, any custom dimensions, and attributes are dimensions. They display as: 

  • Drop-down filters above the sheet: 

Dimension filters in a cube sheet

  • Across the top of the grid in the columns.
  • Down the left side of the sheet. Multiple dimensions and attributes can nest within the rows. To pivot cube sheets, see Change Sheet View

Use the filters to:

  • Filter other filters. 
  • Filter rows. 
  • Find editable cells. You must choose a leaf-level dimension from each filter to edit data. 

Dimension and Attribute Scenario

Your business is a store that sells clothes. "Products" is a dimension in your model. Products has a dimension attribute, called Group. Group categorizes the products. Each product dimension is tagged with the appropriate attribute. The products are organized like this:

Group Attribute Values Tops Bottoms Accessories
Product Dimension Values
  • Tank Tops
  • T-shirts
  • Blouses
  • Sweaters
  • Leggings
  • Jeans
  • Skirts
  • Shorts
  • Belts
  • Hats
  • Purses
  • Gloves

Filter Related Filters 

Often the attributes are related to custom dimension, as in the scenario described. When this is true, use the attributes to filter custom dimensions.

  1. Place accounts in the rows and time in the columns so that the attribute and its associated dimension are in the filters.
  2. Choose Tops from the Group filter, an attribute. 
  3. The available dimensions includes only those tagged with tops:  T-shirts, Tank Tops, Blouses, and Sweaters. 
  4. Choose a different attribute: Bottoms
  5. The available dimensions changes to Leggings, Jeans, Skirts, and Shorts. 

Filter filters.png 

Use Dimensions to Filter Attributes in Rows 

  1. From the toolbar, select Change Dimensions ChangeDimensions.png
  2. For Vertical, choose the attribute, Groups. Select OK.
  3. From the Products drop-down filter, select Jeans. Only Bottom remains in the rows. 
  4. From the Product drop-down filter, select T-shirts. Now, only Tops remains in the rows. 

Dimension filter attributes in rows.png

Use Attributes to Filter Dimensions in Rows 

  1. Select Change Dimensions ChangeDimensions.png
  2. For Vertical, choose the dimension, Product, and select OK. The rows include all the products.
  3. From the Group drop-down filter, choose the attribute Tops. The rows display T-shirts, Tank Tops, Sweaters, and Blouses.
  4. From the Group drop-down, choose the attribute Accessories. Now, the rows display Belts, Hats, Purses, and Gloves. 

Attributes to fitler dimensions in rows

Display Option Filters

For more filtering options:

  1. From the toolbar, select Display Options displayOptions.png.
  2. Select the tabs and select and clear any number of values. The values you clear don't appear in the sheet. 
  3. Select Apply.

Display option filters.png

  • Was this article helpful?