Using Transpose Function to change the Data Layout
How to use Excel's TRANSPOSE array function to switch data between landscape and portrait layouts automatically.
You may want to change the data layout in some occasions. For example, the data downloaded is in landscape mode but you want to change it to portrait mode. Or certain layout fits better with your dashboard than the other.
You can use copy → Paste Special → Transpose to do a one-time transpose. But if you need to do it every day, it is better to make it a formula instead of doing it manually each time.
Excel has a function called TRANSPOSE that makes this easy. Note that TRANSPOSE is an array function — you must press Ctrl+Shift+Enter instead of just Enter after inputting the formula.
Steps
Step 1: Use your mouse to highlight an area the same size as your data table but with the dimensions flipped. For example, if the original data is 8 columns × 7 rows, highlight an area that is 7 columns × 8 rows in the transposed location.
Step 2: Type the formula =TRANSPOSE(A5:H11) where A5:H11 is your data range including headings. You can use your mouse to select the range after typing =TRANSPOSE(.
Step 3: This is the key step — press Ctrl+Shift+Enter, not just Enter. The formula will now appear as {=TRANSPOSE(A5:H11)}. The curly braces indicate it is an array formula.
Step 4: Format the new transposed section as needed.
From now on, every time you change data in the original section, the transposed section updates automatically. The transposed section can be placed on a different worksheet or in a dashboard for reporting purposes.