Skip to content

Pivot Activity

The Pivot activity enables users to perform pivot operations on data within an Excel file. It facilitates the transformation of data by aggregating and summarizing information based on specified columns, rows, and values.

Field Description Requirement
Excel name The reference name of the Excel file that has already been opened or created. Required
Source sheet The name of the Excel sheet containing the data for the pivot operation. Optional
Range Specifies the data range within the source sheet to be pivoted. Optional
Sheet name The name of the sheet where the pivot operation's result will be displayed. This can be a blank or newly created Excel sheet. Optional
Sheet index The index of the sheet to work with in the Excel file. If not specified, the first sheet is used by default. Optional
Destination Determines the starting cell in the destination sheet where the pivoted data will be written. If no destination is provided, the data is copied to cell A1. Optional
Columns Defines the columns to be included in the pivot operation. Optional
Rows Defines the rows to be included in the pivot operation. Optional
Values Specifies the desired aggregation actions to be performed on the data. Optional

Action Types & Examples

Aggregation Function

  • Format: string (representing a function call, e.g., SUM(ColumnReference))
  • Example Result: 123.45

Implementation Examples

Field Setup

  • Excel name: ${pivotExcel}
  • Source sheet: Sheet1
  • Range: A1:B3, A:A, A:H
  • Sheet name: Pivot
  • Sheet index: 5
  • Destination: A1
  • Columns: A, A,B
  • Rows: B
  • Values: SUM(C), COUNT(C),AVERAGE(C)

Technical Notes

  • If the first page of an Excel file is to be pivoted, the Source sheet field does not need to be filled. Otherwise, it must be configured.
  • If no Destination is specified, the pivoted data is copied to cell A1.
  • If Range is not provided, the entire range of data in the Excel file is considered for the pivot operation.
  • If a sheet contains multiple tables or disparate data, the Range field should be explicitly defined.
  • Errors may occur during pivot operations if the Excel file contains different columns with identical names. In such cases, column names should be modified to be unique.
  • Supported aggregation functions for the Values parameter include: AVERAGE, COUNT, COUNT_NUMS, MAX, MIN, PRODUCT, STD_DEV, STD_DEVP, SUM, VAR, VARP.