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

23 thoughts on “Video Lesson: Advanced Pivot Tables – Round 2

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

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

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

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

      • that’s weird… this is what I get:

        (using Firefox on a PC, also tried it in explorer with no luck and as stated before, I already saw several videos, I cannot install software on this computer so I guess I’ll have to watch this one home, I will let you know if it works there)
        thanks for the quick reply.

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

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

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

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

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

Post any comments, feedback or questions below!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s