How to Implement Pivot Table in Excel ?

  Excel Interview Q&A

The best thing about using a pivot table is that even though you don’t know anything in Excel, you can still do a great job with it with an awful lot of sense.

What is a pivot table? In excel

A pivot table is a summary of your data, packaged in a chart that lets you report and track trends based on your information. Pivot tables are particularly useful if you have long rows or columns that hold values ​​that you need to compare and easily compare to each other.

In other words, pivot tables extract meaning from the seemingly endless rumble of numbers on your screen. And more specifically, it lets your data be grouped together in different ways so that you can more easily draw helpful conclusions.

The “pivot” part of a pivot table stems from the fact that you can rotate (or pivot) the data in the table to view it from a different point of view. To be clear, when you create an axis, you are not adding, subtracting or otherwise changing your data from it. Instead, you are simply reorganizing the data so that you can reveal useful information from it.

If you have a data as shown below:

Creating-a-Pivot-Table-in-Excel-Dataset

This is a sales data consisting of ~ 1000 rows.

It contains sales data by region, retailer and customer.

Now your boss wants to know some things from this data:

  • What was the total sales in the South region in 2016?
  • What are the top five retailers by sales?
  • How did Home Depot’s performance compare to other retailers in the South?

You can go ahead and use Excel functions to answer these questions, but what if suddenly your boss comes up with a list of five more questions.

You have to go back to the data and create new formulas every time that changes.

This is where Excel Pivet Tables really come in handy.

Within seconds, a pivot table will answer all these questions (as you will learn below).

But the real advantage is that it can accommodate your data-driven boss by answering your questions immediately.

It is very simple, you can also take a few minutes and show your boss how to do it.

Hopefully, now you have an idea of ​​why Pivot Tables are so terrible. Let’s go ahead and create a pivot table using the data set (shown above).

How to Inserting a Pivot Table in Excel

Here are the steps to create a pivot table

  • Click anywhere in the dataset.
  • Go to Insert -> Tables -> Pivot Table.

Creating-a-Pivot-Table-in-Excel-Insert

  • In the Create Pivot Table dialog box, the default options work fine in most cases. Here are some things to check:
  • Table / Limit: Filled by default based on your data set. If there are no empty rows / columns in your data, Excel will automatically identify the correct range. You can change it manually if needed.
  • If you want to create a pivot table in a specific location, under the option ‘Select where you want to place the pivotable report’, specify the location. Ells, a new worksheet has been created with a pivot table.
  • Click OK.
  • Now a new worksheet is created with the Pivot Table in it.

While the pivot table is created, no data is seen in it. All pivots mentioned are table names and a single row instruction on the left, and pivot table fields on the right.

Creating-a-Pivot-Table-in-Excel-Blank-Pivot-Table-Worksheet

Now before we analyze the data using this pivot table, let us understand what are the nuts and bolts that make up the Excel pivot table.

Excel Pivot Table Nuts & Bolts

To use the spindle table efficiently, it is important to know the components that make the spindle table.

In this section, you will learn about:

  • Axis cache
  • Value area
  • Field of rows
  • Column area
  • Filter area

Creating a Pivot Table in Excel Filter Pivot Cache

As soon as you create a pivot table using data, something happens in the backend. Excel takes a snapshot of the data and stores it in its memory. This snapshot is called Pivot Cache.

When you create different views using pivot tables, Excel does not go back to the data source, instead it uses the pivot cache to quickly analyze the data and give you a summary / result.

The reason the spindle cache is generated is to optimize the functioning of the spindle table. Even when you have thousands of rows, an axis table is super fast at summarizing data. You can drag and drop items into rows / columns / values ​​/ filter boxes and it will instantly update the results.

Note: A downside to the axis cache is that it increases the size of your workbook. Since this is a replica of the source data, when you create a pivot table, a copy of that data is stored in the pivot cache.

Creating a Pivot Table in Excel Filter Value area

The value field is the one that holds the count / value.

Based on the data set shown at the beginning of the tutorial, if you want to quickly calculate the total sales by region in each month, you can get a pivot table as described below (how we will make it later in the tutorial ).

The area highlighted in orange is the value region.

Creating-a-Pivot-Table-in-Excel-Values-Area

In this example, it has total sales in each month for the four regions.

Creating a Pivot Table in Excel Filter Field of rows

Creates the top rows area to the left of the value field.

In the example below, the Rows area includes the areas (highlighted in red):

Creating-a-Pivot-Table-in-Excel-Rows-Area

Creating a Pivot Table in Excel Filter Column area

The column at the top of the value field forms the column area.

In the example below, the column field includes months (explained in red):

Creating-a-Pivot-Table-in-Excel-Columns-Area

Creating a Pivot Table in Excel Filter area

The filter field is an optional filter that you can use to drill down into the data set.

For example, if you want to see sales for multiline retailers only, you can select that option from the drop down (highlighted in the image below), and the pivot table will only update with the data for multiline retailers.

Creating-a-Pivot-Table-in-Excel-Filters-Area

Analysis of data using pivot table

Now, try to answer the questions using the pivot table we have created.

Click here to download sample data and go together.

To analyze data using pivot tables, you need to decide how you want to see the data summary in the final result. For example, you want all areas to the left and total sales right next to it. Once you have this clarity in mind, you can simply drag and drop the corresponding fields into the pivot table.

In the Pivot Tab Fields section, you have fields and fields (as described below):

Creating a Pivot Table in Excel Fields and Area

Fields are created based on the backend data used for the axis table. The field section is where you place the field, and where a field goes, your data is updated in the pivot table accordingly.

This is a simple drag and drop mechanism, where you can simply drag an area and place it in one of the four areas. As soon as you do this, it will appear in the pivot table in the worksheet.

LEAVE A COMMENT