In the previous session, you calculated response rates for groups defined by multiple parameters, such as marital status and education or job and marital status. This was done using text functions along with statistical functions like AVERAGEIF().
Did you know you can perform the same type of multivariate analysis more easily using Pivot Tables? Pivot Tables allow you to summarise and analyse multiple variables at once with minimal effort. Let’s explore how!
Key Takeaways: Pivot Tables
Let’s summarise the main points about what you’ve learned so far:
-
You can create a hierarchical or matrix-like Pivot Table by dragging and dropping fields into rows and columns.
These hierarchical tables help you explore data in depth. For example:
- Among married individuals, which job type performs best?
- Instead of analysing profits at the Product Category level, you can explore profits at the Product Subcategory level.
-
Pivot Table values can be sorted. Applying conditional formatting to these values helps:
- Identify trends in the data
- Spot exceptions or outliers easily (highlighted as distinct colors within the dataset)
- A useful statistical guideline: Before extrapolating data, ensure that you have at least 30 observations. With more than 30 samples, results can often be generalised to the larger population.
Before moving forward, try using Pivot Tables to calculate response rates for a combined marital-education group. This exercise will help you see how Pivot Tables can aggregate data quickly, without relying on statistical or text functions. The drag-and-drop interface also makes it simple to test hypotheses on the fly!