Create a "Live" Transposed Copy of a Table
How to transpose rows and columns, part II. Let's say you want to see the same table in two parts of your worksheet, with one of them a transposed version of the other, and with any data that you change in the first version instantly changed in the other. This takes a bit of forethought, but it's easy when you get the idea. Select a table; count its rows and columns--for example, if the table is at A1:H11, then it has 11 rows and 8 columns. Select a blank region of your worksheet with the dimensions transposed--in this example, 8 rows and 11 columns. In the upper-left cell enter the formula =(TRANSPOSE(A1:H11), but with the addresses of your actual table. Press Ctrl-Shift-Enter, which is the little-known Excel keystroke that creates an array formula. Excel will put curly braces around the formula to indicate that it's an array formula. Any change you make in the original table will also appear in the transposed version, but you can't make changes directly to the transposed version.
Vision LFUG in Austin 4/21/ - 4/23.
Attend our Speaking Session Wednesday, April 23rd at 1:15pm
Excel-lent April Fool's Pranks
Let's see how you can April fool's prank your coworkers by making subtle changes to their Excel settings. No, you're not wasting company time, you're testing your colleague's Excel skills.