Transpose a Records from One Column to Multiple Columns

This video shows how to transpose a list of names and addresses that are in one column to to proper table format. There are two ways that are demo'd. One using formulas/functions and the other using Power Query.

📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!

#excel
#msexcel
#doughexcel

-~-~~-~~~-~~-~-
Please watch: "Convert Table in a PDF File to Excel"
-~-~~-~~~-~~-~-

[wooster]

Doug,
Took me a while to figure out how the row-column portion of the OFFSET function worked, but once it clicked, it made perfect sense. Got the function to work as advertised. Thank you!

[matejarezonja]

Great instructional video. Both versions work. If you copy the formula to Excel you have to know what separators your country uses for formulas in Excel. I had to change dots, to semicolon ; and the formula worked perfectly.

[LotfyKozman]

One of the most favourite and practical videos ever watched in the world of Power Query. Thanks

[salvi]

Exactly what I was looking for.. Except Wat if the column had inconsistent data.. Like say in ur example if for 2 of the entries there was another data like birthday or age.. How do u deal with inconsistent data.. The data I have is product image links.. Some have 5images while some have six.. So how do I add it in one row.. I saw someone use index and match to do this.. Would you be able to explain that

[pralhadshetye]

Thanks a lot. You saved a lot of time for me.

[troyreese]

Really well done. Does exactly what I needed!

[Hairmetal]

Exactly what I was looking for! Going to have to work more with Power Query!

[rockguitarist]

Good vid! Very cool feature and nice to see two diff methods.

[HrobjarturArnason]

Thanks, Very useful, and easy to follow.

[kazimd.mosharrofhossain]

Thank you so much. worth watching. saves a lot of time. Thank a lot Sir

[tungnguyenson]

Thank you smch! That exactly what i want, saved my life.

[kalkun]

thank so much
its really very helpful

[AniManuSCh]

Is there a way to do the viceversa with spill formula on office 365? and with an indirect reference like: a range defined by the user, for example the user in T9 writes E10:F11 then in A1 will be the formula to spill in a single column all the values of E10:F11 that were found.

[MJovad]

What should we do if we have different rows for each entry e.g. if there would be 3 rows for Sally while 5 for Mike, 6 for Puneet etc. We need to show data in front of each name in multiple columns

[ganesh]

Great instructional video, But I want to Transpose data such has some Heading has 5 rows & some has 10 so How would I do that please help??

[muhammadyounas]

My formula is not copying as yours. Its is being copied in other cells as like first cell. Kindly guide me how can I copy it dynamically.

[kalkun]

hi doug
thanl u for this upload, , ,
Kindly help with elaboration n demo1.
I am trying since last one day on my excel sheet but its not going through...
looking forward for ur kind help

[zendastudio]

thanks dough, can u show me how to make combo box with 2 column.

[sktneer]

You could have renamed the column headers in the Power Query Editor itself.

[rajeshdas]

Is this formula need any special excel version ? Because its not working. Specially the A1 column. Do I need to freeze A1 ?