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 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 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.

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!

190 comments

Your email address will not be published. Required fields are marked *

  • 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.

    • 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.

  • 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.

  • 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

  • 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.

  • 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.

  • 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

  • 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?

  • 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.

  • 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

  • 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

  • Jon

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

    Matt

  • Jon

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

    Matt

  • Jon

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

    Matt

  • 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…

    • 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! 🙂

  • 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.

  • 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

  • Hello Jon from the temporary sunny United Kingdom.

    Many thanks for all of the free excellent Excel training.

    Best,
    jmc

  • 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!

  • 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

  • Hi, Jon!

    I have for many years wanted to learn about the Pivot Tables, but just did not think to be possible as so many keep on saying it is a pain.

    However, I was quite surprised how easy it was to follow your videos… and am no longer afraid of Pivot tables anymore.

    Many thanks!

  • Dear Sir,

    i started learnig from this excel files and PDF.
    Please sir reply my doubts and questions
    Thank you

  • Thanks so much for this videos as a beginner on Excel this is marvelous and easy to understand and apply to my daily work from now on is not going to be a challenge anymore please keep doing this videos!!!!

  • I just want to add to the many kudos here … this is by far one of the most helpful instructions i came across on learning pivot tables in Excel. Thank you! Just one question: I assume most if not all of the stuff you covered here would be the same on the Mac as well, correct?

  • Yes, this guy should be doing video lectures for everything we want to learn about. Or if they could all follow his approach we would be on our way to a better world. Now how would I bring back the pivot table field or chart on the right of my screen when I accidentally delete them? As I was following your steps in creating a pivot table, I went ahead of myself and deleted both the field and chart dialog boxes and realized I was unable to bring them back. I had to do the undo button several times to get back to the blank pivot table page. Thanks in advance…

  • Jon:

    Your videos are very helpful. I am between jobs, so I have pressed in during this time to become an advanced Excel user. I watched your videos, read Excel Bible and attended Excel classes at the local library.

    I appreciate the Excel file you provided, too.

    Regards,

    Karan Larabee

  • Jon, thank you for your clear and systematic narrative and demonstration in the videos. They are very helpful and have given me a good refresher as well as new tips. Bravo Jon. Looking forward to more.

  • THANK YOU JON…

    A BIG THUMBS UP FOR THE WORK YOU ARE DOING.

    ITS APPRECIABLE AND YOUR WORK IS OUTSTANDING.

    THE CONCEPT IS EASILY UNDERSTANDABLE.

    LUV YOU

    ANUSHA

  • Thank you for sharing great tutorial in Pivot table. I’m confident creating Pivot table at daily work. As this is amazing feature analying data efficiently and easy to explain data to colleague.
    One question is;
    Could also share how I can use the dashboard with slicer in powerpoint?

  • Hi Jon:

    I love learning the Pivot Tables. You make it so simple and explain it so well; but I don’t have the Pivot Pal or the XL Campus. Can you tell me how to download? I have tried multiple things, but can’t find it.

    Thanks for posting this on Youtube I love it and will be using it-a lot!

  • Sir,
    I have practiced as you directed and taught, very very helpful. Nobody is so kind hearted to help others without compensation,among few, you are one. So kind hearted God bless you
    Thanks a lot

  • Hi Jon,

    I have been `afraid` of pivot tables before seeing your videos, they are are great source of information and am learning s much. Thanks

  • Hi Jon,

    Thank you very much for the detailed explanations you give through your videos. My main concern is regarding the raw data.
    There are different parts of a business that need to be linked but also addressed separately:
    1.- Enquiry from customer
    2.- Contact suppliers
    3.- Purchase order with lead time
    4.- Sales confirmation
    5.- Shipping details
    6.- Goods arrive at destination port, clearing, etc
    7.- Payments terms to supplier
    8.- Payments terms for customers.
    9.- Marketing budget, materials and reports.
    10.- Sales data analysis from each market
    11.- Compare sales vs targets

    So should each of these be a sheet of the Workbook? It would be virtually impossible to create such long record, with so many fields for each product.
    Based on lead time from suppliers we can analyse their performance and see whether it is viable to maintain or change manufacturer.

    Please advice. I would not like to start without having a proper reply.

    Thank you.

    Kind Regards,

    Sajini

  • Wow Jon. Thank you very much for the leg up (help). I am exactly like the character Andy… trying it impress by sorting data. You explained everything at a good steady pace just perfect for an eager beginner. Great instruction. Hope you are well and long may you teach. Thank you again.

  • Hi Jon,

    Thank you so much for this easy to follow video! It has cleared up so many confusions I have had.

    I look forward to starting my online course! Amazing resource!

  • Jon,
    Thank you for this learning module in Excel. I will keep coming to it until I become an expert in Pivot Table Design.
    Thanks Again
    Ray

  • Hi Jon,

    I clicked around the various pages and didn’t find a link to download the data file you use in your videos.

    Rich

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter