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:

excel_1

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.

excel_2.PNG
Highlight your data set and select ‘Pivot Table’
excel_3.PNG
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.

excel_4

excel_5
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.

excel_6.PNG

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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s