Create an aggregate worksheet

Use aggregate worksheets to combine data from an existing worksheet. Aggregate worksheets allow you to use unique values from selected source columns to group data.

To further combine source data, you can create calculated and related columns in aggregate worksheets. In these columns, you can perform calculations on the created data groups, create relationships between worksheets, and configure custom actions.

For example, if you sell products to multiple customers for different prices, you can use an aggregate worksheet to easily calculate the average margin of each product. For setup details, refer to this usage example.

Watch to learn how to use aggregate worksheets.

Limitations

Some common worksheet features aren't available to aggregate worksheets. You cannot:

• Add or edit input columns
• Add, edit, or copy rows
• Import data
• Add row-level action buttons
• Link analytics
• Configure Source Data settings

Configure an aggregate worksheet

1. From the Manager Dashboard, open a workbook.

2. In the worksheet tabs area, select Add.

3. Enter a name for the new worksheet, then select Next.

4. Select Aggregate, then select Next.

5. Select the source workbook and source worksheet that contain the data you want to combine, then select Next.

6. Select at least one column from the source worksheet by whose values you want to group the source data in the resulting aggregate worksheet, then select Next. The aggregate worksheet creates rows for all unique values from the selected columns.

   Note

   You cannot change column selections after creating the aggregate worksheet.

7. To create a calculated column, enter information or make selections, then select Finish:

   • Column Name—Enter a name for the calculated column.
   • Function—Select an aggregate function to perform calculations.
   • Column—Select a column from the source worksheet whose values you want to aggregate in the calculated column.
   • Value type—Select the value type for the calculated column.
   • Display format—Select the display format of values in the calculated column.
   • (Optional.) Select Add a calculated column to create another column.

Alternatively, select Skip and finish to continue without creating a calculated column.

Example

You sell your products to various customers and use a worksheet to organize this data. The worksheet features columns for:

• Product ID
• Customer ID
• Price
• Margin
• Publish Date

To prepare for annual reporting, you want to find the average margin for each product and customer. To do this, you create an aggregate worksheet.

While creating the aggregate worksheet:

1. Group your source worksheet data by unique products and customers.
   • At Step 6, select the Product ID and Customer ID columns.
2. Calculate the average margin for each product.
   • At Step 7, create the Average margin calculated column:
     • From the Function dropdown list, select the AVG function.
     • From the Column dropdown list, select the Margin source column.

The resulting aggregate worksheet displays three columns:

• Product ID—Lists all unique products from the source worksheet.
• Customer ID—Shows customers associated with the listed products.
• Average margin—Calculates the average margin for each product.