Hi Doug: This is what I was exactly looking for! Light bulb went on when you described how clicking on the grand total in the pivot table takes you to a table of records. THAT is what I needed. This solution is appropriate for data with more than two data points. I had a ton of complex horizontal data that needed to be in vertical format. Your solution fit better than using the transpose formula. Thank you so much!
Pivoting table of this kind of data set has always bothered me, thanks a ton mate for the video, This is actually one one of the hidden feature of Excel, people rarely use.
fantastic! I needed to "find" the pivot table wizard as I'm using a recent version of excel but was able to add it to the ribbon and take it from there. problem solved, thanks so much!
Doug, excellent job and thanks for explaining this technique. It is a little different to find the pivot table wizard with the 2011 version on a Mac where you have to hit the keys: command, option, P.
If you have the latest excel version, then the unpivot feature in Power Query is the way to go. ua-cam.com/video/TVjswF_HKCE/v-deo.html ua-cam.com/users/DougHExcelsearch?query=unpivot
Hi Doug, thanks so much for sharing the tip. However, I am unable to pivot the data into the one you showed (at 3:40). My fields become "key", "Q1", "Q2", "Q3" and "Q4", and how recognize them as "Column" and have "Column labels" on top of the four quarter fields? Without this, calculating "grand total" for each key is impossible. Thank you so much for your help.
sorry, I don't quite understand...what you describe above should be the intended outcome (i.e., having the fields become "key", "Q1", "Q2", "Q3" and "Q4)
don't know about that in the mac; this can be done better in with power query (PQ) as the un pivot feature ua-cam.com/video/TVjswF_HKCE/v-deo.html PQ may be available in later version of Mac
Thank you! This is exactly what I needed! Unfortunately, I didn't have the option to bring up the pivot table wizard on my Mac Excel 2016. But, worked perfectly through Windows.
Power Query was meant to handle large datasets in the millions of records. For insight see ua-cam.com/users/dough517search?view_as=subscriber&query=power+query+unpivot
Hi Doug, Very nice trick. I want to do the same but the opposite way. From duplicate values table to make one with unique values and more columns. Any suggestions? Thanks for your time
Thanks Doug, I used this and it saved me a ton of time. I am working on something now that has real column headers with data correctly underneath them, but the dates are across the top and there are 6 lines per record below each date for data labeled on the Rows. There are about 252 rows/ 42 records How can I manipulate this to keep the first 6 columns of correct column oriented data, but convert the date column headers as data rather than a column headers and keep the 252 rows from becoming 252 columns. Thanks
Power Query may help out. Here's my video playlist -> ua-cam.com/play/PL-n8f1cY_Qw9v3Y-jSrPy8-ctFw2yJjLZ.html These specific videos may give some insight: (#11) Convert a Single Column Table to Multiple Column Table [2 Examples] and (#21) Use the Unpivot Feature in Power Query
just concatenate the values of the five columns. You can also do this with microsoft Power Query using the unpivot feature...See this vid ua-cam.com/video/TVjswF_HKCE/v-deo.html
Ho can i auto transpose vertical data in horizontal in the group/breakage of 3 values ex.Vertically 123456789 i want horizontally 123 456 789 like wise please suggest !!!
sounds like you need a to reverse the the records and have the last record first like this: 789 456 123 and then concatenate it. Like in this video, but without the extra comma. ua-cam.com/video/W4sK4V9G0GY/v-deo.html
Really good advice Doug. But I'm having a problem. I did exactly that you show, but when I'm creating the pivot table, excel is returning a table only showing numbers "1" and not the data included in the original table. Do u have any advice? Pablo.
Excellent tip about double clicking in the totals cell. Does anyone know how to do this for large data sets? I have 400,000 rows of data, with 100 values for each row. I’m looking for a way to create a column data set with 40,000,000 rows. This is beyond excel I think. Can this be done in access maybe? Would be grateful if there’s an affordable solution. Cheers.
please i want your help in my data, i have a column of data about 85000 and i want to transpose it into six column and any number of rows until the numbers is finished sequentially. thanks
I have a dataset like this: docs.oklahomacounty.org/sheriff/SheriffSales/ (click through and select a date, doesn't matter wich one) - How do I keep the data on the left column and put those into columns and then put the data on the right side (the various property details) into columns under each column?
If the data is a the same number of rows for each data set the propose may be similar to what's done in this video ua-cam.com/video/OZbFPGwcyrE/v-deo.html
Happy tears! I have been searching for days and days for this solution! Thank you from the bottom of my heart!
Absolutely fantastic. I've been looking for a way other than manual manipulations to do this. Magic! Thank you.
I never comment, but you sir, deserve one. I too have been searching for days for this. This is a life saver. Kudos.
Hi Doug: This is what I was exactly looking for, thank you for helping people in trouble.
Glad it helped you!
This video saves me a ton of work at least twice a year. I'm grateful every time I revisit it to refresh my memory on what I need to do. Thank you!!!
Hi Joseph Schultz, glad you liked it, thanks for commenting!
Hi Doug: This is what I was exactly looking for! Light bulb went on when you described how clicking on the grand total in the pivot table takes you to a table of records. THAT is what I needed. This solution is appropriate for data with more than two data points. I had a ton of complex horizontal data that needed to be in vertical format. Your solution fit better than using the transpose formula. Thank you so much!
Hi Carol Sprock, glad you liked it, thanks for commenting!
I keep coming back to this explanation, using this process relatively frequently. Thx for this!
Pivoting table of this kind of data set has always bothered me, thanks a ton mate for the video, This is actually one one of the hidden feature of Excel, people rarely use.
Hi Anuj Gupta, glad you liked it, thanks for commenting!
thank you so much.... Initially I did'nt find the pivot wizard and then found how to insert... there we go. Saved a lot of time
Thanks Justus Abraham, glad it helped!
You just made my day... Thank you so much :) Watched about 10-12 videos to reach here :) God Bless
Glad it helped!
Brilliant - so simple - I spent hours trying to get the same outcome with macros & VB scripts ... Thumbs up!
Hi Jules B, glad you liked it, thanks for commenting!
Doug, cheers for this video. I had to log in just to say thank you. you can't imagine, you just saved me a day work!
Hi swamfax, thanks for the kind words!
Outstanding video and walk of the process, thanks for sharing!
Thank you so much. This may sound like an overstatement but you really saved my life with this video (y)
Thank you Sir! ..This is a life saver and nothing short of a data sorting miracle to me.
Thanks Inderjeet Singh, glad it helped!
awesome trick mate! such a simple and creative solution to a complex problem
Glad you liked!
You just saved my life! Thank you!!
You are welcome!
fantastic! I needed to "find" the pivot table wizard as I'm using a recent version of excel but was able to add it to the ribbon and take it from there. problem solved, thanks so much!
Fantastic!
Doug, excellent job and thanks for explaining this technique. It is a little different to find the pivot table wizard with the 2011 version on a Mac where you have to hit the keys: command, option, P.
Very nice. Much easier than trying to convert to a table format in access.
Amazing! I was struggling with this!!!
You can do it!
Been looking for this for almost an hour. Thank you!
Hi ravens2376, you're welcome!
Love it.. I was just to buy a tool to do this, but with this.. I saved a few dollars
wow. this has saved me much time. thank you!
Thanks LMT, glad it helped!
Outstanding, just what I needed!
Very very helpful!!! Thank you so much!
Hi Doug, Fantastic, However, I have the opposite problem, I want to do this but in reverse, and tips?
Thank you so much Doug! I needed to import an excel into our database but the format of the excel was not suitable. You saved me so much work!
Thanks Bill Manassas, glad it helped!
Extremly helpful! You (and Mr. Excel) saved me. Thanks
Thanks Merete Rong Sloeveren, glad it helped!
This was a lifesaver. Thank you!
thanks so much! this has saved me millions of time and this is super useful! thanks a bunch! :)
Hi Erica Ng...you're welcome, glad you liked!
Thank you So Much Doug this is amazing
You are very welcome!
This is brilliant. I'm going to adapt it to use in analysis of survey data.
Thank you for the tutorial. Can you do the conversion with formula or without the pivot table?
Is there a way to do this using the latest version of excel? Alt+D+p is not working for the newer version
If you have the latest excel version, then the unpivot feature in Power Query is the way to go.
ua-cam.com/video/TVjswF_HKCE/v-deo.html
ua-cam.com/users/DougHExcelsearch?query=unpivot
@@DougHExcel thanks a lot man!! Really helpful stuff. Keep it up! 🍻
Thank you so much Doug....wish I had known this sooner.....this is extremely helpful!!
Thanks Arnav Singh Tanwar, glad it helped!
Does this work if previously inserted a calculated item or a calculated field ?
Sorry, don’t understand the comment/ question... but there's now an easier way to do this ua-cam.com/video/TVjswF_HKCE/v-deo.html
Thank you soooooo much! It is exactly what I look for.
Thanks Chuyi Feng, glad it helped!
Hi Doug, thanks so much for sharing the tip. However, I am unable to pivot the data into the one you showed (at 3:40). My fields become "key", "Q1", "Q2", "Q3" and "Q4", and how recognize them as "Column" and have "Column labels" on top of the four quarter fields? Without this, calculating "grand total" for each key is impossible. Thank you so much for your help.
sorry, I don't quite understand...what you describe above should be the intended outcome (i.e., having the fields become "key", "Q1", "Q2", "Q3" and "Q4)
Does that have sth. to do with "multiple consolidation ranges"? Thanks!
Probably...also you may want to consider the unpivot feature in Power Query. See ua-cam.com/users/dough517search?query=unpivot
I am trying it on mac. There is no pivot wizard option in the current version of excel, it seems. What can be used instead of pivot wizard?
don't know about that in the mac; this can be done better in with power query (PQ) as the un pivot feature ua-cam.com/video/TVjswF_HKCE/v-deo.html
PQ may be available in later version of Mac
Thank you so very much Doug! Take a bow...
Hi Abhay Dharwadkar, you're welcome!
Thank you, Doug. You saved me! =)
You are welcome!
Thank you! This is exactly what I needed! Unfortunately, I didn't have the option to bring up the pivot table wizard on my Mac Excel 2016. But, worked perfectly through Windows.
Thanks Moriah Kirkpatrick, glad it helped!
Hi Doug, for some reason I am not getting the grand total for rows that would allow me to double click. what could be causing this?
***** I do have it turned on. I thought it may have been because there were blank cells but that didn't solve the issue either.
Thank you so much! Thought I had to do it manually x.x
Thanks Syan L, glad it helped!
I have a very large data set, is there a way to do this in python or R??
or is there a conventional name for this process that I might look up a method to do this in one of those tools?
Power Query was meant to handle large datasets in the millions of records. For insight see ua-cam.com/users/dough517search?view_as=subscriber&query=power+query+unpivot
Fantastic Trick. I was going to write a VBA function to do this, but no more!
You save my day!
Thanks Agung Suwandaru, glad it helped!
hi doug - I cannot get the rows to display a grand total?
Click in the pivot table, and the in pivottable tools tab > Design click the Grand Totals icon to see your options
Good Job, This video really helped me
glad it helped! Thanks for the comment!
Excellent information. It helped me a lot to save my time
Thank you!
Glad you found this helpful. Thanks for the comment!
Excellent Find! thank you so much for sharing.
Glad you liked it!
Thanks a lot. this really helps me a lot...
Hi Doug,
Very nice trick. I want to do the same but the opposite way. From duplicate values table to make one with unique values and more columns. Any suggestions?
Thanks for your time
maybe something here ua-cam.com/users/DougHExcelsearch?query=power%20query%20csv
Thank you!!!....you're the best, this save a lot of my time trying to re-arrange my data sets for stats analysis through spss...
Thanks Calliz1000, glad it helped!
You're a genius! Thank you!
Great video!!
This is great, but I am using Excel 2016, and when I click on the grand total twice I get the data in columns, any idea how I can fix this?
Hi +Jose Antonio Perez Bolea, sorry I don't do consulting :-( ....but try to post on the mrexcel.com forum!
Extremely helpful! Save my day. tks
Thanks helenice mateta, glad it helped!
Great video, very helpful. Thanks!
Hi Jordan Wilcox...you're welcome, glad you liked!
Thanks so much. Saved me a bunch of time.
Thanks Danny Jenkins, glad it helped!
good video. but doesn't work on the latest version of excel on Mac.
Hi John Paul, thanks for the comment!
Thanks Doug, I used this and it saved me a ton of time. I am working on something now that has real column headers with data correctly underneath them, but the dates are across the top and there are 6 lines per record below each date for data labeled on the Rows. There are about 252 rows/ 42 records How can I manipulate this to keep the first 6 columns of correct column oriented data, but convert the date column headers as data rather than a column headers and keep the 252 rows from becoming 252 columns. Thanks
Power Query may help out. Here's my video playlist -> ua-cam.com/play/PL-n8f1cY_Qw9v3Y-jSrPy8-ctFw2yJjLZ.html
These specific videos may give some insight: (#11) Convert a Single Column Table to Multiple Column Table [2 Examples] and (#21) Use the Unpivot Feature in Power Query
how about for the large data with mutiple columns i tried and its not working
not all data are copied that's the error
maybe unpivot in power query see ua-cam.com/users/DougHExcelsearch?query=unpivot
Great, You're a HERO ♥
Hi Atif Dar, thanks for the kind words!
Hi Dough, how to handle multiple columns, you have used 2 columns in your example, what to do if i have 5 columns instead?
just concatenate the values of the five columns. You can also do this with microsoft Power Query using the unpivot feature...See this vid ua-cam.com/video/TVjswF_HKCE/v-deo.html
Great video really helpful with sorting data
Thanks David Thorp, glad it helped!
Ho can i auto transpose vertical data in horizontal in the group/breakage of 3 values
ex.Vertically 123456789 i want horizontally
123
456
789
like wise
please suggest !!!
sounds like you need a to reverse the the records and have the last record first like this:
789
456
123
and then concatenate it. Like in this video, but without the extra comma. ua-cam.com/video/W4sK4V9G0GY/v-deo.html
Great tip!
Hi James Baldwin, thanks for the comment!
Great video; could you please share the same for Excel 2010?
Doug, I tried same keyboard shortcuts with Excel 2010 & it worked like charm!!!
thanks ..really helped
Thanks it saved my day....
This is fantastic!
Hi John Skowronski, glad you liked it, thanks for commenting!
Really good advice Doug. But I'm having a problem. I did exactly that you show, but when I'm creating the pivot table, excel is returning a table only showing numbers "1" and not the data included in the original table. Do u have any advice? Pablo.
Hi Pablo A. Astudillo-Estévez, thanks for the comment!
Excellent tip about double clicking in the totals cell. Does anyone know how to do this for large data sets? I have 400,000 rows of data, with 100 values for each row. I’m looking for a way to create a column data set with 40,000,000 rows. This is beyond excel I think. Can this be done in access maybe? Would be grateful if there’s an affordable solution. Cheers.
Hi! How i can make the same in office 2016?
You should be able to do the same in the latest version.
wow -- you are my hero
Hi Ryan Seitz, thanks for the kind words!
Yes I was really useful !
Glad you think so!
Thanks a lot u saved my day
Hi Mohamed Dirar, glad you liked it, thanks for commenting!
Thanks a lot sir.. you made my my very easy due to this.
Hi sonu jangra, you're welcome!
You can also separate the unified text by formulas:
1st Column =LEFT(A1,FIND(":",A1)-1)
2ed Column=RIGHT(A1,LEN(A1)-FIND(":",A1))
please i want your help in my data, i have a column of data about 85000 and i want to transpose it into six column and any number of rows until the numbers is finished sequentially. thanks
Hi Ahmed Khedr, sorry 🙁....but try a post on the mrexcel.com forum!
thank you!
GENIUS!
Hi Assya Amarsy, thanks for the comment!
THANK YOU BRO
You're Welcome!
Thank you so much!!!!!
You're Welcome!
Thank you
You're welcome!
Great, only one like button over here......... Can this be done with formulas?
Hi Akash Tribhuvan, thanks for the comment!
how to the exact opposite of this?
Creating a pivot table would cover this
ua-cam.com/video/ZQxgYtl33xY/v-deo.html
Thanks !
THANKS
You're Welcome!
tell how to convert column data in row?
maybe some of these videos will give insight ua-cam.com/users/dough517search?query=reverse+pivot
Thanks
Welcome!
Thank yo so much sir.
You're Welcome!
excellent
Thanks
Great stuff! Thanks :-)
Glad you liked it!
Nice tip, but a bit outdated because now you can quickly unpivot this data using Power Query.
Yes, you are right. And PQ has this unpivot feature down cold ua-cam.com/video/XQS5V6_AGn8/v-deo.html
I have a dataset like this: docs.oklahomacounty.org/sheriff/SheriffSales/ (click through and select a date, doesn't matter wich one) - How do I keep the data on the left column and put those into columns and then put the data on the right side (the various property details) into columns under each column?
If the data is a the same number of rows for each data set the propose may be similar to what's done in this video ua-cam.com/video/OZbFPGwcyrE/v-deo.html
Sort of. I have one column with repeating data and a second column with unique data.
De mucha ayuda!
Gracias!
hello topic is another and u are telling another what is this?
Sorry, don’t understand the comment/ question...
THIS. YOU FUCKING ROCK. THANK YOU!!
Hi Ivan Otero, thanks for the comment!