Let’s say we have a range of data that is 13 columns by 10 rows, like this:
We want to transpose the data range to another location, with the names across the top and the months down the left column, and link them so any changes to the original will also change the transposed data. It’s easy in Excel! Here’s how.
The first step is to determine how many rows and columns we have in our original range, and how many we will need in the transposed area. In our example, we have 13 columns and 10 rows, so we will need 10 columns and 13 rows for the transposed area.
We’ll go to the area that we want the transposed data range in, and highlight what will be the transposed number of rows and columns (in our example that will be 10 columns and 13 rows):
With the range highlighted, in the upper left cell, type the following formula: =TRANSPOSE(A1:M10) – this is the range of the original data:
But, instead of hitting ENTER, hit CTRL-SHIFT-ENTER. This creates an array:
Note the squiggly brackets around the formula. Now, if we change any data in the original range, the transposed range will change as well.
We can format the new data range and the links will remain:
Man, I just love some of these great features of Excel! I hope you do, too!