Intro to Pivot Tables and Dashboards Video Series 1 of 3
120

Introduction to Pivot Tables and Dashboards [Video 1 of 3]

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

Learn to Create a Pivot Table and Pivot Chart in Excel

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 cleanup your source data before creating a Pivot Table.

Pivot Table Source Data Checklist - Excel Campus.pdf (262.2 KB)

Use the following file to follow along with the video and create your own Pivot Table and Pivot Chart.

Intro To Pivot Tables Part 1.xlsx (76.5 KB)

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 and his Boss - Excel Campus

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.

Turn Data into a Dashboard with Pivot Tables

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.

Intro To Pivot Tables Part 1.xlsx (76.5 KB)

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!

PivotPal Logo Pivot Table Add-in for Excel

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

Please leave a comment below with any questions.  Thanks again!

 

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 120 comments
Susan - July 10, 2017

HI Jon, I have really enjoyed your first video and found it very helpful. I have managed to create a table without much trouble but when I try to save the table it loses all it’s functionality when reopened and the source data is not saving even though I have ticked this option in the Options box. Obviously I am missing something. Are you able to advise?
Thanks Susan

Reply
Margarita - July 5, 2017

Hi Jon! Thank you for your tutorials. They are great! Very thorough and in an easy to understand format for a beginner like me. What would you recommend for companies that still use Excel 2007?! To upgrade to 2014? 😉

My company uses 2007 and I actually struggled to recreate the pivot table based on the company’s data I got. First, I was learning the hard way, on my own, that the data needs to be in the specific format, otherwise, charts are not going to work. I saw that later in your other email about format. After I got it in the right format, I still could not get the amounts to be summarized. Instead, the revenue or amount field was counting the rows or something rather. It was frustrating. But, I need to practice more. And, we need to upgrade our excel at work to a more current year!

Thanks again for all your help!

Reply
John - July 5, 2017

Hello Jon from the temporary sunny United Kingdom.

Many thanks for all of the free excellent Excel training.

Best,
jmc

Reply
Bernadette Williams - July 2, 2017

Jon, I can’t thank you enough. I am working real hard to learn as much as I can with regards to V-lookup, Pivot Tables, Macros, interactive Dashboards, Formulas, PowerPivot, and Power Query. I have barely scratched the surface on all these tools and functions and for years have only used Excel mostly like a Word document since many of the organizations I’ve worked for were using their own proprietary systems to produce time consuming work…however, there were always those instances that Excel had to be used more in depth to some degree. I always found myself calling over an Excel Power User to help me set up a worksheet for data tracking, or help to write out a formula, or instruct me on this or that – and always felt inefficient when it came to using Excel. I am in between jobs now and making a hard effort to improve my skills but there is so much to learn. As soon as I can, I will definitely take your courses because you have the ability to teach Excel in a way that it is fun and interesting to listen to and now I want to learn more. I am optimistic that by learning to navigate Excel beyond intermediate level that I will land a better job commensurate with my experience.

Thank you so much Jon for sharing your given talent.

BW
Tampa, FL

Reply
Sharon - May 1, 2017

Great videos, but I need something that I can refer to as I attempt to build pivot charts and prepare dashboards at work. What is the best book you can recommend that I could use as a daily tool to apply a lot of what you taught in these videos. (Focus on pivot tables and dashboards), Thank you, you are doing an awesome job.

Reply
Alain - April 6, 2017

Thank you so much Jon, I really appreciate your videos, it is so helpful to be able to get the numbers to talk. Students, workers and investors can’t be efficient without it and your videos are so well done and clear, it is a pleasure to spend time learning how to use Excel spreadsheets. I am 65 but as an investor, I am glad I can use these new skills, it is very helpful. Thanks again.

Learning, the best hobby in life…

Reply
    Jon Acampora - April 10, 2017

    Hi Alain,
    Thank you so much for the nice feedback. I’m happy to hear you are learning more about Excel, and enjoying the videos. I really appreciate your support!

    Have a great day! 🙂

    Reply
Matt - April 5, 2017

Jon

Love your videos, Is the data set available so I can use in my class. I teach High School students.

Matt

Reply
Matt - April 5, 2017

Jon

Love your videos, Is the data set available so I can use in my class. I teach High School students.

Matt

Reply
Matt - April 5, 2017

Jon

Love your videos, Is the data set available so I can use in my class. I teach High School students.

Matt

Reply
Frank Roehler - April 4, 2017

Jon,
I am interested in working better with Excel. I am way behind in reading your emails and watching the video. I have saved them for the day I could study more.

I like the way your video explains the options.
Thanks. Frank

Reply
ROSA - March 12, 2017

Hi Jon,

Thank your for your tutorial, they are so well explained and easy to follow. Keep the hard work!

Reply
varun singhal - March 3, 2017

Hi Jon,
Your teaching style is really amazing and simple. your video cleared all my doubts. Can you share me the link of the excel data so that i can do some more practice.
Waiting for your positive reply.

Varun Singhal

Reply
Jeanette Bates - February 26, 2017

Jon –

You have saved my life with your tutorial. I’m looking for a job and I needed to upgrade my skills. Pivot tables was very confusing until I watched your video.

Thank you again for your great assistance.

Reply
K Brock - February 20, 2017

It really does look professional. I’ve always want to learn about pivot tables. Thanks for sharing and making this possible. Is there anywhere I can find a data source to practice with?

Reply
S Jahan - February 19, 2017

Thank you very much Jon for sharing your knowledge in Pivot Table. I really appreciate your video teaching, data for pivot table, and the checklist prior to start pivot table work in excel. Most important to mention, I like checklist and data for pivot table to practice. The data in excel is very important to practice. For me, it is like a symbolic ground to practice baseball. All I need to do, practice on this data to become expert on this amazing powerful tool. Thank you

Reply
Mary Chambers - February 19, 2017

I am impressed.

Thank you for simplifying the learning process for pivot tables.

I have been treading learning how to use pivot tables. Now I am encouraged and excited.

Reply
Saif - February 17, 2017

Afternoon Jon,

Your style and method of transferring knowledge was great and simplified. I enjoyed the video presentation of the topic.

Thank you.

Kind regards, Saif.

Reply
Najwan - February 15, 2017

Thank you so much for this great tutorial on Pivot Tables,It look scary but you made us simple

Reply
Jackie Lundy - February 5, 2017

Jon,

Nice Work!!

Thank you again for developing and delivering this great tutorial. I am confident I will enjoy applying data and creating my very own pivot tables with greater ease.

Jackie

Reply
    Jon Acampora - February 15, 2017

    Thank you Jackie! I’m happy to hear you are learning more about Excel and applying this knowledge to your work. Awesome! 🙂

    Reply
Emma Smith - January 16, 2017

Thanks Jon for sharing your expertise. Going forward creating pivot tables are going to be very helpful in my career. I wish I had known about Excel Campus before.

Reply
    Jon Acampora - January 20, 2017

    Thank you Emma! I appreciate the nice feedback, and happy to hear you are using pivot tables. They are awesome! 🙂

    Reply
hesham - January 11, 2017

thank you mr JON ACAMPORA

Reply
Pam - December 27, 2016

Jon

Thank you so much for this great tutorial on Pivot Tables. I have seen them used and always wanted to use them myself but I was very intimidated by them. I love Excel and what it offers for reporting functions and this has just made it better. Now that I know the correct way to set up the data source and how to use it, this will be a common functionality for all my reporting needs.

Reply
    Jon Acampora - December 27, 2016

    Awesome! Thanks so much Pam. I’m happy to hear you are less intimidated. I think pivot tables get a bad rap as being difficult and scary, but they can really help us get our job done faster.

    Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x