Sheet Summary Reports

 

Sheet summary reports allow you and your team to easily aggregate the summary fields you need across multiple sheets, so you can quickly see critical information and data in a portfolio level view.

A powerful addition to any teams’ reporting needs, these reports aggregate sheet summary fields from one or more sheets into a single report to surface key metrics based on report criteria. 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 a Sheet Summary Report

Aggregate your summary data across multiple sheets using sheet summary reports to create a portfolio-level view. Create a sheet summary report to:

  • Report on key summary fields across multiple sheets to get a high-level overview
  • Communicate portfolio summary and progress metrics in executive dashboards
  • Highlight portfolio level stats using charts and graphs on  

Who can use this capability

permissions requirements Anyone with a Smartsheet license who has access to sheets with sheet summaries can create a portfolio report. Anyone shared to the report and the underlying sheets can see the information in the report.
Plan type requirements Business, Enterprise, and Premier plans.

Before you begin

  • Make sure that your summary fields are consistently named across all of your sheets. Even minor differences in character spacing and capitalization can cause summary information to be excluded from a report.
  • To prevent the appearance of duplicate columns in the report, make sure that you’re using the same field type across all summary fields used to collect the same kind of data.

Create a portfolio view

To create a portfolio view, you’ll need to create a sheet summary report and define what information is pulled into it.

Create your sheet summary report

  1. Select the plus icon (on the left panel) > Create > Report.
  2. Type a name for your report.
  3. Choose the Sheet Summary Report option.
  4. Click OK.

Define what is pulled into the report 

  1. Open the report builder by clicking the report builder icon report builder icon .
  2. Click Source Sheets and specify which sheets and their sheet summaries will be pulled into this report.
  3. Click Columns to Display and select the summary fields you would like to show in this report.
  4. Click Filter Criteria to define parameters for summary information displayed in the report.

    report builder filter criteria image
     
  5. Click Sorting to define how summary information is sorted in the report.
  6. Click Save to confirm your selections, and summary data that meets defined criteria will be displayed in the report.

Calculate Key Project Metrics With Sheet Summary Formulas

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

Who can use this capability

permission requirements icon 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.
Plan types icon Business, Enterprise, and Premier plans.

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.
  • You can find our complete functions list here.

Sheet summary formula references

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

When you reference

Use this syntax

Example 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})

Provide Context to Symbols With a Sheet Summary Legend

Build a legend in the sheet summary to describe the meaning of each icon or symbol on your sheet.

Who can use this capability

permissions requirements icon 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.
Plan availability icon Business, Enterprise, and Premier plans.

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, click the Menu icon (right side of the field) and select Lock field.