Turn a table into a list of row/column/value tuples

Question

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.

Answer 1

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

Answer 2

If the data is in A1:D4

Add the following

Row labels (first column), F2:F10, use

=OFFSET($A$1,INT((ROW()-ROW($F$2))/COUNTA($A:$A))+1,0)

Header labels (second column), G2:G10, use

=OFFSET($A$1,0,IF(MOD(ROW()-ROW($F$2)+1,COUNTA($1:$1))=0,COUNTA($1:$1),MOD(ROW()-row($F$2)+1,COUNTA($1:$1))))

For the values (third column), H2:H10, use

=Index(OFFSET($A$1,1,1,COUNTA($A:$A),COUNTA($1:$1)),Match($G2,OFFSET($A$1,1,0,COUNTA($A:$A)),0),MATCH($H2,OFFSET($A$1,0,1,1,COUNTA($1:$1)),0))

If a new rows or columns are added, just fill down until you get all the required tuples.

File that shows the above formulas working https://docs.google.com/spreadsheets/d/1YCxCEVU_zO3NL8vi95B4adoHoPMc8fWZUK2mTLG6MQc/edit?usp=sharing