CP018: Dont be a Pivot Table Virgin!

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

1. hbelal says:

   August 21, 2014 at 1:17 pm

   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.

   Reply
2. Nirrobi says:

   August 21, 2014 at 4:58 pm

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

   Reply
3. Nirrobi says:

   August 21, 2014 at 4:58 pm

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

   Reply
4. Sally says:

   August 21, 2014 at 10:38 pm

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

   Reply
5. Bryan says:

   August 25, 2014 at 1:04 pm

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

   Reply
6. sumit saxena says:

   August 26, 2014 at 12:48 pm

   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.

   Reply

Leave a Reply