How to Convert a Pivot Table to a Standard List
ฝัง
- เผยแพร่เมื่อ 8 พ.ค. 2011
- I created this video in response to a viewer who has a boss and a client who:
1) Do not know how to use Pivot Tables
2) Do not want to learn how to use Pivot Tables
She has been asked to create a summary of sales by Year a Quarter of the year - a report that is easy to create with a Pivot Table!
However, now she needs to convert the Pivot Table into a standard Excel List so that her boss and her client can Filter it and Subtotal the list.
Don't laugh - this is NOT an uncommon request. Watch as I demonstrate each step:
First to create the Pivot Table Report.
Then to Convert it into a List - including "filling in the blanks!
I invite you to visit my online shopping website - shop.thecompanyrocks.com - to view all of my videos and to take advantage of the many resources that I offer.
Danny Rocks
The Company Rocks
9 years later and this video can still save lives. Thanks, Danny!
Danny, your explanation of the process is PERFECT, and the only video I could find on TH-cam that explains how to successfully "unpivot" from a PivotTable. Thank you so much for this video!!
This is a great video - no fluff, no long introduction. Excellent tips & insight. Thank you
Danny, my new friend, you just saved me HOURS of time! I used to do this for my job every week...manually. Not any more. Thank you! Thank you!
Thanks! This is exactly what I needed. I made a beautiful pivot table for a coworker who doesn’t understand how to manipulate them. Now I can just output it as a static table so everyone is happy.
Even after 11 years, this is a great video for those who inherit old data. Danny all of your videos are done so well with clear, concise explanations. I am not sure when it was introduced in Excel and I am sure you are aware of this but I have 2016 and if you double-click on one of the totals row at the bottom of the pivot table it creates a new worksheet with the raw tabular data.
Your video is amazing. You saved me. I hope you are doing well, 10 years later. May you live long and prosper.
Thank you! I learned some things that have been baffling me for a long time with pivot tables. The first was the grouping function when you showed how to get the quarters set up! And the other one was filling in the blank cells with the Go to special function! I love these tricks! You have saved me loads of time!
An immersive video that I have seen after many years.
You did rock on explaining how to convert a Pivot Table to a Standard List. Thanks much!
Thank you for the "Go To Special" and Ctrl-Enter tip! I've been using either a macro or doing it manually! Now I can go impress my clients :)
My pleasure!
I am happy that you learned some new "Excel Tricks" that you can use! am sure that your clients will be impressed!
Danny Rocks
The Company Rocks
Thanks Danny. Learnt some good tips about the pivot table and (more exciting) I now know about the "Go To Special." I can use that in many places.
The Ctr+G + Special function is brilliant! Thank you!
Thanks, Danny. I've been struggling for hours on how to get my pivot tables to do more!
This Rocks! Danny, very smart. Thank you
Precisely what I wanted. Wasted lot of time since I did not what to search for though I knew what I wanted. Thanks for this concise video.
Thanks Danny, This really helped me. Keep up the Good work !
Spectacular! Thank you very much Danny
I am pleased that I could save you so much time!
Thanks for watching my Excel Tutorials here on TH-cam!
Danny Rocks
The Company Rocks
My pleasure. I am happy that you learned some new tips from my video tutorial
Thanks for adding your comments.
Danny Rocks
The Company Rocks
Thanks! I am pleased that you found my Excel tutorial helpful.
Re: Software - I use Camtasia Studio Software - published in the USA by TechSmith - to film, edit, and produce all of my videos. One of the options available is to "Highlight the Mouse, Clicks, etc.
It is a very good piece of software - flexible, affordable, and easy to use.
Thanks for adding your comments.
Danny Rocks
The Company Rocks
Thanks :)
I just found that in Excel, you can also set the pivot table to autofill the blank customers automatically.
The option is under "Report Layout", called "Repeat All Items Labels"
Danny Thanks
I have learnt the CTRL+ G stuff to remove the blank
Really that helped me a lot
I was doing the dragging way earlier
Thanks Danny! Your video helped me save a lot of time... :)
you da man! This came in clutch.
Thank You Very Much for helping People Stay Blessed
My pleasure. I am happy that you enjoyed my tutorial.
Thanks for adding your comment.
Danny Rocks
The Company Rocks
Extremely helpful tip. Thx very much!!
You rock Danny!
Cherry -
I am very pleased that you are now excited to get started with Excel Pivot Tables.
Thanks for adding your comment!
Danny Rocks
The Company Rocks
Very very useful! Thanks a lot for sharing this.❤️
Very helpfyl! Danny do Rocks!
Thank you Mr. Danny Rocks
Thanks Danny! very helpful!
Now I'm excited to use the Pivot Table! :)
Thanks for such detailed video. I have a suggestion. To avoid the missing values which were filled by selecting the empty cells, there is option in "PivotTable Tools->Design->Report Layout->Repeat All Item Labels". This will repeat all the labels and we will get the data in complete table form.
Great Pivot Table Tutorial!
Thanks! I learnt a lot from here!
HOLY SHIT! this just saved me like...a LOT of time. Thanks Danny!
SUPERB TIPS. WOW. THANK YOU
unbelievably amazing... thanks a lot.....
i love it.its so helpful
very helpful, thank you so much for sharing .
You rock Dan
Nicely and simply explained
Thank you for doing this.
Just saved me HOURS of work.
Thanks Danny -- informative video. I learned from it! Seeing you exploring the various Pivot Table options was useful.
A query: do you use any particular software to get the "yellow spotlight, red ripples" indicator around your mouse pointer?
Thank you very much. It is my pleasure.
Danny Rocks
The Company Rocks
Thanks Danny, so cool! :)
It helped me a lot..... Thank you very much..
super informative...thanks!
Muito bom, explicado com clareza.obrigado!
useful very useful thank you Danny
Excellent! Thank you.
This is very handy, thanks
great tut sir. love
Hello Faliq -
Yes, this feature was introduced in Excel 2010. So ... if you are using Excel 2007 or earlier, you do not have access to this new feature.
Nevertheless, frequently my viewers "inherit" reports and Excel workbooks where the hierarch of data is similar to a Pivot Table report. So, this is a valuable technique to add to your repertoire.
Thanks for adding your comments!
Danny Rocks
The Company Rocks
Thanks a lot sir
Looking for more videos
thanks a lot very useful to me.
Thanks for sharing
Awesome! Thank you
very helpful thank you Sir
this is really helpful.
Thank you so much.
very very helpful !
Excellent! Thanks
Thank you!
I find that the majority of Excel users do not know how to use PivotTables or PivotCharts and they refuse to learn. Usually, it's because they tried once and could not understand why they behaved so differently than a normal spreadsheet. Being able to do what you have shown in this video is invaluable to an advanced user that still needs to interface with the casual Excel user. Thanks!
You are a giant among men, sir.
Thank you very much
Thanks! Can you please put this workbook over here so that we can practice. Thanks
Excellent
Hi thanks for sharing. CAn you fill the blanks gor the headings into the real pivot table?
Amazing
Thanks..
Good👍 video
Sorry for the delay in responding. I just came across your comment.
Re: Your question. If I understand you, you simply want to copy a text list of video titles - is this correct?
If so, follow this process:
1) Highlight all of the text entries. 2) Copy your selection (use the Keyboard Shortcut Ctrl + C. 3) Open up the Excel Workbook to receive this information and click on a cell in the worksheet. 4) Paste what you copied (Ctrl + V is the Keyboard Shortcut).
Danny Rocks
Great
I don't visible option tab in my excel sheet.. how to enable that please tell me
Please also share the practice file
Is it possible to fill blank cell in pivot table itself to show customer name in each cell as above?
design-report layout-fill blanks
johmar ydia thankyou sir i will try that..
+Sajid Mirza welcome
thnks
4:30- how to convert a pivot table into a regular list
Hi, can you show me how to copy my videos list from. C:MYDOCUMEN/VIDEOS
LIST OF VIDEOS LET SAY; DANY , JON, JOSE , HOUUSE PARTY, KIDS .
INTO EXECEL
Just turn 64 on Dec 20
Can I send you mine data table to make it pivot table .
It's almost 1800 rows
Not relevant anymore.
Great! Thanks!