Excel HACK: Change HORIZONTAL Data to VERTICAL (NO Transpose Function needed)

1 year ago
125

May 9, 2019 Excel Advanced Formulas & Features

How can you reconfigure a horizontal row to a vertical column?
You can use a simple Excel hack to get this done.

In my previous video (https://youtu.be/yYVokk0NdiI) I talk about 2 other methods you can use. One method is to copy the data and paste special and select transpose. This way however the data is static.

Another option is to use the Excel TRANSPOSE function. One thing I don't like about the Transpose formula is that it's an array function and you need to remember to press Control shift enter (at least until you get dynamic array functionality). You also need to highlight the answer area first before inputting the Transpose formula.

This Excel hack is really simple. It's also dynamic and you can also account for additional cells. I came across this from Bob Umlas (his Excel books are great by the way). All you need to do is briefly switch to Excel's R1C1 referencing style. Find out more in the video.

⯆ Blog post and Workbook: https://www.xelplus.com/excel-horizontal-data-to-vertical/

LINKS to related videos: 3 ways to Transpose (including Transpose function): https://youtu.be/yYVokk0NdiI

Unstack Excel data: https://youtu.be/rscXNUlRsH0

★ My Online Excel Courses ► https://courses.xelplus.com

✉ Subscribe & get my TOP 10 Excel formulas e-book for free
https://www.xelplus.com/free-ebook/

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593

GEAR
Camera: https://amzn.to/2FLiFho
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Microphone: https://amzn.to/2DVKstA
Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#MsExcel

Loading comments...