If you do a lot of Excel work at home, work, or home office, you have the option to customize your worksheets exactly the way you want. However, each person has their own quirks and methods of working with Excel. Some people prefer to organize their data into columns and others into rows.
If someone gives you an Excel file and you prefer the data to appear in a different order, you can easily turn columns to rows and rows to columns using Excel’s built-in transpose function. There are two main ways to transpose data in Excel: by copying and pasting, or using the transpose function. While both will carry your data, they work differently, which I will explain below.
Switch using the TRANSPOSE function
Suppose someone gives you an Excel file with column-ordered data and you prefer the data to be rows.
Start by choosing the area where you want to move data from columns to rows. Note that in the above example, the data is taken from A1 to B6. This is a 2 by 6 (2×6) datasheet. To select an area to transpose, you need the opposite area or a 6 by 2 (6 × 2) area. Starting at cell A12 (or wherever you want to transfer data), select a 6×2 area.
– /
Notice that the cells we selected include A12 through F13, a 6×2 area. With this area selected, click the formula bar above. Before you start typing the formula, make sure the selection is still selected. Now enter the following formula into the formula bar
= transpose (a1: b6)
but don’t press Enter yet. Entering this formula in Excel is different from most other formulas. When you press Enter, you need to hold down the Ctrl and Shift keys. So press Ctrl + Shift + Enter. Your Excel worksheet should now look like this:
The keyboard shortcut Ctrl + Shift + Enter will enclose the formula in curly braces. This tells Excel that the output of the formula will be an array of data and not a single cell. Oddly enough, you can’t type the parentheses yourself; you must use the key combination Ctrl + Shift + Enter.
When you use the transpose function to transpose the data, both datasets are linked. This means that if you change the data, for example in A1, it will also change the value in A12. Even if the cells have formulas, the values ??will be updated in both places.
In the example below, I have some data on workers, hours worked and total wages. I used the transpose function to transpose the data.
I went ahead and changed one name and hours worked for all people, and as you can see, both datasets are in sync.
This also means that if you delete cells or rows of the original data, you will get a reference error in the transposed cells! If you don’t want the two datasets to be related, it is better to use the copy and paste method described below, which duplicates the data rather than concatenating it.
Switch using copy and paste
An easier way to transpose data in Excel is to use the copy and paste function. The data will not be linked, so you can safely delete the original dataset if you want. However, if you make changes to the original dataset, they will not be reflected in the transposed data, since it is just a copy.
Select the data you want to transpose and then right-click and select Copy, or press CTRL + C on your keyboard.
Now right click any empty cell where you want to paste data and click the Transpose button. If you just hover over the transpose button, you will see the data on the sheet in real time.
If you change any of the data in the original dataset, it won’t affect the transposed data, and vice versa. That’s all.
–