|
UNPIVOT Function
Transforms table columns from a Range, Array, Table or spill reference into rows. The UNPIVOT function is similar to Power Query unpivot, except that it is dynamically driven by the Excel calculation engine rather than by Power Query commands.
UNPIVOT allows for multiple header rows, including or excluding unpivot and other columns and providing labels for the attribute and value columns.
UNPIVOT is a multi-threaded, non-volatile array function.
UNPIVOT Syntax
UNPIVOT (Table_Range, [ UnPiv_Columns], [Other_Columns], [Attrib_Names], [Value_Name] [, HasHeader])
Table_Range
Array, Table, Range or Spill reference to unpivot
UnPiv_Columns (Optional)
Optional. A one-dimensional array or range specifying which columns to unpivot.
Either UnPiv_Columns or Other_Columns or both should be specified.
If Other_Columns is not specified all other columns will not be un-pivoted.
A list (array or range) identifying the columns that are to be un-pivoted, given as one of:
If Table_Range is not an array: One or more ranges within Table_Range. Multiple disjoint ranges must be enclosed in ( ) – for example (A1:B1,D1) results in columns 1,2 and 4.
Alphabetic labels are used as lookup values in the first row of Table_Range to find the columns. These labels can be supplied in an array or a range.
Relative column numbers are used directly.
These column numbers can be supplied in an array or range.
For example {2,4} gets the second and fourth column of the Table_Range.
Negative numbers work from right to left so {-1, -2) gets the last two columns of the Table_Range.
Zero acts as an include operator.
{1,0,5} results in Columns 1 through 5 inclusive
{1,5} results in Column 1 and Column 5 only
{5,0} results in Column 5 and all Columns after Column 5
{0,5} results in the first 5 Columns
Other_Columns (Optional)
Optional. A one-dimensional array or range specifying which columns not to unpivot.
Either UnPiv_Columns or Other_Columns or both should be specified.
If UnPiv_Columns is not specified all other columns will be unpivoted.
A list (array or range) identifying the columns not to be unpivoted, given as one of:
If Table_Range is not an array: One or more ranges within Table_Range. Multiple disjoint ranges must be enclosed in ( ) – for example (A1:B1,D1) results in columns 1,2 and 4.
Alphabetic labels are used as lookup values in the first row of Table_Range to find the columns. These labels can be supplied in an array or a range.
Relative column numbers are used directly.
These column numbers can be supplied in an array or range.
For example {2,4} gets the second and fourth column of the Table_Range.
Negative numbers work from right to left so {-1,-2) gets the last two columns of the Table_Range.
Attrib_Names (Optional, default “Attribute”)
Optional. Default “Attribute”. A range or array or constant of the names to give to the columns of unpivoted column labels. (If no alphabetic column labels are found the column will be created using Column1, Column2, …)
Value_Name (Optional, default “Value”)
Optional. Default “Value”. The name to give to the column of unpivoted values.
HasHeader (Optional, indicates the number of header rows in Table_range)
Optional. Default -1: automatic detection of header
An integer value specifying if Table_Range has a header row:
-1 If the first column of the first row of the UnPiv columns is alphabetic the first row is assumed to be a header row, else not.
0 Table_Range does not have a header row
1 Table_range has a single header row.
N Table_range has N header rows.
UNPIVOT Example
.png)
(This example uses sample data from”M is for (DATA) Monkey” by Ken Puls and Miguel Escobar, ISBN 978-1-61547-034-1)
We want to unpivot this data, but ignore the Total column and include the extra column J the user has added after the Total column.
=UNPIVOT(Data!A4:J8,(Data!B4:H4,Data!J4),"Sales Category","Date","Units")
Data!A4:J8 gives the data we want to unpivot and ignores the Total Row.
(Data!B4:H4,Data!J4) gives the columns we want to unpivot and ignores the Total column.
“Sales Category” gives the column NOT to unpivot
“Date” and “Units” give labels for the attribute and Value.
The result of the UNPIVOT formula is
.png)
|
