Pivot
The Pivot() function is used to arrange and aggregate a range of information into a more readable format. This usually involves creating new columns using information from the rows. Pivot() is an aggregate-type function.
Related functions: Depivot, GroupBy
Syntax
Pivot (Range, Row Grouping, Column Grouping, Formula, Calculate Error Value)
| Range | The range of cells in Excel used as input |
| Row Grouping | The columns of the range on which row aggregation will occur. This will then create a new row group for each unique set in the selected column(s). |
| Column Grouping | The columns of the range on which column aggregation will occur. This will then create a new column group for each unique set in the selected column(s). |
| Formula | The formula that will be used to aggregate the values |
| Calculate Error Value | The value to be displayed when an error occurs in the formula |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Sum data using Sector to group the rows and Year to group the columns
Example 3. Determine averages using Sector to group the rows and Year to group the columns
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Sum data using Sector to group the rows and Year to group the columns
The first column of the input range (Sector) is used as the row grouping, and the second column (Year) is used as the column grouping. The final column is summed by Sector and Year.
| Range | Pivot information and sum per sector, per year | |||
| Sector | Year | Number | =Pivot(A3:C13,”C1″,”C2″,”SUM(C3)”) | |
| Financial Services | 2013 | 400 | ||
| Basic Resources | 2013 | 250 | ||
| Healthcare | 2013 | 25 | ||
| Basic Resources | 2014 | 300 | ||
| Oil & Gas | 2013 | 350 | ||
| Basic Resources | 2014 | 35 | ||
| Insurance | 2014 | 800 | ||
| Financial Services | 2013 | 450 | ||
| Insurance | 2013 | 75 | ||
| Telecommunications | 2013 | 120 | ||
| Oil & Gas | 2013 | 450 | ||
| Range | Output | |||||
| Sector | Year | Number | 2013 | 2014 | ||
| Financial Services | 2013 | 400 | Basic Resources | 250 | 335 | |
| Basic Resources | 2013 | 250 | Financial Services | 850 | ||
| Healthcare | 2013 | 25 | Healthcare | 25 | ||
| Basic Resources | 2014 | 300 | Insurance | 75 | 800 | |
| Oil & Gas | 2013 | 350 | Oil & Gas | 800 | ||
| Basic Resources | 2014 | 35 | Telecommunications | 120 | ||
| Insurance | 2014 | 800 | ||||
| Financial Services | 2013 | 450 | ||||
| Insurance | 2013 | 75 | ||||
| Telecommunications | 2013 | 120 | ||||
| Oil Gas | 2013 | 450 | ||||
Example 2. Multiple columns – Sum data using Asset and Sector to group the rows, and Year and Month to group the columns
The first two columns of the input range (Asset and Sector) are used as the row grouping and the next two columns (Year and Month) are used as the column grouping . The final column (Number) is summed.
| Range | Sum per code, sector, year and month | |||||
| Asset | Sector | Year | Month | Number | =Pivot(A3:E13,”C1,C2″,”C3,C4″,”SUM(C5)”) | |
| JTD | Oil & Gas | 2000 | March | 620 | ||
| GPS | Industrials | 2000 | June | 250 | ||
| APN | Health Care | 2005 | June | 25 | ||
| CFH | Industrials | 2005 | March | 300 | ||
| PHO | Consumer Goods | 2000 | June | 800 | ||
| BVT | Industrials | 2005 | March | 35 | ||
| ZIK | Health Care | 2005 | March | 800 | ||
| JTD | Oil & Gas | 2000 | June | 450 | ||
| VRR | Consumer Goods | 2000 | June | 75 | ||
| PHO | Consumer Goods | 2000 | June | 480 | ||
| THT | Consumer Goods | 2000 | March | 250 | ||
| Range | Output | ||||||||||
| Asset | Sector | Year | Month | Number | 2000 | 2000 | 2005 | 2005 | |||
| JTD | Oil & Gas | 2000 | March | 620 | June | March | June | March | |||
| GPS | Industrials | 2000 | June | 250 | APN | Health Care | 25 | ||||
| APN | Health Care | 2005 | June | 25 | BVT | Industrials | 35 | ||||
| CFH | Industrials | 2005 | March | 300 | CFH | Industrials | 300 | ||||
| PHO | Consumer Goods | 2000 | June | 800 | GPS | Industrials | 250 | ||||
| BVT | Industrials | 2005 | March | 35 | JTD | Oil & Gas | 450 | 620 | |||
| ZIK | Health Care | 2005 | March | 800 | PHO | Consumer Goods | 1280 | ||||
| JTD | Oil & Gas | 2000 | June | 450 | THT | Consumer Goods | 250 | ||||
| VRR | Consumer Goods | 2000 | June | 75 | VRR | Consumer Goods | 75 | ||||
| PHO | Consumer Goods | 2000 | June | 480 | ZIK | Health Care | 800 | ||||
| THT | Consumer Goods | 2000 | March | 250 | |||||||
Example 3. Determine averages using Sector to group the rows and Year to group the columns
The first column of the input range (Sector) is used as the row grouping and the second column (Year) is used as the column grouping. The final column is averaged by Sector and Year.
| Range | Pivot information and average per sector, per year | ||
| Sector | Year | Number | =Pivot(A3:C12,”C1″,”C2″,”AVERAGE(C3)”) |
| Oil & Gas | 2000 | 620 | |
| Industrials | 2000 | 250 | |
| Healthcare | 2005 | 25 | |
| Industrials | 2005 | 300 | |
| Oil & Gas | 2000 | 800 | |
| Industrials | 2005 | 35 | |
| Insurance | 2005 | 800 | |
| Oil & Gas | 2000 | 450 | |
| Insurance | 2000 | 75 | |
| Consumer Goods | 2000 | 480 | |
| Oil & Gas | 2000 | 250 | |
| Range | Output | ||||
| Sector | Year | Number | 2000 | 2005 | |
| Oil & Gas | 2000 | 620 | Consumer Goods | 480 | |
| Industrials | 2000 | 250 | Healthcare | 25 | |
| Healthcare | 2005 | 25 | Industrials | 250 | 167.5 |
| Industrials | 2005 | 300 | Insurance | 75 | 800 |
| Oil & Gas | 2000 | 800 | Oil & Gas | 530 | |
| Industrials | 2005 | 35 | |||
| Insurance | 2005 | 800 | |||
| Oil & Gas | 2000 | 450 | |||
| Insurance | 2000 | 75 | |||
| Consumer Goods | 2000 | 480 | |||
| Oil & Gas | 2000 | 250 | |||