Can I Transfer Excel Data from Rows to Columns | Excel Tricks


Excel Logo on a gray background

In the event you began to enter information in a vertical association (columns) after which determined it will be higher in a horizontal one (rows), Excel has you coated. We’ll have a look at 3 ways to transpose information in Excel.

The Static Methodology

On this technique, you’ll be able to rapidly and simply transpose information from a column to a row (or vice versa), nevertheless it has one essential disadvantage: It’s not dynamic. While you change a determine within the vertical column, for instance, it gained’t robotically change it within the horizontal one. Nonetheless, it’s good for a fast and easy repair on a smaller dataset.

Spotlight the world you wish to transpose after which press Ctrl + C on the keyboard to repeat the information.

highlight columns

Proper-click the empty cell the place you’d wish to show your outcomes. Beneath “Paste Choices” click on “Paste Particular.”

paste special menu

Examine the field subsequent to “Transpose” after which press the “OK” button.

check transpose box

Transpose Knowledge with the Transpose Formulation

This technique is a dynamic resolution, which means we will change the information in a single column or row and it’ll robotically change it within the transposed column or row, too.

Click on and drag to focus on a gaggle of empty cells. In a great world we’d depend first, because the formulation is an array and requires you to focus on precisely the variety of cells you want. We’re not going to do this; we’ll simply repair the formulation later.

click and drag

Kind “=transpose” into the formulation bar (with out quotes) after which spotlight the information you wish to transpose. As a substitute of urgent “Enter” to execute the formulation, press Ctrl + Shift + Enter as an alternative.

transpose formula

As you’ll be able to see, our information has been lower off as a result of we didn’t choose sufficient empty cells for our array. That’s okay. To repair it, click on and drag the field on the backside, right-hand facet of the final cell and drag it out additional to incorporate the remainder of your information.

Our information is there now, however the result’s a bit of tousled due to our lack of precision. We’re going to repair that now. To appropriate the information, simply return to the formulation bar, and press Ctrl + Shift + Enter yet one more time.

formula bar

Transposing Knowledge with Direct References

In our third technique of transposing Excel information we’ll use direct references. This technique permits us to seek out and exchange a reference with the information we wish to show as an alternative.

Click on an empty cell and sort in a reference after which the placement of the primary cell we wish to transpose. I’m going to make use of my initials. On this case, I’ll use bcA2.

data entry

Within the subsequent cell, beneath our first one, sort in the identical prefix after which the cell location to the precise of the one we used within the earlier step. For our functions, that might be cell B2, which we’ll sort in as bcB2.

data entry

Spotlight each of those cells and drag the highlighted space out by clicking and dragging the inexperienced field on the backside proper of our choice.

drag to enlarge

Press Ctrl+H in your keyboard to deliver up the “Discover and Exchange” menu.

find and replace

Kind your chosen prefix, “bc” in our case (with out quotes), into the “Discover what” subject, after which “=” (with out quotes) into the “Exchange with” subject.

find and replace

Click on the “Exchange All” button to transpose your information.

You is likely to be questioning why we didn’t simply add “=A2” to the primary empty cell after which simply drag it out to autofill the remainder. The explanation for that is as a result of approach Excel interprets this information. It’ll certainly autofill the cell subsequent to it (B2), however it can rapidly run out of knowledge as a result of C3 is an empty cell and Excel reads this formulation from left to proper (as a result of that’s the way in which we’re dragging when transposing our information) as an alternative of high to backside.


Check Also

how to hide tinder profile

Hello there! I hope you’re doing well. Today, we are going to be talking about …