Skip to main content
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday company.

Filter Sheets

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

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, click Display Options displayOptions.png.
  2. Click the Time tab.
  3. Select the Start and End of the time range you want to view.
  4. Select the columns 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

Available for 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

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, click Display Options displayOptions.png.
  2. Click 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. Click 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. Click 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. Click Apply.

When filters are active in a modeled sheet, the Display Option button turns orange: FiltersOn.png.

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

Filter with Keywords 

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.

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, click 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 Product Group. Each product dimension is tagged with the appropriate attribute. The products are organized like this:

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

Filter Other Related Filters 

In the filters DimensionIcon.png indicates a dimension, and AttributeIcon.png indicates an attribute. Often the attributes are related to custom dimension, as in the scenario described. When this is true, use attributes to filter custom dimensions.

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

Using attributes to filter dimensions

 

Use Dimensions to Filter Attributes in Rows 

  1. From the toolbar, click Change Dimensions ChangeDimensions.png
  2. For Vertical, choose the attribute, Groups
  3. From the Other Dimension Coordinates section, choose T-shirts from the Product drop-down filter. 
  4. Click OK. Only Tops remains in the rows. 
  5. From the Product drop-down filter, choose Jeans. Now, only Bottoms remains in the rows. 

Dimension filter attributes in rows.png

Use Attributes to Filter Dimensions in Rows 

  1. Click Change Dimensions ChangeDimensions.png
  2. For Vertical, choose the dimension, Product, and click OK. The rows include all the products.
  3. From the Group drop-down filter, choose Tops. The rows display T-shirts, Tank Tops, Sweaters, and Blouses.
  4. From the Group drop-down, choose 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, click Display Options displayOptions.png.
  2. Click through the tabs and select and clear any number of values.
  3. Click Apply.

Display Options Tabs for filters.png

  • Was this article helpful?