Pivot Table Basics: Exploring our data and answering basic questions (2/3)

Okay, so in the last post we created a Pivot Table and got a weird looking sheet with a Field List pane on our sheet. The listed fields are nothing but the column headers in our dataset. To carry out an analysis on our data, a Pivot Table needs the list of fields so that it can slice and dice the data according to values in those fields.

Now this slicing and dicing of our data can happen in multiple ways and a Pivot Table gives us four options to do it by simple drag and drop operations:

  1. Report Filter: This kind of serves as a master filter of our data as we would have in a dashboard. A field dragged into this section will be created as a drop down for filtering data. We can then filter data corresponding to our selected values in this drop down.
  2. Column Labels: As the name suggests, a field dragged into this section will form column labels with unique values from this field.
  3. Row Labels: Similar to column labels, a field dragged into this section will form rows with unique values from this field.
    Crab Tip: We suggest that for better interpretability, you select a field with lesser unique values as column label and more unique values as row label.
  4. Σ values: This aggregates values in our dataset which lie at the intersection of row and column labels for a selected filter by performing different operations (which are user specified) like sum, count, average, etc.

So basically, we’ve laid out the ground rules for a Pivot Table. Essentially what our Pivot Table does is that it aggregates values by the filters/labels specified by us. The best part about this is that we can continuously change our selected values and look at different analyses without writing even a single formula. All of this will be clearer with the exercises that follow.

Let’s say you’re an analyst who has been given this dataset and you have to quickly examine this dataset in as many ways as possible and get a good idea about our data. Let’s try answering a few questions about our dataset:

  1. How many students does each school have?
  2. What is the number of male and female students in school MS?
  3. What is the average grade of male and female students in both the schools?
  4. What is the sum of absent-days for male and female students in each school (add absent days for each student in each school)?

Just follow the video below to get answers to these questions using a Pivot Table. Do remember to switch on the subtitles.

While these were some simple operations that we introduced to give you a basic introduction to Pivot Tables, we recommend that you explore the dataset further and play around with the Pivot Table to see what else you can do.

We hope you found this post useful. In the next post we’re going to look at some advanced features of Pivot Tables which will help you to become a pro at Pivot Tables. Stay tuned in!

Have a question? just leave a comment and we will answer A.S.A.P