You can do the transposing without any macro:

The direct way for your first step would be to use the `TRANSPOSE`

function, i.e. select a range that is 365rowsx24cols and enter `=TRANSPOSE(B4:NB27)`

and press `Ctrl`+`Shift`+`Enter`.

However, I'd recommend to address problem 1 and 2 in a slightly different manner: Translate your data to a list, i.e. a table with 365x24 rows x 3 columns: *Date*, *Hour*, *Value*. This table can then be easily be used as the source for a pivot table!

To do so, you could either use two helper columns - the first one starting at 1 and increasing every row by one until it's 24 then reset (In A2: `1`

, in A3: `=IF(A2=24,1,A2+1)`

), the second starting with 1 and increasing by 1 every 24 rows (B2: `1`

, B3: `=IF(A3=1,B2+1,B2)`

. Alternatively, you could simply use the `ROW()`

function (which returns you the row of the cell with the formula if no parameter is provided) to derive the number 1 to 24 with `MOD(ROW(),24)-1`

and the 1 to 365 with `INT((ROW()-2)/24)+1`

. (The `+1`

and `-2`

is to adjust for the data table starting in row 2).

Using the `INDEX`

function with those two parameters, you can now easily retrieve the data for the 3 columns:

Date: `=INDEX($B$3:$NB$3,B2)`

(or without the helper columns: `=INDEX($B$3:$NB$3,INT((ROW()-2)/24)+1)`

)

Hour: `=INDEX($A$4:$A$27,A2)`

(or `=INDEX($A$4:$A$27,MOD(ROW(),24)-1)`

)

Value: `=INDEX($B$4:$NB$27,A2,B2)`

(or `=INDEX($B$4:$NB$27,MOD(ROW(),24)-1),INT((ROW()-2)/24)+1)`

)