Bottom line: In this video series you will learn how to use Pivot Tables to create meaningful reports and interactive charts and dashboards.
Skill level: Beginner
Video #1 – Introduction to Pivot Tables
In this first video we are going to learn how to setup your source data, then create a Pivot Table and Pivot Chart. I also show you how to reformat the chart to upgrade the look from the default settings.
Best viewed in Full Screen HD.
Note: If you are familiar with Pivot Tables then this video might be too basic for you. I would still love for you to watch the video, but don't want to waste your time. In the next two videos we will be putting together the dashboard. There is also a shorter video below where I give a Pivot Chart a makeover…
Downloads
Use this checklist to clean up your source data before creating a Pivot Table.
Use the following file to follow along with the video and create your own Pivot Table and Pivot Chart.
Pivot Tables are Amazing!
A few days ago I shared a story about how I got to teach my Dad how to use Pivot Tables. As you know he is really excited about Pivot Tables. They were easier to learn than he expected, and he is saving a ton of time when creating reports and summarizing his data.
So I want to share this same training with you!
Create Dashboards with Pivot Tables and Charts
In this video series we are going to follow my friend Andy on the journey to learn Pivot Tables.
Andy has been tasked by his boss to create some charts and a dashboard for an upcoming presentation. He really wants to impress his boss, but doesn't have time to learn complex formulas or macros. What he really needs it Pivot Tables and Pivot Charts.
As you will see, Pivot Tables allow you to quickly summarize and analyze your data. I am going to show you how you can discover trends and learn a lot about your data with just a few clicks.
Bonus Video: Pivot Chart Makeover in Excel 2010
In the video above I explained how to reformat the Pivot Chart. In that video I was using Excel 2013. I recreated the video using Excel 2010 because the menus are a bit different and I want to make sure you could follow along if you are using Excel 2007 or 2010.
Why the Chart Makeover?
When presenting your data visually on a chart you want the reader to be able to easily understand the story you are trying to tell. To do this you want to get rid of all the extra elements that do not contribute to the story.
In this case we removed the axis labels, legend, vertical lines, and field buttons. This stuff can be referred to as “chart junk” because it tends to clutter the chart.
The default settings of the Pivot Charts typically contain a lot of chart junk. These extras elements can be distracting for the reader, and take away from your story.
The type of chart you create will determine what elements you want to display. There are entire books written on the art and science of data visualization, and it is definitely a good topic to study.
PivotPal is Here!
I show a few of PivotPal's features in the 11:28 mark in the video above.
PivotPal is now available!
Click here to learn more about PivotPal!
Thank You!
I want to say a big THANK YOU if you took the time to complete the pivot tables survey! The response was overwhelming and I am still going through all the results. I will share more about that with you in the coming weeks.
Additional Resources
- In part 2 of the series we learn how to use the different calculation types to investigate our source data.
- In part 3 of the series we create the dashboard and add slicers to make it interactive.
- Checkout my article on how to structure your source data for pivot tables.
- Here is an in-depth article on how pivot tables work.
Please leave a comment below with any questions. Thanks again!
Jon, found it.. Belay last request…
Fantastic video! I had no idea what a pivot table was before watching your video. This is so cool and seems like an easier way to view data. I really enjoyed your tutorial, you made is so easy to follow. Looking forward to the next video!
Thank you for closing the gap in my knowledge on the details for formatting charts. Well done.
Thank you very much, your videos are educative. It has always been my desire to broaden my knowledge of excel and just landed my self in the best place to learn new skills. However, following your videos, was unable to use pivot pal on excel 2013. Looking forward to receiving new videos
I have watched the series of Pivot Table 1 to 3, and each time you used PivotPal, or XL Campas, I felt frustrated, because my Excel does not have those functions or menu.
I am trying to learn how to use the Excel I have, and how to make the learning effective in my way. So whenever you showed some functions or menu dialog of PivotPal or XL Campas, I couldn’t concentrate on your lecture, because I have no idea of how or why to use those that are not in my Excel.
Would you kindly provide me with the modified versions of the 1-3 series of Pivot table videos without using PivotPal or XL Campas? This is necessary for me (and I think for everyone who has just standard Excel workbook) to fully understand and to be able to fully utilize what we learn about Pivot table in the actual settings.
Thank you.
Intro To Pivot Tables Part 1.xlsx is not working. It is corupted
Check your PC, I have downloaded it and it’s working
hi jon
i want to say a big thank you to all your tutorials,i learnt Microsoft power Bi and each time they talk about pivot table and power query i felt confuse but having gone through your pivot table lessons i understood yours so easily.
i was initially frustrated on the topic,but you made it so simple to understand.
i will check back on power query too.
thanks ,thanks again,i do appreciate. and have always saved your emails that you send.
let me perfect myself and i will check back on pivotpal, i love what i saw there on the explanatory video.
Dear Sir
Thank you for your tutorials, I had hands on knowledge but i am improving watching your videos
EXCELLENT Excel resource! Clean, clear, and easy to follow…highly engaging.
This is really helpful. thank you so much .
it’s no waste of time every time i watched your Video Jon.
Really sir , ur videos are awesome, any one can understand
Thanks
Hi Jon. Could you kindly assist me on how to download the sample excel file? Thanks
thanks
this was fantastic!!!!!!!!
Thank you & it is very help full
Excellent!
Thanks! Very helpful.
Am excited to have found you…am looking forward to a great adventure
How can I download the excel sheet you used in your tutorial?
Thanks soo much for your help.
Your teachings are super helpful, easy to follow and practice.
Thanks
Just went in to purchase Pivot Pal. Unfortunately, it’s not Mac compatible.
I would like to see how to set up a pivot table across multiple workbooks (1 sheet in each workbook). For me, it would allow me to have smaller worksheets/workbooks by keeping the data for each year in a separate worksheet/workbook. They would all be structured the same – same number of columns, with the same headings, etc. Is it possible?
Thank you!
very cool, thank you, teacher
Can you share more about KPIs?
It been a very long time since these videos were put up. I was wondering if you are still in the Excel teaching business. I’ve learned more watching these free videos then I did from any other source. I’d love to buy your products if they are still available.
Thank you for the videos, i learned a lot ☺️☺️
Your Videos are just life-saving!!
Thank You, For the understanding shared to me.
When using the slicers, is there a way to set them up so that, for example, the information for all sales people is shown?
I tried several times but cannot download the excel data to follow.
Thanks
I will review over the weekend
Thanks for this awesome tutorial. but im finding it difficult to download the practice sheet. could you please display it here again or send the download vlink to my email.
Much thanks!
Great tutorials
I just had this link sent to me, encouraging to download the file in order to practice, however, the link is not an active link for downloading the exercises.
Can you send them to me by chance? I’ve encountered this on other areas of your website and blogs as well, but figured it was old posts. This was literally just sent to me via email.
Thanks!
Crystal
Hello Crystal, you need to provide your name and email address under the Downloads. Once done, the like to download the file will appear. Thanks! 🙂
Hi!
The course looks great but I have no idea how to download the excel file. I have got some emails now with some links but I cant find the file anywhere. PLZ help!
Thanks
Raf
Thanks a lot
Very helpful
Thank you
Too much ads. Can’t find the file for practice. Or is there ?
I’m thrilled with the way it is presented in deep detail!
So grateful for real.
I got the files, and I’m doing it so easily after watching your video.