Excel: Mastering Pivot Tables

Ah, Microsoft Excel—the bicycle for the mind. Excel is an amazing tool for working with smaller datasets. As you’re probably already aware, there is a myriad of functions and tools available to you in excel, but today we’re going to take a look at pivot tables—what they’re for, how they work, and what to expect from a pivot table exercise in an interview.
What is a pivot table?
Pivot table exercises are one of the most common excel proficiency tests given in an interview—together we’ll walk through how to build one, and use it to answer a question you might see in an interview scenario.
A pivot table allows you to summarize a larger data set by calculating the average, sum, etc. of various measures. Think of it as a tool you can use to aggregate like terms: You wanna find total sales by region? Use a pivot table and BOOM. Done. But how does it really work? Let’s start with a sample data set:
The sample data set above contains sales by client, city, and state. It is common in an interview to be given a data set similar to the one above and asked to calculate various totals using pivot tables. Let’s go over how to create a pivot table when given a data set:
Highlight your data set, go to the insert tab, and click PivotTable. A new sheet will pop up with a PivotTable Field List on the right-hand side.

Highlight your data set and select ‘Pivot Table’
Pivot Table Field List

At this point, you can drag the fields you want into the areas you want to create a variety of aggregations.
For example, let’s say your hiring manager wants you to give them sales by city—all you have to do is drag city to column or row labels, and drag sales to values.

Your result set should look like this

You’ll notice that the graphic says “sum of sales”. The default level of aggregation is “sum”, however you can change it by clicking on the icon and selecting “value field settings”. From there you’ll be able to change the level of aggregation to whatever you need.
Pivot Tables are relatively easy to handle once you get the hang of them. If you need more practice—google “sample datasets”, drop one in Excel and play around with it using Pivot Tables.
We hope you found this article useful–if you enjoy our work, or you have any questions please leave a comment below!

Leave a Reply