Written by:

Written by:

Thomas Szigeti

An Initial discussion

about Pivot Tables

This is going to be a multi part post as pivot tables are a very large and powerful tools of the trade and there is a lot we can talk about it.

Grab a coffee and let’s get to it!

-Why the hell would I need a Pivot table?
-What is a pivot table anyway?

These were the questions in my head when back in 2013 as a fresh Real Time Analyst I had to churn out daily reports in a Pivot table. 

I remember, we had a two page step by step instructions printed and laminated, so we all knew how to do the report. 
Frankly, it was a scary introduction to Pivot Tables. 

See if we can answer my questions

-Why the hell would I need a pivot table?

Pivot tables are a different kind of beasts. They designed to handle large datasets, from a few hundred to thousands of rows of data. If you have your raw data in order with a few clicks (or drag and drops) you can create one or multidimensional Pivot Tables. 
So the short answer to my question: -It can handle large amount of data with ease.

-What is a Pivot Table anyway?

The best way I ever been explained is:
Tables that we “humans” understand is something like a normal school schedule.
Like the one below.

Human school schedule

Where the days are across and the hours are down. 

O.K. this is cool. After all WE are humans, and this is how we understand. 
Not Excel Pivot though. laughing

The Raw data for the Pivot Table needs to be a bit like a proper database. 
One row have to contain information about one specific cell.

A bit like this:

School schedule pivot raw format

If you check the Pivot kind of table, it is very much like a simple database. 
Out of this we can make a pivot table, and the three fields: Time, Subject, Day is at our fingertips to do whatever we like to put out of this database.
We can do this with the Pivot Table Fields Panel.

Pivot Table Fields Panel



If you look on the left you can see that on the top part the Pivot table provides you with the available fields.








On the bottom part
There are four different areas that you can drag your fields.

  • Filters -Anything that you drag here will become a filter option
  • Columns -Anything here will create a column
  • Rows -Fields dragged here will become rows
  • Values -The values here will become the output of what found based on the other fields.
    (Note: this is Sum now, but it can be Count, Average, etc…)

Based on the Field setting above we will end up with a simple Pivot table like this:
(Note: I had to replace the subjects with Values as the Value field mostly accepts numerical values. (Text can be put here but that needs a bit of formula magic* -We will do it later on.))

I think this gentle introduction is enough as Part 1.

If you want to understand Pivot Tables a lot better, then go to Part 2

Come and visit us.

Come and visit excelangel on Facebook to find and join the growing community of true office workers.

Oh, I almost forgot.
Drop us a like while you there…

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]