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!