Unpivot Table LAMBDA Function

UNPIVOT.lambda

/*
Name: Unpivot Table (UNPIVOT)
Description: Given a table range with headers and array of header names, unpivots the 
   specified columns in place, optionally removing any blank entries.
Written By: Excel Robot (@ExcelRobot)
Category: Array
*/
UNPIVOT=LAMBDA(table,[columns_to_unpivot],[attribute_name],[value_name],[remove_blanks], LET(
   _ColumnsToUnpivot, IF(
      ISOMITTED(columns_to_unpivot),
      DROP(TAKE(table, 1), , 1),
      columns_to_unpivot
   ),
   _AttributeLabel, IF(ISOMITTED(attribute_name), "Attribute", attribute_name),
   _ValueLabel, IF(ISOMITTED(value_name), "Value", value_name),
   _FirstColumnToUnpivot, MATCH(INDEX(_ColumnsToUnpivot, , 1), INDEX(table, 1, ), 0),
   _UnpivotColumnCount, COLUMNS(_ColumnsToUnpivot),
   _ColumnNumbers, SEQUENCE(1, COLUMNS(table)),
   _IncludeColumns, (_ColumnNumbers >= _FirstColumnToUnpivot)
      * (_ColumnNumbers < _FirstColumnToUnpivot + _UnpivotColumnCount),
   _UnpivotColumns, FILTER(_ColumnNumbers, _IncludeColumns),
   _OtherColumns, FILTER(_ColumnNumbers, NOT(_IncludeColumns)),
   _FullOuterJoin, CROSSJOIN(
      CHOOSECOLS(table, _OtherColumns),
      VSTACK(_AttributeLabel, TRANSPOSE(_ColumnsToUnpivot)),
      TRUE
   ),
   _WithValues, HSTACK(
      _FullOuterJoin,
      VSTACK(_ValueLabel, TOCOL(DROP(CHOOSECOLS(table, _UnpivotColumns), 1)))
   ),
   _RemoveBlanks, IF(
      OR(ISOMITTED(remove_blanks), remove_blanks),
      FILTER(_WithValues, INDEX(_WithValues, , COLUMNS(_WithValues)) <> ""),
      IF(_WithValues = "", "", _WithValues)
   ),
   _ColumnOrder, LET(
      n, COLUMNS(_RemoveBlanks),
      s, SEQUENCE(1, n),
      IFS(
         s < _FirstColumnToUnpivot, s,
         s < _FirstColumnToUnpivot + 2, s + n - _FirstColumnToUnpivot - 1,
         TRUE, s - 2
      )
   ),
   _Result, CHOOSECOLS(_RemoveBlanks, _ColumnOrder),
   _Result
));

/*
Name: Cross Join Tables (CROSSJOIN)
Description: Given two tables, returns the Cartesian product of rows from the tables.
Written By: Excel Robot (@ExcelRobot)
Category: Array
*/
CROSSJOIN = LAMBDA(table1, table2, [has_headers], LET(
   _HasHeaders, IF(ISOMITTED(has_headers), TRUE, has_headers),
   _Data1, IF(_HasHeaders, DROP(table1,1), table1),
   _Data2, IF(_HasHeaders, DROP(table2,1), table2),
   _D1Rows, ROWS(_Data1),
   _D1Cols, COLUMNS(_Data1),
   _D2Rows, ROWS(_Data2),
   _D2Cols, COLUMNS(_Data2),
   _OuterJoinedData, MAKEARRAY(_D1Rows * _D2Rows, _D1Cols + _D2Cols,LAMBDA(i, j,
      IF(j <= _D1Cols, INDEX(_Data1, ROUNDUP(i / _D2Rows, 0), j), INDEX(_Data2, MOD(i - 1, _D2Rows) + 1, j - _D1Cols)))),
   _WithHeader, IF(_HasHeaders, VSTACK(HSTACK(TAKE(table1, 1), TAKE(table2, 1)), _OuterJoinedData), _OuterJoinedData),
   _WithHeader
));

The columns_to_unpivot parameter is now optional. So, when you have a range to unpivot that has only one column of keys on the left and you want to unpivot all the columns to the right, you no longer have to specify which columns.

it's not working, when column header more than two or more

it's not working, when column header more than two or more

True, it's only expecting a one row header at the top of your table. Thanks for clarifying that!

how to add this formula in my excel 365

how to add this formula in my excel 365

By far, the easiest way to use UNPIVOT in any workbook using Excel 365 desktop is by having the OA Robot Excel add-in installed and use the Unpivot Array command in Array Robot Vol 1. At the time of this comment, OA Robot is still in private Beta and there is a waiting list at https://www.oarobot.com, but it'll be publicly available soon and the Unpivot Array command (and tons of other robot commands) will be included in the free version.

If you are not yet using OA Robot, you can still add it the manual way using Excel Labs' Advanced Formula Editor. First install the Excel Labs Office add-in, and click the ribbon icon to start it up. Under Modules tab in the Excel Labs task pane, click Import From URL icon, enter in the url to this Gist (https://gist.github.com/ExcelRobot/54fe4bf04f24a83b83d3b3bddda4d3c3), then click the Save icon. This will save the UNPIVOT lambda (and it's dependency, CROSSJOIN) into the active workbook only.

Happy unpivotting!