13. Sheet summary

With the sheet summary,Track key informationOr expand the project,Reduce time to report on important metricsBecause you can, you can make better decisions faster and make better decisions.

Mission criticalWhen it is difficult to manage information directly in the sheetBy leveraging sheet summaries, you can organize and report such information in a standard and error-free manner.In the right panel, you can also customize the sheet summary fields and include advanced formulas that integrate strings, symbols, images, links, and sheet data.

How to use sheet summaries

Sheet summary commentary video

Used as a place to put information other than tabular data on the sheet

Smartsheet sheets have a tabular structure and record records defined by column type for each row.
Elements other than the data in this tabular structure, such as specific rows, non-task information, and information that describes the entire project, can be recorded elsewhere rather than in the rows to keep the sheet tabular. The structure can be maintained.

• For example, belowMicrosoft siteIt is a template of inventory management (equipment) in, but in the upper right, there is information that is not related to the table structure data such as inventory execution date and inventory execution person.
• Specifically, the "Equipment inventory list" in the title of the table is in the "ID" column, and the information of "Inventory date" and "Inventory performer" is between the "Inventory number" and "Total amount" columns. It is located in.
• This usage is common in "Japanese Excel culture that emphasizes appearance", but in Smartsheet, the column type for each column is properly specified such as letters / numbers, contacts, dates, drop-down lists, etc., so it is organized. Other than the data in the tabular structure that has been created, you can clean it up by putting it in a sheet summary.

By putting the information of "inventory execution date" and "inventory performer" in the above Excel in the sheet summary, it became a neat structure consisting only of the tabular data of the sheet.

Used as a place to put basic information on sheets such as project management

For example, in the case of the project management sheet below, you can put information related to the entire sheet such as the project name, project description, project manager, etc. in the sheet summary.
I'll explain it in another tutorial, but this information isSheet summary reportYou can also use it in.

Used as a place to put constants used for the entire sheet

The example below is an example of the annual paid leave accounting book, but the fixed number of regular working hours (one day) required when converting the hourly leave required for vacation management to full leave is placed in the sheet summary.

• This constant of XNUMX (hours) is[Scheduled working hours (XNUMX day)]# It can be quoted in the form of = hourly holiday @row / [prescribed working hours (XNUMX day)] # and is used in the column of hourly wage full holiday conversion.

Regarding constants, it is best practice to use them universally on multiple sheets, for example, quoting things like consumption tax rates with cell links, but by putting constants used only on a specific sheet in the sheet summary, it is a sheet. Is refreshing.

Also, if the constant changes, you only need to change the sheet summary, and you can use the drop-down list to change the constant to simulate multiple scenarios. (For example, when calculating the percentage of hourly wages for XNUMX hour, XNUMX hours, and XNUMX hours, respectively.)

Use as a place to put information that aggregates sheets

In the first example, the project summary data is calculated by a function and placed in a sheet summary.
Dashboards are a common place to provide project summary information, but for a single project, sheet summaries allow you to see key summary information in the sheet.

• Number of high-risk tasks　　
  • = COUNTIF (risk: risk, “high risk”)　
• High-risk task (list)
  • = JOIN (COLLECT (main task: main task, risk: risk, “high risk”), CHAR (10))
• Number of completed tasks
  • = COUNTIF (Status: Status, “Done”)
• Completed tasks (list of)
  • = JOIN (COLLECT (main task: main task, status: status, “completed”), CHAR (10))

In the second example, the aggregated data for a particular department related to vacation management is calculated with a function and placed in a sheet summary.

• Total number of days taken for full holidays and half holidays (affiliation 1)
  • = SUMIF ([Affiliation 1]: [Affiliation 1], [Affiliation 1] #, Full holiday: Full holiday) + SUMIF ([Affiliation 1]: [Affiliation 1], [Affiliation 1] #, Half holiday: Half holiday)
• Average number of days taken for full holidays and half holidays (affiliation 1)
  • = [Total number of full holidays and half holidays (affiliation 1)] # / COUNTIF ({affiliation-1}, [affiliation 1] #)
• Total number of hours off (affiliation 1)
  • = COUNTIFS (hourly holiday: hourly holiday,> 0, [affiliation 1]: [affiliation 1], [affiliation 1] #)
• Number of 1-hour breaks taken (affiliation 1)
  • = COUNTIFS (Time holiday: Time holiday, = 1, [Affiliation 1]: [Affiliation 1], [Affiliation 1] #)
• 1 hour time off acquisition rate (affiliation 1)
  • = [Number of 1-hour breaks taken (affiliation 1)] # / [Total number of hours off taken (affiliation 1)] #

Used as a place to put tag information for searching and classifying sheets

The example below isSmartsheet experience cornerThis is an example of using the sheet for project management experience as a place to put tag information for searching and classifying sheets using the sheet summary.

On the search screen as shown below, as a typeSummary fieldSelect and enter the value of project-management-samples here to search for the sheet used for the project management experience.

Define work using sheet summaries

Sheet owners and managersAllows you to add new sheet summary fields, edit field properties, and enter existing locked / unlocked fields. Sheet owners, admins, editorsAllows you to enter an existing unlocked feel.You need a license to add new sheet summary fields, edit field properties, and enter existing locked / unlocked fields.

Any user with whom a seat is shared can view the seat summary.

Sheet summaries provide a standard place to define, organize, and report custom project, business, and summary level information.Check the contents of the sheet summary in the right panel next to the sheet data.

---

Access the sheet summary

Summary icon on the right panel  Click to expand the Sheet Summary panel.

The summary field has space for you to enter information for different summary levels.

Create a new sheet summary field

With the sheet summary open, do the following:

1. At the bottom of the sheet summary [+ New field] Click the button.
2. Select the appropriate field for the information you want to display.Use the summary field type table below to select the required fields.
3. Enter the field name,[OK] Choose.A new blank field appears in the sheet summary.
4. Make other necessary changes (add new fields, edit properties, add data to fields) and make other necessary changes to the toolbar at the top of the sheet. [keep] Click the button.

• To save your changes to the sheet summaryPlease save the sheet. Detail is,"Save and update sheetsPlease see the article about.

The sheet summary is now ready.You can change the structure, change summary field properties, format summary fields, and add data at any time from the sheet summary.

Summary field type

Field type	Description
String / number	String description or number (eg) Project summary, Total budget)
Contact list	Show users registered in available contacts (eg) Project manager, window)
Date	Important dates (for example, project start or end date)
drop-down list	Select a dropdown option from a custom list (for example) Department name, project type, Project status)
Checkbox	Checkbox that can be switched on / off
symbol	Visual icons (eg black and white circles, stars, antenna bars)

Maximum sheet summary

The sheet summary corresponds to:

• Up to 200 fields (Not counted as the target of the sheet column)
• Field name (up to 50 characters)
• Field value equal to cell size
• Drop-down option (up to 500)

Formatting or adjusting field properties, adding links or images to fields

The new summary field contains data,Can contain hyperlinks, images, etc..

The format of the new or existing summary field is next to the title of the summary field.Menu iconClick to adjust (for example, number or currency format or property lock).

Get the most out of your sheet summaries: Tips and Best Practices

Using the sheet summaryView project-wide metricsOrSearchability by tagging sheets with custom metadataCan be increased.

View key project details using formulas or strings

One of the many uses of sheet summaries is to show the state of the sheet.For example, a project plan shows the state of the project. You can achieve this by placing a formula in the Sheet Summary field and it can also be updated automatically.

With reference to the data from the grid or another summary field, you can insert a formula in the summary field and automatically calculate the key information.

You can quickly create formulas by clicking and dragging the grid to select grid cells and cell ranges.The cells referenced in the formula are highlighted during the formula creation.

When referring to ...	Use this syntax	Formula example
… Other summary fields (same sheet)	[Field name] #	= SUM ([Budget 2016] #, [Budget 2017] #)
… Cells in the grid (same sheet)	[Column name]	= SUM (Expenditure 1: Expenditure 3)
… Cells in the grid (from another sheet)	{Reference name between sheets}	= COUNT ({Warehouse B Inventory})

Sort sheets to make searching easier

You can create a sheet summary field that contains key values ​​that you can easily find.Here are some examples:

• Use the drop-down list field or the string / number field to label the department / department associated with the sheet.
• Add keywords to searchable string / number fields.Examples: construction / architecture, electricity, engineering

For more information on the search function,Search in Smartsheet.

Create a legend that explains how to use the symbols

Create a legend in the sheet summary to explain the meaning of each icon or symbol on the sheet.
To create a legend:

1. Includes legend descriptionString / numeric fieldIs added at the top of the sheet summary.
2. One for each symbol,Symbol fieldTo the sheet summary.In the name of the field, enter the purpose of the symbol.Be sure to select the group of symbols used in the sheet for each field.
3. eachSymbol fieldSet the icon that matches the description.

Bulk copy and standardize sheet summaries for multiple sheets

Owners and sheet managers of both the original and destination sheets (I also have a license) Allows you to bulk copy the structure of a sheet summary.

Maintain consistency with metadata and portfolio data by copying the structure, formulas, and other content of the sheet summary to up to 20 existing sheets at once.

Copy the sheet summary

In the sheet summary that contains the fields you want to copy, do the following:

1. Menu icon to the right of the sheet summaryClick.
2. [Copy summary to another sheet]  Choose.
3. Up to 20 copy destination sheetsSelect.You can choose to copy only the field names or include field values ​​and formulas.
   
   
    
4. [copy] Click.

• Copying a sheet summary to another sheet is a permanent process and is irreversible.If the data may be overwritten, a confirmation message will be displayed before the bulk copy process begins.

That's it.Sheet Her summary has been copied.

Calculate key project metrics using sheet summary formulas

Sheet owner and administrator (must be licensed user) Can add summary fields to new sheets, edit field properties, and fill existing fields (whether locked or not). Owner, administrator, editorCan be entered in an existing unlocked field. All users with whom the sheet is sharedCan display a sheet summary.

You can automatically calculate budget summaries and aggregate project status and project status by entering formulas in the sheet summary fields.

Creating a sheet summary formula

In the sheet summary, do the following:Equal sign in the sheet summary field (=) とThe function you want to useEnter the. (You cannot enter a formula in the "Checkbox" field)

• The following table shows an example of referencing another sheet summary field in a sheet summary formula.
• A list of functions available in Smartsheetplease use this form..

Refer to the sheet summary formula

Use this table as a guide for referencing other fields when creating sheet summary formulas.

Reference purpose	Syntax to use	Formula example
Other summary fields (same sheet)	[Field name] #	= SUM ([2016 budget] #, [2017 budget] #)
Sheet cell	[Column name]	= SUM (Expenditure 1: Expenditure 3)
Cell on another sheet	{Reference name between sheets}	= COUNT ({Warehouse B Inventory})

References to sheet summary fields (Field Name #) can be used in both sheet summary fields and sheet cells.For more information, seeBasic formula."