Need Help - How do I do this?

in Excel For All9 months ago

Thanks Paula for sharing this community. I have been following your blog and youtube channel and you announced this community at a perfect time. I got the link from the youtube channel on the Christmas message video. I have been trying to improve in Excel as I would really like to get a new job. But there is so much to learn. Now I can just ask questions when I am stuck.

This might seem like a noobs question, but how do I change the cells in the Yellow, the ones that go down a column to look like the cells going across the row. You can see an example below. This is the typical sort of data entry I have to do on a regular basis with my job. However, I am often not entering the data to the same sheet, or workbook. I just put them on the same sheet to show an example of what I have to do.


Best of luck with the community Paula, I am glad you gave me that steem account. I never tried a beta anything before so this is a different opportunity for me. Do make sure you let me know when it is out of beta and open for anyone to join. I can share it for you then.


If your data is supposed to change you should go for an Array formula using TRANSPOSE function. The array formula for the example shown in the screenshot is

Now about how to enter this formula.
Select the cells from G2 to R3, Type in the formula =TRANSPOSE(B3:C14) and press CTRL, SHIFT and ENTER keys together. A pair of braces will be added to your formula and the data present in the cells from B3 to C14 will be transposed into the selected cells.

Solution 1.PNG

One more example for your understanding

To transpose the data present in the cells B2 to G4 into the cells D7 to F12,
Select the cells from D7 to F12, Type in the formula =TRANSPOSE(B2:G4) and press CTRL, SHIFT and ENTER keys together.

Example 1.PNG

did you know in Excel 365 you don't need to press CTRL, SHIFT and ENTER? Excel sees everything as arrays in Excel 365

Hay @angelak and welcome to the community.

Let me try and help you. Changing the data from rows to columns or columns to rows is known as transposing

If you copy the data, select where you want to paste the data, then right-click and under paste option, select transpose. This will work from one workbook to another.
The fastest way to do this is to learn the keyboard shortcuts.
Ctrl + C to copy
Ctrl + Alt + V to open paste special
E to select transpose
Enter to paste the transposed data

You could also use the function = TRANSPOSE(array), however, you mentioned that you copy to a different workbook and this could lead to linked workbooks which I don't think you want.

There is also an option to do it with Power Query. let me know if you want me to detail the steps involved using Power Query.

If this solves your problem , can you please add Solved to the title and upvote the comment

Congratulations @angelak! You have completed the following achievement on the Steem blockchain and have been rewarded with new badge(s) :

You published your First Post

You can view your badges on your Steem Board and compare to others on the Steem Ranking
If you no longer want to receive notifications, reply to this comment with the word STOP

Vote for @Steemitboard as a witness to get one more award and increased upvotes!

Hi @angelak, can you please confirm if this has been solved and edit the post title accordingly

Coin Marketplace

STEEM 0.17
TRX 0.03
JST 0.036
BTC 10836.36
ETH 363.42
USDT 1.00
SBD 0.98