قالب وردپرس درنا توس
Home / Tips and Tricks / Transpose Excel data from rows to columns (or vice versa)

Transpose Excel data from rows to columns (or vice versa)

Excel Logo on a gray background

If you started entering data in a vertical arrangement (columns) and then decided it would be better in a horizontal arrangement (rows), then Excel has you covered. We will look at three ways to transpose data in Excel.

The static method

This method allows you to quickly and easily transpose data from a column to a row (or vice versa), but it has one critical drawback: it is not dynamic. For example, when you change a number in the vertical column, it is not automatically changed in the horizontal column. Still, it is good for a quick and easy solution for a smaller data set.

Highlight the area you want to transpose and then press Ctrl + C on the keyboard to copy the data.

highlight columns

Right-click the empty cell where you want to display your results. Under ‘Paste options’, click on ‘Paste special’.

paste special menu

Check the box next to “Transpose” and then press the “OK” button.

tick the transpose box

Transpose data with the transpose formula

This method is a dynamic solution, which means that we can change the data in one column or row, and it will automatically change in the transposed column or row as well.

Click and drag to highlight a group of empty cells. In an ideal world, we count first since the formula is an array and you need to mark exactly the number of cells you need. We are not going to do that; we’ll just solve the formula later.

Click and drag

Type “= transpose” in the formula bar (without quotes), then highlight the data you want to transpose. Instead of pressing “Enter” to run the formula, press Ctrl + Shift + Enter instead.

transpose formula

As you can see our data has been cut off because we have not selected enough blank cells for our array. Which is good. To fix the problem, click and drag the box at the bottom right of the last cell and drag it further out to include the rest of your data.

Our data is there now, but the result is a bit confused due to our lack of precision. We’re going to fix that now. To correct the data, go back to the formula bar and press Ctrl + Shift + Enter again.

Formula bar

Convert data with direct references

In our third method of converting Excel data, we use direct references. With this method, we can find a reference and replace it with the data we want to display instead.

Click on an empty cell and type a reference and then the location of the first cell we want to transpose. I’m going to use my initials. In this case I am using bcA2.

data entry

In the next cell, under our first, type the same prefix and then the cell location to the right of the one we used in the previous step. For our purposes, that would be cell B2, which we will type as bcB2.

data entry

Highlight both cells and drag the highlighted area out by clicking and dragging the green box at the bottom right of our selection.

drag to enlarge

Press Ctrl + H on your keyboard to open the “Find and Replace” menu.

find and replace

Type your chosen prefix, “bc” in our case (without quotes), in the “Search for” field, then “=” (without quotes) in the “Replace with” field.

find and replace

Click on the “Replace All” button to transpose your data.

You may be wondering why we didn’t just add “= A2” to the first empty cell and then dragged it out to auto-fill the rest. The reason for this is due to the way Excel interprets this data. Indeed it fills in the cell next to it (B2) automatically, but it quickly runs out of data because C3 is a blank cell and Excel reads this formula from left to right (because that’s how we drag when transposing our data) instead of top to bottom.

Source link