Switch Rows with Columns — data transposing
Today, we want to learn how to transpose our data, i.e. how to switch columns with rows.
We have two solutions to choose, a static one and a dynamic one.
Let’s use the first one. We need to copy our data (1) and then right click on our target cell (2). From the pop-up menu, we select the Transpose option (3) (Fig. 1)
We can see that our data has been transposed (Fig. 2)
Now, let’s use the dynamic solution. Here, we just use the TRANSPOSE function and select the array we want to transpose (Fig. 3)
=TRANSPOSE(A1:B6)
And we have our data transposed. However, we can see here that Excel didn’t copy the cell formatting. If we want the same formatting, we should copy it from somewhere else (1) using the Format Painter (2) option, and use it in the target place (3) (Fig. 4)
When we change something in our original data, the data in the static solution won’t change, but the function will.
In versions older that the Dynamic Array Excel, we should select a proper range before putting the formula in a cell, then press the Ctrl + Shift + Enter combination to enter it as an array formula.