When you create datasets, you frequently have to rotate rows and columns. Excel lets you transpose data in any direction. Transposing data means converting columns into rows and rows into columns.
This article describes three different ways of transposing data horizontally. The first two are easier to use for small datasets, but the last one is better for complex datasets.
Transposing an Example of Excel Data
Take a look at the dataset below. Two columns list the names of employees and their salaries.
Transposing Data Horizontally
Rotation of data from a horizontal to a vertical orientation is possible in three ways.
- Simple copy-pasting the data.
- Using transpose function.
- Entering formulas as text.
Let’s see how all three transposing methods work, and what limitations each has.
1. Simple Copy Pasting
This is a straightforward way to transpose vertical rows into horizontal columns by copying the data in rows and pasting it into columns. Here is how you can transpose data using this method.
1. Select the cells you want to transpose.
2. Press CTRL + C to copy it.
3. Go to the cells where you want to get the new result.
4. Press right mouse click and go to paste special.
5. Checkmark the transpose option.
6. Click OK.
Using this method to transpose the data is super simple, but there is a downside. The transposed data is not dynamic. If you add new information in cells down below this data or alter the existing data you have just transposed, the changes won’t be fetched automatically by excel.
Therefore, you may need to add new data manually or transpose the data from scratch.
2. Using the Transpose Function
With the Transpose Function, you can instantly rotate data from rows into columns. It requires one argument, array, which refers to the dataset you want to be transposed. Let’s apply it to the sample data for the above example.
1. Select range (A3: B12) as array argument in transpose function.
2. Press Enter.
As shown above, it implemented the transpose function incorrectly. This is because we are attempting to convert an entire array into a single cell, which is not possible.
Instead of selecting a single cell, highlight the area containing the exact number of cells in the selected range. This way, the transpose function will allocate one cell for each value in the dataset without overlapping or causing errors.
You must, however, first highlight the area. So aside from counting the number of cells, you will not have the option to select an exact area.
If you are not patient enough, you can randomly select a few cells. Then, after pasting the data, you can extend the selection to pick the rest of the information. Let’s check how you can do it.
1. Highlight cells with range, let’s say, F12: M13.
2. Select the same array from the dataset (A3:B12) and add it to the transpose function.
3. You will get the same error if you press Enter to execute the formula, as you usually do.
4. Instead, press CTRL + Shift + Return.
It executed the transpose function perfectly, but it missed the last two values in the range. The reason for this is that the selected cells were less than the number of cells in the range.
Drag to the right corner of two cells to populate the data for the other two entries.
Excel may not pick up the correct data when populating this way. So, here’s how you can correct the Auto-Populated entries:
- Go to your main transpose function again.
- Again, press CTRL + Shift + Enter.
3. As it’s dynamic, any changes you make in the original dataset will be reflected. Changing the salary of Finch to 50,000 has automatically updated the transposed area.
While the transpose function is convenient, it may not work well for large datasets since counting a large dataset is time-consuming. That’s where another technique to transpose data effectively comes into play. Let’s discuss it here:
3. Entering Formulas as Text
In this technique, you’ll have to type the formula as text.
1. Manually type A3 in any cell.
2. Add any alphabet as a prefix before A3, let’s say X.
3. Like A3, add the first cell (B3) of the second row with the same X.
4. Highlight both cells and drag them across the rest of the cells in columns 17 and 18.
5. Select all the populated entries.
6. Press CRTL + H to open up the Find and Replace menu box.
7. Enter “X” in the Find What option and add an equal sign (=) in the Replace With.
8. Click on Replace All and press OK.
You will see how many entries it has replaced in the dialogue box, which you can compare with the original dataset to confirm that the technique was applied correctly.
In the same way as the Transpose function, this method of transposing data is dynamic. Therefore, any changes made to the original array will be automatically updated.
Which Technique Is Most Useful to Transpose Data?
Due to two factors, the last technique is a much simpler approach than using the Transpose formula.
- It doesn’t require you to select the range of cells before applying the formula.
- As you populate the entries, you’ll see the referenced cell number along the way.
You can use this technique on large datasets if you know the starting and ending cell numbers in the range.
Can All Three Methods Be Used to Transpose Horizontal Data?
Simply copying the data and using the Transpose function method works exactly the same for transposing both the horizontal and vertical data. However, the last method is a bit tricky while implementing for vertical data. So, always go with the first two methods while transposing horizontal data.
Transpose Data Effectively With These Three Methods
Changing data orientation often involves rotating vertical rows into horizontal columns. Using both static and dynamic methods, Excel allows you to transpose data horizontally.
Static methods are much easier to execute, while dynamic methods are helpful if you need to change the information later. To determine which method works best for you, you can try all of them.