How To Transpose A Data Range And Retain The Links In Excel

In this tutorial we are going to review how you can transpose a range of data and keep the original and the transposed range linked, so that if you change anything in the original, the transposed range will also change.

You can download the file here and follow along. When you get a preview, look for the Download in the upper right hand corner.

Let’s say we have a range of data that is 13 columns by 7 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 7 rows, so we will need 7 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 7 columns and 13 rows):

With the range highlighted, in the upper left cell, type the following formula:


This is the range of the original data. But, instead of hitting ENTER, hit CTRL-SHIFT-ENTER. This creates an array:

Note the curly brackets around the formula. Now, if we change any data in the original range, the transposed range will change as well. And 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!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply