Excel Formula Friday - Using Advanced Excel Transpose

Happy Friday Excellers. Welcome back to my next Excel Formula tip or Excel tutorial in this #formulafriday 2019 blog post series. Today I want to share with you how to build on the real useful Transpose Excel function. What can it have more uses?. Well, it can be more useful let’s just say that. If you have been Excelling for some time then you will probably have been using the Transpose functionality probably regularly to transpose or rotate rows of data to columns or columns of data to rows.

This is a really great straightforward function as already seen in the screencast above, as a one-off action. But let’s turn up the volume and make this transpose data dynamic. So, when you update your original data it updates your transposed data. This is really useful if every week or month your data delivery needs to be transposed, no need to create a new worksheet at all or write the transpose function over and over again. Just follow the steps below once and you will be good to go!.

Notes On Excel Transpose

This is an ARRAY Function. This means that you will need to hit Ctrl+Shift+Enter to use the function.

First, you need to select some cells. This formula is slightly different to the normal you may be used to, as you need to select the destination range of cells before you being typing the formula. So in our example, we need to select 7 Columns and 2 Rows as we originally had 7 Rows and 2 Columns. This is where the new transposed data will end up.

With the cells selected begin typing as normal as you would with any other formulas = TRANSPOSE(

Continue the formula and type the range of cells. In this example, it is A1B7. Do NOT hit enter yet.

Close your brackets or parentheses then press Ctrl+SHIFT+Enter at the same time to enter this as an array formula.

That’s it, whenever you update the original data your transposed data will update. Why not give this a go and experiment.

Have you ever used Excel TRANSPOSE in this way before or do you think it will be useful? – Please share in the comments below. We would love to hear from you.

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Tips on the first Wednesday of the month and you will automatically receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Formula Friday Series or the Macro Monday Series Click The Link Below