CP018: Dont be a Pivot Table Virgin!

Posted on August 21st, 2014 in Chandoo.org Podcast Sessions , Pivot Tables & Charts - 6 comments

In the 18th session of Chandoo.org podcast, lets loose your Pivot table virginity.

Note: This is a short format episode. Less time to listen, but just as much awesome.

CP018: Don't be a Pivot Table Virgin! - Introduction to Excel Pivot Tables - Chandoo.org Podcast

First some good news!

Our podcast crossed 100k downloads. Yay!!!

That is right. It took us 6 months & 17 episodes to reach this milestone.

Thanks for loving Chandoo.org podcast. You are awesome.

PS: If you have not already subscribed, get it here from iTunes (other ways to subscribe).

What is in this session?

Pivot tables are a very powerful & quick way to analyze data and get reports from Excel. But surprisingly, not many use them. Today, lets bust your pivot table virginity and understand the concepts like pivoting, values, labels, filters, groups and more.

In this podcast, you will learn,

  • Announcements
  • What is a Pivot Table?
  • Example of business data & reporting needs
  • Terms to understand
    • Labels
    • Values
    • Groups
    • Filters
      • Report filters
      • Slicers
      • Label & Value filters
  • Creating your first pivot table
  • Learning more about pivot tables

Go ahead and listen to the show

 

Links & Resources mentioned in this session:

Excel Pivot Tables – Introduction, Examples, Tutorials & Tips

Advanced Pivot Table concepts

Pivot Table uses & case studies

Books & Courses on Pivot Tables

 

Transcript of this session:

Download this podcast transcript [PDF].

You & Pivot tables… tell me all the racy stories

I lost my pivot table virginity in 2005. But I quickly regained it as I did not use them much for next 3 years. Then I lost it for good and I am glad for that. Now a days, I use pivot tables almost every week. And they give me quick and easy solutions to many analytical problems I face.

What about you? When did you loose your pivot table virginity? How do you use them every day? Please share your tips, stories & experiences in the comments area.

 

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

6 Responses to “CP018: Dont be a Pivot Table Virgin!”

  1. hbelal says:

    I don't remember when I used it for the first time but I start using it frequently since 2008. Now in my work, I have some empty PivotTables and Charts linked to input sheet ( to encode customers bank statements ), one input is done, some macro will work to copy original data and update all linked PivotTables and Charts which gives my team great advantage of analyzing customer information in most effective, efficient and acute way. Thanks Pivot Tables.

  2. Nirrobi says:

    Hi.
    I just started to use pivot table last year and it great ????
    Habelal, can you explain how you do it? Sound great...

  3. Nirrobi says:

    Hi.
    I just started to use pivot table last year and it great πŸ™‚
    Habelal, can you explain how you do it? Sound great...

  4. Sally says:

    I first learnt about pivot tables in 1998 on a training course.
    At that time I couldn't get my head around pivot tables, but in the same course I learnt about vlookup. I could see a use for vlookup in my work, and so used it. And have used it continuously since. Vlookup is my "go to" formula for absolutely everything.

    Finally, 12 months ago, we were upgraded from Office 97 to Office 2010. I was so excited - I had read all about sparklines & slicers and really really wanted to be able to use them. I kept hounding our IT lady until she upgraded me ahead of everyone else.

    Now that pivot tables are so much easier to use (and I'm probably more excel savy - ) I'm using them more and more.
    (our inventory system is DOS based, so I need to pull information together from several different reports to get the reports required - hence the heavy use of vlookup)

    I'd only just started using a computer back in 1998 .... didn't have them when I was at school .... although I did babysit for a family that had a computer - apparently it was one of the very first personal computers in New Zealand. The kids were allowed to occasionally play a pacman type game - very slow compared to the computer games kids have now!!!

  5. Bryan says:

    While you can use pivot tables to handle changing data filters, this isn't a really good example of why you would need to use pivot tables. If all you worry about is changing the totaling category from shoes to swimsuits, then a subtotal function should work just fine.

    Where pivot tables really shine is in their ability to react to changing data. Say for example, you need to compare the number of shoes sold by store, by month. The manual way would be:

    1) Get a list of all stores (say, by copying the "Stores" column and removing the duplicates), and make these the rows of your data table
    2) Take each month (probably using a formula) and make it the columns of your data table
    3) use a SUMIFS formula similar to =SUMIFS(category,"shoes",store,[relative rowreference],month,[relative column reference]). Note that you might have to do some trickery to get the "month" value, since your data actually contains the full date.

    In this case if you want to change from "shoes" to "swimsuits", then yeah, you will have to change the formulas. But instead of hard-coding the category, you could just refer to another cell. Then when you want to change categories, you just place the new value in the cell, and it's no big deal. There's really no need for a pivot table if all you are worried about is changing the category.

    But what about next month? You will have to extend your column formula and then Ctrl+R for all your summary formulas. What if you build a new store? You will have to add a new row and Ctrl+D your summary formulas. Any time the data changes you will have to change your table.

    If you instead created a pivot table with grouped dates as the columns (legend fields/series), store as the rows (axis fields/categories), sum of sales as the data (values), and category as the report filter, you will never need to update your report when data changes. If the next month's data feed has a new store, when you refresh the pivot table the new data will automatically be included. When you roll over to next year, you won't have to rejigger all of your formulas to account for a new year.

    This is the true value of the pivot table.

    Oh, and I had seen pivot tables in my old job, but I never learned how to use them until I got to the job I'm in now. I think I first used a pivot table "for real" about a year ago, so I would have been 26 or 27! πŸ™‚

  6. sumit saxena says:

    I lost my Pivot Table virginity at 25 πŸ™‚ , in my last company where i found it very useful in doing Sales Data Analysis and reporting to HO.

Leave a Reply