Excel PivotTables Made Easy - And Why Things Go Wrong! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
James
Wow! I love this! Let me explain why I learned so much today. I can't be the only one in such a group.
I used Lotus spreadsheets years ago, used a couple others, and now use Excel. I also have used several database programs over the years--sparingly, but understand the concepts well.
Here was the problem (until just now):
I hear the phrase Pivot Table, and it doesn't accurately describe what I NOW understand is going on. I thought, -They want you to make a table so that you can quickly turn columns into rows and rows into columns. Well, I almost never need to do that.- Then the quick blurb I get is, -No, Pivot Tables help you summarize your tables easier.- Well, I never had trouble quickly summing what columns I wanted.
BUT! If someone had said, -Pivot tables let you quickly and easily do the equivalent of SQL on your data in a table and it's even much easier than SQL . . . - That's what I needed to hear.
To summarize the misunderstanding I've had for years:
The term -pivot table- has a definition that I didn't know. It's barely related to the definition of the word -pivot-.
The shorthand people would use to talk me into a pivot table was always, -It helps you summarize your data more easily.-
Thanks so much for this video!!! I will now start using pivot tables for some of my many pet projects.
reply
Wow! I love this! Let me explain why I learned so much today. I can't be the only one in such a group.
I used Lotus spreadsheets years ago, used a couple others, and now use Excel. I also have used several database programs over the years--sparingly, but understand the concepts well.
Here was the problem (until just now):
I hear the phrase Pivot Table, and it doesn't accurately describe what I NOW understand is going on. I thought, -They want you to make a table so that you can quickly turn columns into rows and rows into columns. Well, I almost never need to do that.- Then the quick blurb I get is, -No, Pivot Tables help you summarize your tables easier.- Well, I never had trouble quickly summing what columns I wanted.
BUT! If someone had said, -Pivot tables let you quickly and easily do the equivalent of SQL on your data in a table and it's even much easier than SQL . . . - That's what I needed to hear.
To summarize the misunderstanding I've had for years:
The term -pivot table- has a definition that I didn't know. It's barely related to the definition of the word -pivot-.
The shorthand people would use to talk me into a pivot table was always, -It helps you summarize your data more easily.-
Thanks so much for this video!!! I will now start using pivot tables for some of my many pet projects.
reply
Ian
Many thanks, Mynda - this is brilliant. Coincidentally, I discovered only yesterday that you can format your PT in tabular form, so that it looks more like a database. It fills the blank cells on the left with row labels, thereby 'un-nesting' rows where you've got more than one data item in the rows. This is the main thing that always annoyed me about PTs . I absolutely get how quick they are at summarising your data, but it was their look and format that annoyed me. But now that I know this (and have seen you do it so easily) I think I'm converted! This will make it much easier for me to summarise data by account master (which has three dimensions, equating to three data items in the rows) for upload to SAP systems.
reply
Many thanks, Mynda - this is brilliant. Coincidentally, I discovered only yesterday that you can format your PT in tabular form, so that it looks more like a database. It fills the blank cells on the left with row labels, thereby 'un-nesting' rows where you've got more than one data item in the rows. This is the main thing that always annoyed me about PTs . I absolutely get how quick they are at summarising your data, but it was their look and format that annoyed me. But now that I know this (and have seen you do it so easily) I think I'm converted! This will make it much easier for me to summarise data by account master (which has three dimensions, equating to three data items in the rows) for upload to SAP systems.
reply
Megamundus
Dear Mynda thank you for all you are sharing with us. I personally have been greatly benefited from your training material and i wish you are always happy and healthy.
Would you please help me with this:
i have an accounting Trial Balance in an excel sheet table with, say, 5 columns for account code, account description, debit, credit and balance.
The code is in the form of xx.xx.xx.xxxxxx and can be 2, 5, 8 or 14 characters long, including the dots, depending on the ranking of each account.
Is it possible to bring in a pivot table selected accounts , each one in a row, with all data, including the description in a separate column ???
Be well !!!
reply
Dear Mynda thank you for all you are sharing with us. I personally have been greatly benefited from your training material and i wish you are always happy and healthy.
Would you please help me with this:
i have an accounting Trial Balance in an excel sheet table with, say, 5 columns for account code, account description, debit, credit and balance.
The code is in the form of xx.xx.xx.xxxxxx and can be 2, 5, 8 or 14 characters long, including the dots, depending on the ranking of each account.
Is it possible to bring in a pivot table selected accounts , each one in a row, with all data, including the description in a separate column ???
Be well !!!
reply
Cathy
Thank you for your video. I have one question. I must update my sales report weekly and use pivot table to help to calculate the weekly performance of our store. When I update and add new data to the table sheet every Monday, refresh pivot table source, the result comes out as unexpectedly wrong. I must insert a new pivot table then can make a correct result. I can not figure out the problem. But I watched your video you told me whenever you added new data, just refresh is Ok. I am thinking if this refresh function is only for editing excel file? Once you close it , then you must re-create a new pivot table again?
reply
Thank you for your video. I have one question. I must update my sales report weekly and use pivot table to help to calculate the weekly performance of our store. When I update and add new data to the table sheet every Monday, refresh pivot table source, the result comes out as unexpectedly wrong. I must insert a new pivot table then can make a correct result. I can not figure out the problem. But I watched your video you told me whenever you added new data, just refresh is Ok. I am thinking if this refresh function is only for editing excel file? Once you close it , then you must re-create a new pivot table again?
reply
Jim
I have a bunch of data which is similar and I thought I could use a pivot table to display it but I don't think this is the correct way. I have the following data: room air temp, supply air temp, and return air temp. The data was taken at different times but has a time stamp associated with it. I can put it all in one table with the headers of day, time, temp, and item. How can I add these three items to one table showing them all as separate line graphs with time as the x axis and temp as the y axis? In other words I would like excel to sort them and put them in chronological order.
reply
I have a bunch of data which is similar and I thought I could use a pivot table to display it but I don't think this is the correct way. I have the following data: room air temp, supply air temp, and return air temp. The data was taken at different times but has a time stamp associated with it. I can put it all in one table with the headers of day, time, temp, and item. How can I add these three items to one table showing them all as separate line graphs with time as the x axis and temp as the y axis? In other words I would like excel to sort them and put them in chronological order.
reply
aphastus
That-s a great tip. I myself always been doing tables like the last example, althought I have never had to make extensive data analysis out of them. The thing is that I applied for a job as data analysis technician in my HR department and tried to extract data for my work cneter and I got the same table as the example. I-m trying to make a dashboard with some KPIs to bring along my interview and I-m stuck with a lot of duplicated data that the only thing I can think to do with is tidy up manually -
reply
That-s a great tip. I myself always been doing tables like the last example, althought I have never had to make extensive data analysis out of them. The thing is that I applied for a job as data analysis technician in my HR department and tried to extract data for my work cneter and I got the same table as the example. I-m trying to make a dashboard with some KPIs to bring along my interview and I-m stuck with a lot of duplicated data that the only thing I can think to do with is tidy up manually -
reply
Woody
Your video makes the concept of pivot tables very clear using very clear data. However, when I try to create my own pivot tables based on the real world data I'm working with, they don't make any sense at all. I'm used to working with complicated formulas and the pivot table, to me, just adds confusion. Perhaps I need to see many more examples of real world use before I can see the value in pivot tables in my world.
reply
Your video makes the concept of pivot tables very clear using very clear data. However, when I try to create my own pivot tables based on the real world data I'm working with, they don't make any sense at all. I'm used to working with complicated formulas and the pivot table, to me, just adds confusion. Perhaps I need to see many more examples of real world use before I can see the value in pivot tables in my world.
reply
Marty
Thanks for getting back to me. I played around and found out how to change column titles (a report with -Sum of Costs- instead of just -Costs- as a column title didn't look good).-
I've been using Excel for 25 years (don't ask how old I am (-)) and never used a pivot table but playing around with them now seems like a great tool in the Excel arsenal.-
Thanks again.-
Marty
reply
Thanks for getting back to me. I played around and found out how to change column titles (a report with -Sum of Costs- instead of just -Costs- as a column title didn't look good).-
I've been using Excel for 25 years (don't ask how old I am (-)) and never used a pivot table but playing around with them now seems like a great tool in the Excel arsenal.-
Thanks again.-
Marty
reply
So
Thanks so much for this simple and effective video. It's the best video that I can find to start with PT without going too much into detail. I'll practice all the features that you have included a few times during this week, in order to well assimilate all the mentionned functions. The download Excel practice file is an excellent extra.
reply
Thanks so much for this simple and effective video. It's the best video that I can find to start with PT without going too much into detail. I'll practice all the features that you have included a few times during this week, in order to well assimilate all the mentionned functions. The download Excel practice file is an excellent extra.
reply
Paul
Hi Mynda,-
I am using a pivot table that I have added to the data model so I can count some distinct values.-
If I try to use the getpivotdata function and try to reference a date cell I get the #ref! error.-
I have made sure my data model range is in the same format, am I missing something?-
Thank you
reply
Hi Mynda,-
I am using a pivot table that I have added to the data model so I can count some distinct values.-
If I try to use the getpivotdata function and try to reference a date cell I get the #ref! error.-
I have made sure my data model range is in the same format, am I missing something?-
Thank you
reply
Add a review, comment
Other channel videos
