Analysing Data in a Pivot Table - Excel Basics

Recall, we had calculated response rates for groups having multiple parameters (e.g.: marital - education; job - marital). We had accomplished this using Text functions & Statistical functions like AVERAGEIF(). Can we do the same - multivariate analysis - through PIVOT Tables? Yes. Let's explore!

That may have been a little too heavy! Let's summarize what we have observed:

• We can drag and drop fields into rows and columns to create a hierarchical/matrix-like pivot table. Hierarchical tables often help deep dive into data i.e. they help answer the following questions:
  • Among married people, which is the "best" job type?
  • Instead of just looking at Profits at a Product Category level, a salesperson may use a hierarchical table to analyse profits at a Product Sub-Category Level.
• The values in a PIVOT Table can be sorted. Applying conditional formatting to the sorted values helps:
  • Understand the trend in data
  • Isolate exceptions to the general trend (Exceptions are pockets of a particular colour surrounded by a different colour).

We should remember a statistical thumb rule: Before using any data to extrapolate, we should check whether we have >30 individual observations or not. Statistically, whenever we have >30 sample observations, we can extrapolate the same to the population as well.

Before moving ahead, why don't you try to use the PIVOT Tables to calculate the response rates by a combined marital-education group? You will now appreciate the power of PIVOT Tables. They help aggregate without having to use statistical and text functions. Moreover, the drag and drop feature makes it very easy to validate hypotheses on the fly!