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.

Information can be quickly combined into groups, and summarized using functions to calculate totals on report columns.

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 of your assigned work items across all projects, overall budget concerns, high priority items, and more.
  • Communicate portfolio summary and progress metrics in executive dashboards.
  • Highlight portfolio level stats using charts and graphs.

Before you begin

  • Make sure your summary fields are consistently named across all included sheets. Even minor differences in character spacing and capitalization can exclude summary information from your report.
  • To prevent duplicate columns in the report, make sure you’re using the same field type across all summary fields used to collect the same kind of data. For example, if you’re aggregating budget information, make sure all columns included have budget formatted as currency. If you have some columns as currency and some as numeric values, you’ll get a column for each field type. 


Sheet Summary Report is not available for Premium Apps.

Creating a Portfolio View

To create a portfolio view, first create a sheet summary report and then define what’s included. 

Create your sheet summary report

  1. On the left Navigation Bar, click Solution Center (plus icon) and then select Report
  2. Type a name for your report, select Sheet Summary Report, and then click OK.

Define what is pulled into the report 

Once you create 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 their sheet summaries will be pulled into this report.

TIP: If you select a workspace, all sheets in that workspace will be in scope. The report will dynamically update the scope of the report as sheets are added and removed from the workspace. Keep in mind that selecting a folder will only add the sheets currently in the folder; the scope for folders is not dynamic.

Columns to Display

Select the summary fields you would like to show in this report.

Filter Criteria

Define parameters for summary information displayed in the report.

Group
Combine similar values into groups so rows can be organized 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’ve 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

  1. Click the More icon on the left side cell of the row you want to modify, and select Edit. Alternatively, you can right click the Sheet Name cell > Row Actions... > Edit
  2. 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.

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.

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

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

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

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.