Video Lesson: Advanced Pivot Tables – Round 2

Advanced Pivot Tables Round 2

This lesson picks up right where the last Pivot Table lesson left off.

In this tutorial, you will learn:

  • How to use multiple levels of data within row or column labels.
  • Expand / collapse all functionality through right-click (Expand / collapse entire field)
  • Advanced filtering options including: right-click, row label, column label, and report filters. Includes multiple simultaneous filters, and using search/filter functionality within each data level.
  • ‘Defer Layout Update’ option which allows for changing fields without pivot table automatically updating.
  • Fields Section / Areas Section Stacked vs. Other Pivot Field List Formats.

Make sure to download the Advanced Pivot Tables – Round 2 workbook to follow along with the lesson!

If you’re following along with the Lesson Guide, next up is Auto-Filter and Advanced Data Filtering

This entry was posted in Workbook/Reference Lessons and tagged , , , . Bookmark the permalink.

28 Responses to Video Lesson: Advanced Pivot Tables – Round 2

  1. Laszlo Christine says:

    Thank you so much for these didactic and interesting lessons – Best and most complete site for learning

  2. yitzbbfkbfvk says:

    thank you
    love you

  3. DeKarla says:

    I love these, it’s been years since I’ve used these functions and the videos have refreshed all the things I forgot, taught me new things and cleared up any issues that I had! Just one thing, is there any way you can make the volume louder? I have a hard time hearing the videos.

  4. Kevin says:

    Hi
    These have been very helpful, thanks.
    I was hoping you could provide some help. When I filter date data using normal filter options in Excel, I can quickly filter by date as it breaks it down by year, month and date. So for example i could instantly select all of 2017, or de-select all of 2016, wit ha single click. When I take the same data into a pivot table and set ‘date’ as a filter, this option for fast selection of data disappears, and I need to select every individual date separately. Do you know if there is a way that I could select/de-select entire months/years within a pivot table filter?
    Thanks in advance!

  5. Anonymous says:

    Clear your throat

  6. Umang C Juthani says:

    This is the best training i had. Thanks. May I donate some money for your efforts. You guys rock

  7. Shashidhar says:

    Hello Ben,

    Your videos have been very helpful. If possible, please create a lesson on Pivot Charts.

  8. Anonymous says:

    A lot of thanks for you

  9. milliem282 says:

    Is there a workbook on test style so that i can do the exercises from scratch so I can practice what i have learned from Ben’s great lessons?
    Thank you!
    Millie

  10. Anonymous says:

    This has been really useful for me…thanks very much

  11. Hareesh Kumar Kurapati says:

    Thank you so much

  12. Mirela says:

    Another great lesson! Many thanks!

  13. Peter R. says:

    Hi, I’m looking into utilizing these new found skills to take mutual fund performance for periods such as 1 month, 3 month, YTD, 1Y, 3Y, 5Y (my column headers). I have the rows of my data set including both funds and indices. In a column i’ve only filled in for funds, i’ve listed a ticker for a benchmark that is in the data set. Is there a way to utilize calculated rows and perhaps an INDEX(MATCH( or something similar to return the under/over performance per period against the benchmark?

  14. Anonymous says:

    Thank you sooooooo much for these awesome lessons!!!!!

  15. Rameeshh says:

    Hi Ben,

    I can’t thank you enough for the great help you did for me.

    The raw data for sales only has date wise and monthly fields and so we can only know the monthly picture.

    My question is, with out having to disturb the existing data, is there a way either in formulas or in pivot that we can view the performance weekly. Could you please let me know?

    Regards,
    Rameeshh

  16. ash9star says:

    Thanks! Found this very helpful. Wondering if you have lessons on creating charts out of pivot table reports.

  17. Chase B says:

    Awesome lesson! I am just wondering if you can add additional total columns to the pivot table such a s a % column?

  18. Anonymous says:

    this video is not working properly, I already saw other videos which were working fine, so its not about the PC I’m working on, any ideas?

  19. roy.fox@utexas.edu says:

    Hey Ben, Great video and I love the site. I have one question- I work with a lot of trade data that sometimes doesn’t have a value inserted in some of the cells. When I go to make a pivot table, I always get the count of each month rather than the sum because of the blank values. Is there anyway to either: A) fill all blank cells with 0’s or B) make the sum function the default in the pivot table settings? I use Excel 2010

    Thanks a bunch

    • [Responded separately via email, but pasting here in case anyone has a similar issue]:

      I’ve been at work so I didn’t get to look until now. From poking around online, it appears that it is the data type of the cells in question (found here http://www.mrexcel.com/forum/excel-questions/483-pivot-table-default-sum-count.html). This quote is specifically relevant:

      “Sum is the default if your chosen field contains nothing but numbers. If *any* of the data isn’t a number, even if it’s a blank cell, then the default will come up as Count.”

      I noticed in addition to blanks you sometimes have a hyphen ‘-‘ in some of the cells instead of a 0. You will likely want to do a find and replace to replace any hyphens in your numbers to be 0 instead.

      Hope that helps,

      Ben

  20. James E says:

    Hi Ben,

    I don’t have a “search box” in my “Employee” menu… Referring to your Morgan example. Is there a way to add this?

    Videos are awesome! Thanks! I will be sure to tell others!!

    James

  21. Lee says:

    Hi Ben– Love your videos. I have a pivot table question for you. Every time I try to create a pivot table with the data that I want, it automatically alphabetizes the information in my column. Is there a way to turn this off? I tried right clicking on the column and checking “sort in data source order,” but nothing changes. What I am doing wrong?

    Thank you!
    Lee

  22. RAVI says:

    HI BEN I LIKE TO KNOW FIRST WHAT IS YOUR QUALIFICATION AND SECOND THING WHICH IS WHAT IS YOUR LEARNING TECHNIQUE WHICH I CAN APPLY IN MY LEARNING TECHNIQUE AND TELL ME WHAT THING WHICH INSPIRE YOU THE MOST OF TIME?

    I REALLY WANNA KNOW I HAVE NEVER SEEN THIS MUCH DEPTH AND CLEARITY OF KNOWLDGE.

    GREAT SIR FOR YOUR ATTITUDE.
    OM SHANTI

  23. Beth says:

    Thank you, just saved me about 4 hours of work.

    Can you use the pivot data for merging into Word? I have a download of online test data on students and need to present certificates showing the tests that a student has passed, may be one test or 5 tests, and need to reflect that on the certificate to the student. Thanks.

    Love the format you use – best site ever for learning. Doesn’t assume we are all beginners and need to cover every innuendo of basics. THANKS!

  24. Alejandro Rodrigues says:

    Very, very useful, thank you Sir!!! Can’t wait for the nex in.depth analysis on pivot tables!!!

  25. Mency says:

    With knowledge comes power… thanks heaps for sharing these…

Comments are closed.