Learn more

Adding or editing a spreadsheet filter

Filtering spreadsheet sequences

Duplicating an existing spreadsheet filter

Renaming a spreadsheet filter

Removing a spreadsheet filter

Edit Spreadsheet Filters window

Sample spreadsheet filter expressions

Filtering items on the spreadsheet

You can create filters to limit the items displayed on the spreadsheet to items that match specified criteria. Filtering items for an estimate enables you to more easily find and analyze estimate data.

You use the buttons in the Spreadsheet Display section of the ribbon to add Create New Filter button and edit Edit Filter button filters; to select and apply a filter to the current spreadsheet sequence, and (optionally) pin Pin Filter buttonthe filter.

Note: After applying a filter, if you edit the estimate, the program does not filter items automatically as you work. This is by design. You need to select the Reapply Filter button Reapply Filter buttonto refresh the display.

You apply filters to spreadsheet sequences. When you pin a filter, it is automatically applied to other sequences and other estimates when you view them. You can create as many filters as you need and you can use different filters for different sequences in your estimate.

You can also apply a spreadsheet filter when you print a spreadsheet report.

Understanding how to construct a filter expression

The power of the filters derives from your ability to construct expressions using the item and estimate fields, operators, functions and field values exposed in the Edit Spreadsheet Filter Expressionwindow.

Constructing a spreadsheet filter involves combining the fields, operators, and functions in the Edit Spreadsheet Filter Expression window with unique field values to create a valid, logical expression.

  • Fields

    Fields represent the attributes of items in your database—for example, Description, Crew, Takeoff Quantity, or Material Price.

  • Operators

    Operators are used to perform operations on fields and values. Common operators include:

    • Arithmetic Operators: +, -, *, /

    • Comparison Operators: =, !=, <, >, <=, >=

    • Logical Operators: AND, OR, NOT

  • Functions

    Functions perform specific calculations or operations. Some common functions include:

    • String Functions: Contains(str, constainsStr), RegexMatch(str, pattern), Length(str)

    • Numeric Functions: Min(value1, value2), Round(fractionalDigits), Abs(value)

    • Date Time Functions: CurrentDateTime(), CreateDateTime(Y, M, d, h m, s)

    • WBS Functions: WbsValueByCode(wbsCodeName), WbsCodeByIndex(wbsIndex)

    • Conversion Functions: ConvertToInteger(str), ConvertToFloatingPoint(str), ConvertToDateTime(str)

  • Unique Field Values

    Unique field values are the specific values associated with a selected field. When you use one of these field values in an expression, an item will have to use this specific value to be considered a match. For example, you might specify "Lump" as the unique value for the Takeoff Unit field. When you apply the filter, the spreadsheet displays only items that have Lump as the takeoff unit.

Example: Here are some examples of filter expressions that you can construct.

Sample filter expressions
To show only items that: Add this filter expression:
Have Joist in the description (regardless of case) Contains(ToUppercase(Description), "JOIST")
Include notes that contain Exclusions: and then Sitework on a new line Contains(Notes, "Exclusions:" + NewLine() + "Sitework")
Contribute labor dollars that don't come from crews LaborAmount<>0 and Crew=''
Are "lump sum" items TakeoffUnit='Lump'
Exceed a certain dollar value TotalAmount>5000
Items that use a WBS code with the name Bid Item WbsValueByCode('Bid Item')='2'

For more examples of spreadsheet expressions, see Sample spreadsheet filter expressions.