Excel Tips: Using Transpose in an Array Formula

You have a table that is configured horizontally and want to retrieve its values into a vertical layout. How do you quickly create a single formula to retrieve the these values?

In the example here I listed the number of movies I like on different NBC cable channels. Your horizontal layout (in this case B2:G3) could reside in a separate tab. Perhaps it is a P&L that has multiple columns for each fiscal year compared (2008, 2007, 2006, etc). You want to retrieve the values in a horizontal fashion (as the above picture range in columns I and J.

Open up excel and try this with me:
1. Enter the data exactly as listed above (don't worry about formatting it pretty)

2. Highlight the range J3:J7 and then enter the formula shown below (don't hit enter yet!):

In case you can't read this clearly, the formula is: =transpose(b3:g3

3. Press Ctrl+Shift+Enter. You have now entered an array formula into the range of cells you selected (J3:J7) and this is what your spreadsheet should now look like:

Bonus tip: To copy the headings from B2:G2 into the I column, do this: Select the range B2:G2, move your cursor to I2 and select Edit -> Paste Special. In the paste special dialogue box turn on the "Transpose" checkbox and hit enter!

Have a challenging Excel task and want help? Let me know what you are interested in and I will post the tip on my blog!


Hector said...

Good one thank you!!

Anonymous said...

Good fill someone in on and this post helped me alot in my college assignement. Thank you as your information.

Anonymous said...

hi, good site very much appreciatted