Google Sheets – Turn Table into List of Row/Column/Value Tuples

google sheets

I want to turn a table like this

|          | apple | banana | chia |
|----------+-------+--------+------|
| antelope | 1     | 4      | 2    |
| bear     | 6     | 3      | 9    |
| cheetah  | 8     | 2      | 1    |

into a table containing the same data, but with one row for each value, explicitly listing the row header, column header, and the value.

| antelope | apple  | 1 |
| antelope | banana | 4 |
| antelope | chia   | 2 |
| bear     | apple  | 6 |
| bear     | banana | 3 |
| bear     | chia   | 9 |
| cheetah  | apple  | 8 |
| cheetah  | banana | 2 |
| cheetah  | chia   | 1 |

Ideally, modifying the original table will automatically update the tuple table.

Best Answer

Alternatively, for a 1-formula solution automatically updating when new rows are added, try:

=ArrayFormula({trim(transpose(split(concatenate(rept(A2:A&char(9),counta(B1:1))), char(9)))), transpose(split(rept(join(char(9), B1:D1&char(9)),counta(A2:A)), char(9))), transpose(split(CONCATENATE(B2:D&char(9)), char(9)))})

NOTE: after adding a name in column A, column B, C and D can not be left blank.

Example spreadsheet