Excel Skills for Data Analysts: Deep Dive Part 2

Pivot Tables and Data Summarization

When I started learning excel as a beginner, I haven’t heard of pivot tables. As I started deep diving into the world of Excel, I found out that pivot tables not only are useful for data analysts handling very large datasets, but also useful for someone who is trying to manage their finances at home or trying to make sense of the data they’ve collected from their small home business.

Pivot tables let you quickly summarize, analyze and visualize large datasets. They will help you answer questions like

• Total sales by the region?

• Average salary per department?

• What are your 5 top performing products?

So, what is a pivot table?

A pivot table is a dynamic summary of a dataset that helps analyze trends and relationships.

Common uses of pivot tables,

• Sum, count, or average data quickly.

• Group and filter data dynamically.

• Compare categories (e.g., Sales by Region).

How to create a pivot table?

I have the above dataset and I’m going to create a pivot table for it below.

• Select the dataset

• Insert > pivot table

• Choose new worksheet

• Click ok

• Drag and drop fields as needed

• Click ok

This pivot table has given us the total sales by region.

Pivot Table Features

As discussed above, pivot tables have certain features. Below we are going to look at the different features and how to use them to better analyze your data.

The first feature is summarizing your data. We already saw how to get the sum of a particular column using pivot tables. In addition to that we can get the average and the count. Let’s see below how we can get these different values.

• Click on the pivot table

• Go to value field settings

• Choose sum, count or average

Here when I select average from the field settings, I get the average of sales.

Second feature is filtering data. As you may have already seen, there’s an area called filters in the pivot table fields section. Let’s say I wanted to see the records of only the North region.

I’m going to drag region into the filters area and select only North.

Now I can see the records only where the region is North.

The third feature is grouping data. If your dataset has dates then you can group sales by month or year. I’ve added a new column to our dataset called date.

What we are trying to do here is to group our dataset according to date, it could be by year, month or week or even quarterly.

• First select the entire dataset and create a pivot table

• Drag date into the Rows area

• Drag sales into the values area

• Right click on any date on the pivot table and select group

• Select months/ years

Next up we are going to look at pivot charts, which is a method of data visualization. We are going to talk in detail on data visualization in the next section, but I thought I should introduce pivot charts now since we are already on the subject.

Basically, pivot charts help you visualize pivot table data.

It has,

• Column charts

• Line charts

• Pie charts

Here’s how you can insert a pivot chart,

• Click inside the pivot table

• Go to insert > pivot chart

• Select column chart

• Click ok

Here you have the total sales by the year and month.

That all for pivot tables and data summarization. The next article will be about data visualization.