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.

Sample Data To Be Transposed

Transposing Data Horizontally

Rotation of data from a horizontal to a vertical orientation is possible in three ways.

  1. Simple copy-pasting the data.
  2. Using transpose function.
  3. 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.

Selecting the Dataset

2. Press CTRL + C to copy it.

3. Go to the cells where you want to get the new result.

Copying The Dataset

4. Press right mouse click and go to paste special.

Paste Special Option

5. Checkmark the transpose option.

Checking Transpose Box

6. Click OK.

Data Transposed Horizontally

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.

Related: How to Calculate Standard Deviation in Excel

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.

Implmenting Transpose Function Without Selecting Cells

2. Press Enter.

Transpose Function Giving Error

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.

Selecting The Cells Range

2. Select the same array from the dataset (A3:B12) and add it to the transpose function.

Implementing 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.

Data Transposed With Transpose Function

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.

Auto-filling Remaining Entries of Transposed Data

Drag to the right corner of two cells to populate the data for the other two entries.

Cells Auto-filled But Not Correctly

Excel may not pick up the correct data when populating this way. So, here's how you can correct the Auto-Populated entries:

  1. Go to your main transpose function again.
  2. Again, press CTRL + Shift + Enter.
Transposed Data Corrected Again

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.

Changing Finch Salary to Test Its Dynamic Nature

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.

Adding A3 in Cell

2. Add any alphabet as a prefix before A3, let’s say X.

Adding A Prefix Before A3

3. Like A3, add the first cell (B3) of the second row with the same X.

Adding XB3 To Another Cell

4. Highlight both cells and drag them across the rest of the cells in columns 17 and 18.

Dragging Cells Ahead in Column 17 and 18

5. Select all the populated entries.

Entries Populated Successfully

6. Press CRTL + H to open up the Find and Replace menu box.

Find and Replace Dialogue Box

7. Enter “X” in the Find What option and add an equal sign (=) in the Replace With.

Replacing The Prefixes

8. Click on Replace All and press OK.

Clicking Ok in Dialogue Box

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.

Data Transposed Successfully

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.

Related: How the Data Table in Microsoft Excel Lets You Compare Results

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.