Let’s know more about Excel Pivot Table

All you need to know about Excel Pivot Table in order to help you in your daily basis

Jonathan Shinray Fang | LinkedIn

Hello, I’m Jonathan a dedicated data enthusiast, I’m learning while writing article that I find interesting in medium, and this is the let’s know more about series.

image source

Introduction

Wow, this may be my first Microsoft Excel hack I will ever write. As a dedicated data enthusiast, I find myself using this software so often. And most of the time, I keep pivoting some tables to see the information they hold and extracting their insights. Without further ado, I’ll present to you my first Microsoft Excel hack article, Let’s know more about Excel Pivot Table.

What is Microsoft Excel?

Microsoft Excel or Excel for short, is a spreadsheet software that developed by Microsoft to organize, format, and calculate data in a grid pattern of cells that divided by lettered columns and numbered rows.

illustration for excel

Excel is having a ton of features to manipulate your data, not mentioning a whole library of formulas to work with. We’ll discuss this later in the next article.

Author’s Notes:

For you guys that are familiar with coding, you might see that Formulas in Excel is similar to pseudocode. They both expressing logic and calculations. Excel Formulas also looks similar to Structured Query Language (SQL)

What is Excel Pivot Table?

We just know what is Excel, then what is Pivot Table? Pivot Table is a powerful feature that allows you to calculate, summarize, and analyze data in Excel.

screen capture of excel ribbon pivot table location

To access this feature, first we need block or highlights the cells then go to Insert ribbon -> click on PivotChart.

But why we use it? Top 3 reasons I’ve got as a Data Analyst:

1. It’s efficient, it can quickly summarize large amount of data without you know complex formulas (beginner friendly).
2. It’s flexibility, it can rearrange data to view it from different angle and have customizable row and column value, slicers, and filters.
3. It’s insightful, it can identify trends, patterns, and outliers in your data.

The Use Case for Microsoft Excel Pivot Table

Here is my example of Excel Pivot Table:

Imagine I have data customer savings, the data includes columns for identifier, customer,_id amount and timestamps. I want to analyze how much per customer savings, to make sure they are qualified to make a loan.

data used in this example

First, I highlight the entire dataset.

Then go to Insert > PivotChart and choose to place it in a new sheet.

After that a new sheet will pop up, I set up the Pivot Table by

• drag “identifier” to Filters
• drag “customer_id” to Axis
• drag “timestamps” to Legend (as I make it into Months)
• drag “amount” to Values (it is automatically change to sum)

As you can see the picture below a bar diagram just show up.

Finally, we can tweak and adjust it to our needs, like using slicer, change diagram or right-click any fields to change formulas (we can change the sum to average, count etc.). And we can start identifying and analyzing the data here, we can see that xc31090 is not having a good record in February, makes him a non-qualified to loan, since we need a good record above IDR 1 million per month for example.

Conclusion

By understanding this feature can help you to transform raw data into meaningful insights, helping when you need to make a decision. That’s all thank you, see you in my next article.