Using Transpose Function to change the Data Layout
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. Of course, you can just simply use copy-and-paste-special-transpose function to do the data transpose. But if you need to do it every day, it is better to make it a formula instead of manually doing it. Again, I would highly discourage people using Visual Basic codes to do it because I believe that we should always use the simplest way to do things provided that the results are the same.
Microsoft has a wonderful function called Transpose that will make the job super easy. Please note that the Transpose function is one of the array functions. It means that you need to press CTRL+SHIFT+ENTER keys at the same time instead of just using the ENTER after inputting the formula.
I have the above data but I want to change it to have the months showing on top and the countries showing on the left. These are the steps that I will use:
Step 1: Use my mouse to highlight an area exactly the same as my data table but different layout. For example, the 8 columns in the original data area, so it will become 8 rows in the transposed area. As the same token, the columns in the transposed area should be 13 columns.
Step 2: Input this formula: =TRANSPOSE(A5:H11) where A5:H11 is the data area including the headings (countries and months). You can use your mouse to select the data range after you type in =TRANSPOSE(. You just need to put an ending parenthesis “)” at the end of the formula.
Step 3: This is the key step, press CTRL+SHIFT+ENTER keys at the same time, not just “ENTER”. The formula will now show up as {=TRANSPOSE(A5:H17)}. Please note the two brackets {} at the beginning and ending of the formula. They indicate that the formula is an array.
Step 4: Format the new data section.
From now on, every time I change the data in the original section, the data in the transposed section will be changed automatically. The transposed section can be put into a different worksheet or in the dashboard for reporting purposes.
File to download: Using Transpose Function to change the Data Layout.xlsx
Related Article: Count Duplicates as One Item
