4 Easy Steps to Create Pivot Table

You are sitting with your manager for the Weekly Sprint Review Meeting. Your manager wants to know how many tasks are completed and how many are yet to be completed. You have the data ready in your Excel sheet. You show them to your manager. However, he is not impressed and asks you to just the numbers and not the big data pool you are presently showing him. What will you do?

Fortunately, now we have simple yet versatile data analysis tools to break down the complex data structure. Pivot table is a tool that helps you quickly answer many important business questions.

One of the reasons we create Pivot Tables is to pass important information that is easy to see and understand. The “pivot” part of a pivot table roots to the fact that you can rotate (or pivot) the data in the table to view it from a different perspective. Put, you’re reorganizing the data so you can reveal useful information.

Let us now check out how this pivot table works.

Step 1. Enter your data into a range of rows and columns.

Every pivot table in Excel starts with a basic Excel table, where all your data is captured. To create this table, navigate to MS Excel and then simply enter your values into sets of rows and columns, as shown below:

Tabular data

Here, I have a list of people, their education level, and their location. With a pivot table, I could find several pieces of information. I could find out the locations of people with a Master’s degree, for instance.

Step 2. Insert your pivot table.

Inserting your pivot table is the easiest part. You’ll want to:

Highlight your data.

Go to Insert in the top menu and click on Pivot Table

Select the Pivot Table option

A dialogue box will come up, confirming the selected data set and giving you the option to import data from an external source (ignore this for now). It will also ask you where you want to place your pivot table. I recommend using a new worksheet.

You typically won’t have to edit the options unless you want to change your selected table and change the location of your pivot table.

Once you’ve double-checked everything, click OK.

You will then get an empty result like this:

Empty result in new sheet

This is where it gets a little confusing, and where I used to stop as a beginner because I was so thrown off. We’ll be editing the pivot table fields next so that a table is rendered.

Step 3. Edit your pivot table fields.

You now have the “skeleton” of your pivot table, and it’s time to flesh it out. After you click OK, you will see a pane for you to edit your pivot table fields.

Pivot Table fields

This can be a bit confusing to look at if this is your first time.

In this pane, you can take any of your existing table fields (for my example, it would be First Name, Last Name, Education, and Location), and turn them into one of four fields:

Filter

This turns your chosen field into a filter at the top, by which you can segment data. For instance, below, I’ve chosen to filter my pivot table by Education. It works just like a normal filter or data splicer.

Column

This turns your chosen field into vertical columns in your pivot table. For instance, in the example below, I’ve made the columns as Location.

Row

This turns your chosen field into horizontal rows in your pivot table. For instance, here’s what it looks like when the Location is set to be the rows.

Value

This turns your chosen field into the values that populate the table, giving you data to summarize or analyze.

Values can be averaged, summed, counted, and more. For instance, in the below example, the values are a count of the field Name, telling me which people across which educational levels stay in which location.

Step 4: Analyze your pivot table.

Once you have your pivot table, it’s time to answer the question you posed for yourself at the beginning. What information were you trying to learn by manipulating the data?

With the example, I wanted to know how many people are married or single across educational levels.

I therefore made the columns Education (degree), the rows Location, and the values Name (I also could’ve used Surname).

Values can be summed, averaged, or otherwise calculated if they’re numbers, but the Name field is text. The table automatically set it to Count, which meant it counted the number of first names matching each category. It resulted in the below table:

Here, we’ve learned that one person is from Britain and is a PhD.; A Bachelor and a Master from China; One Master from France; Two Bachelor and one PhD from India; One Diploma from Indonesia; One Bachelor and one PhD from Japan; One Master and one PhD from Singapore; Two Diplomas from Spain; One Bachelor, one Diploma and one Master from the USA.

We can take any data of any complex nature and turn the data in valuable information with this analytic tool — Pivot Table.