Sheet Summary Reports

Sheet summary reports allow you to easily aggregate summary fields from one or more sheets into a single report so you can quickly see critical information in a single portfolio level view.

Sheet summary information is surfaced based on the report's criteria. It can then be quickly combined into groups and summarized using functions to calculate totals on report columns.

Sheet summary reports can be added directly to a dashboard, or visualized in a chart to communicate real-time progress to stakeholders.

Create a portfolio view with sheet summary report

APPLIES TO

  • Smartsheet
  • Pro
  • Business
  • Enterprise

Create a portfolio view

First, you need to create a sheet summary report and then define what is included. 

Create your sheet summary report

  1. On the left Menu bar, select Create (plus icon).
  2. Select Create new > Report.
  3. Type a name for your report and select Sheet Summary Report.
  4. Select OK.
  5. Open the report and select your source sheets.

Define what is displayed in the report

Use the settings on the report toolbar to adjust your Portfolio View.

Use this tab

To do this

Source Sheets

Specify which sheets and summaries will be pulled into this report.

If you select a workspace, you get all sheets in that workspace. The report will update when sheets are added and removed from the workspace. Keep in mind that selecting a folder will only add the sheets in the folder; summaries on folders do not automatically update when the contents of the folder change.

Columns to Display

Select the summary fields or system columns for this report.

Filter Criteria

Define parameters for summary information displayed in the report.

Group Combine similar values into groups to organize rows into logical categories or classifications.
Summarize Extract key information from your report, such as the Count of completed projects.

Sort

Define how summary information is sorted in the report.

Once you have configured each setting, select Save to confirm your selections. Summary data that meets defined criteria will be displayed in the report.

For more information about how each of these configuration options works, see Build a Row Report with Report Builder.

Edit custom sheet summary fields in the portfolio view report 

You can edit the formatting of fields included in your portfolio view report right in the report; you do not need to go back to the summary field in the underlying sheet to make changes.  

  1. Select Moreon the left side cell of the row you want to modify.
  2. Select Edit. The Sheet Summary form will open if the source sheet has custom fields. Otherwise, you will see the message This sheet has no custom summary fields defined.
  3. Enter your changes and click Save.

Tips to build a portfolio view: 

  • Make sure all your field names are consistent across sheets. 
  • Use the same field type across all summary fields used to collect the same data type. For example, if you are aggregating budget information, ensure all columns in all included sheets have budget formatted as currency. If you have different field types, for example, currency and numeric, you will get a column for each field type.


Sheet Summary Report is not available for Premium Apps. For example, you cannot use a Sheet Summary type of Report as a source for your Dynamic View.

Calculate key project metrics with sheet summary formulas

APPLIES TO

  • Smartsheet
  • Business
  • Enterprise

RELATED CAPABILITIES

Who can use this capability?

Owners and sheet Admins (who also have a license) can add new sheet summary fields, edit field properties, and type in existing locked or unlocked fields. Owners, Admins, and Editors can type in existing, unlocked fields. Anyone shared to the sheet can view the sheet summary.

Automatically calculate budget summaries, aggregate project status and project health, and more by placing formulas in sheet summary fields.

Create a sheet summary formula

In your sheet summary: Type the equals sign (=) and the desired function in a sheet summary field. (Note that you can’t enter formulas in checkbox fields.)

  • Use the table below for examples on referencing other sheet summary fields in your sheet summary formulas.
  • To see a complete list of functions, see functions.

Sheet summary formula references

Use this table as a guide for referencing other fields as you build sheet summary formulas.

When you referenceUse this syntaxExample formula
Other summary fields (same sheet)[Field name]#=SUM([Budget 2016]#, [Budget 2017]#)
Cells in the sheet[Column name]<row number>=SUM(Expenses1:Expenses3)
Cells from another sheet{cross-sheet reference name}=COUNT({Warehouse B Inventory})

 

Sheet summary field references ([Field Name]#) for formulas can be used both within Sheet Summary Fields and Sheet Cells. For more information, see Formula basics.

 

Provide context to symbols with a sheet summary legend

APPLIES TO

  • Smartsheet
  • Business
  • Enterprise

RELATED CAPABILITIES

Who can use this capability?

Owners and sheet Admins (who also have a license) can add new sheet summary fields, edit field properties, and type in existing locked or unlocked fields. Owners, Admins, and Editors can type in existing, unlocked fields. Anyone shared to the sheet can view the sheet summary.

Create a sheet summary legend

In your sheet summary:

  1. Add a Text/Number field at the top of your sheet summary with a description of the legend.
  2. Add Symbol fields to the sheet summary, one for each symbol. In the name of the field, type the purpose of the symbol. Make sure to select the group of symbols being used on the sheet for each individual field.
  3. In each Symbol field, set the icon that matches your description.

You may also want to lock these fields to prevent Editors from making changes to them. To lock a field, select the Menu icon on the right side of the field and select Lock field.