Next ❯ ❮ Previous

What is Pivot Table

Pivot Table is a very useful and important feature of Excel. There is a very useful and powerful feature of Pivot Table in Excel. Pivot tables allow you to easily analyze important information from a large, detailed data set.

PivotTable is a powerful tool for calculating, summarizing and analyzing data that helps you see comparisons, patterns and trends in your data. Pivot Table is very easy to learn.

I have an Excel Files which is a kind of sales report. In which you have been given Brand, Year, Week, Month, Region, State, City, Name, Quantity and Amount.

Now we will create a Pivot Table of this data and summarize and analyze the data as per our requirement.

First of all we will select our data completely. If you want, you can also select All manually or by CTRL + A.

To add Pivot Table after selecting the data, you click on Insert > Pivot Table > From Table/Range. Or we can also use the shortcut keys ALT + N + V.

Now a Pivot table window will appear, in which you have to follow the following steps:

Select a table or range

Table/Range: Here will tell the range you have selected. If you want, you can also change the range according to your need.

Choose where you want the PivotTable to be placed

New worksheet: If you want to add PivotTable to new worksheet then use this option.
Existing worksheet: If you want to add PivotTable to any old worksheet then use this option. And you have to click on OK.

I want to create a Pivot Table in a new worksheet that's why I am selecting New Worksheet and clicking on OK.

Now you will get to see Pivot's view in Excel. Now we have to add fields in the Pivot Table field according to our report, after which we can prepare our report as per the need.

What is Pivot Table Field?

After creating a Pivot Table, you will see a list of fields. You can change the design of a Pivot Table by adding and arranging its fields. If you want to sort or filter the column of data shown in Pivot Table, then you can try Sort Data in Pivot Table and Filter Data in Pivot Table.

You can add the following types of fields to the Pivot Table

Filter: Filter fields are shown as filters in top-level reports at the top of the Pivot Table.
Column: The column field is shown as a column label at the top of the Pivot Table.
Row: The Row field on the left side of the Pivot Table is shown as Row Label.
Values: Value fields are shown in the Pivot Table as summarized numeric values.

Create Report in Pivot Table in Excel

Let's make some report in Pivot Table. So I want to create following type of report in Pivot Table.

Total Sold Quantity of Each Product
Total Sold Amount of Each Product
States wise sales report
Top 10 states wise sales report
City wise sales report
Top 10 city wise sales report
Top 5 states sales revenue
Top 5 city sales revenue

Report 1: Total Sold Quantity of Each Product

In this report, we will calculate the total sold quantity of each product. For which we will follow the steps given below

Row: Here we will add BRAND which will show us the list of sold product in Row Label.
Values: Here we will add the field of QTY, which will give the total of the quantity sold.

Report 2: Total Sold Amount of Each Product

In this report, we will calculate the total sold quantity of each product. For which we will follow the steps given below

Row: Here we will add BRAND which will show us the list of sold product in Row Label.
Values: Here we will add the field of AMT, which will get the total amount of the total product sold.

Report 3: States wise sales report

In this report, we will calculate state wise sales report.

Row: Here we will add State which will show us the list of sold product in Row Label.
Values: Here we will add the field of QTY, which will get the total sold product in each state.

Report 4: Top 10 states wise sales report

This report is exactly like report 3, In this report we will calculate top 10 state only, so we will copy report 3 above or make the same report in the same way..

We will bring the top 10 state by applying a filter on the state that is visible in the row label. For which you will need to follow the steps given below.

Click on the Filter icon on Row Label then Choose the Value Filter option from list.
Under Value Filter option, Choose the Top 10 (we will get this option at the bottom of list) and click on Ok.

Next ❯ ❮ Previous