USM Content
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 sheet summary report
USM Content
Prerequisites
To create a portfolio view, you need:
- Your source sheets. The information in each sheet summary is the information you can pull into the report.
- A sheet summary report.
Create your sheet summary report
- On the left Menu bar, select Create (plus icon).
Scroll down to the bottom of the page and select Report > Sheet Summary Report.
Brandfolder Image
Your new report opens, and you can start editing it.
Define what you want to see in the report
On the report toolbar, you can use the tools described in the table below to define what and how you want to see it in the report.
| Use this tab | To do this |
|---|---|
| Source Sheets | Specify which sheets and summaries are pulled into the report. If you select a workspace, you pull all the sheets in that workspace, and the report updates when sheets are added and removed from the workspace. If you select a folder, you pull only the sheets inside the folder, and the report doesn’t update automatically 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. |
Your first step is to select your source sheets. Then, choose what columns you want to display.
After that, you can use the Filter Criteria, Group, Summarize, and Sort tools to organize the information how you want to see it.
Edit custom sheet summary fields from the report
You can edit the fields in your portfolio view report right in the report; you don't need to go back to the summary field in the underlying sheet to make changes.
- Select the Kebab
menu in the left side cell of the row you want to modify. - Select Edit. The Edit form opens if the source sheet contains custom fields. Otherwise, you see the message: This sheet has no custom summary fields defined.
- Enter your changes and select Save.
Keep the following in mind
- Ensure 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 that all columns in all included sheets are formatted as currency. If you use different field types, such as currency and numeric, the system creates a column for each field type.
The Sheet Summary Report isn't available for Premium Apps. For example, you can't use a Sheet Summary type of Report as a source for your Dynamic View.
Calculate key project metrics with sheet summary formulas
USM Content
Sheet summary formulas let you pull live calculations, like total budget, task counts, or project health, directly into your sheet summary, so key metrics are always visible without digging through rows.
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.
Sheet summary formula examples
The table below has some sheet summary formula examples.
| When you reference | Use this syntax | Example formula |
|---|---|---|
| Other summary fields (same sheet) | [Field name]# | =SUM([Budget 2024]2, [Budget 2025]2) |
| Cells in the sheet | Column name + row number | =SUM(Expenses1:Expenses3) |
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.
For a complete list of functions, see functions.
Cross-referencing other sheet data in formulas
In sheet summary field formulas, you can cross-reference data from other sheets.
Learn how at Create cross-sheet references.
Provide context to symbols with a sheet summary legend
USM Content
Symbols in Smartsheet offer a quick way to visualize information, but their meaning can vary based on the context of your sheet.
Adding a sheet summary legend provides a reference for each symbol, making it easier to understand data correctly and stay aligned.
Create a sheet summary legend
- Open the sheet summary by going to the Summary icon
in the right feature bar of your sheet or in the toolbar. - To create a legend summary, add a new field by selecting the New Field button at the bottom or the Add button (table view)at the top of the sheet summary.
- Select Text/Number as the type and name your field. The new field appears on the sheet summary.
- In the text field, add a summary of your symbol legend.
- Now, add a new field, but select the Symbols type. In table view, they are named Status, Trends, Progress, and Ratings.
- Select OK or Apply. The symbol field appears in the Sheet Summary.
In the symbol field you just created, select the symbol to display.
Lock these fields to prevent Editors from changing them. To lock a field, select the menu icon to the right of the field and select Lock Field.
- Repeat steps 4 and 5 for each symbol you want to describe.
-
Brandfolder Image
