Array formula does not return array (Excel 2016)

I am trying to use an Offset function to return numbers from a table (multiple rows and columns). However, the array is only returning the first cell.

This happens for all array formulas as well (such as TRANSPOSE), where dragging the formula down would not help transpose the data.

where dragging the formula down would not help transpose the data

Yep, entering formula in one cell and copying / dragging won't work.

These array formulas work correctly only if you enter them once: select all destination cells, type your formula and press CTRL+SHIFT+ENTER.

See also help on TRANSPOSE: https://support.office.com/en-us/article/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027