How to Build a Pivot Table Report
On the left side of the sheet, a new empty pivot table has been created where the summary would be shown. On the right side, we can see the FIELD NAME which are the headers of the columns of our data set. FIELD NAME is to be dragged to empty boxes i.e Filters, Columns, Rows, and values to show their corresponding values in the Pivot Table.
Let’s drag the FIELD NAME into the boxes and see their effects individually.
1. Values Area in Excel
Values sum up all the entries in the FIELD NAME dragged in it. Here, as Sales are dragged here, our pivot table shows the sum of all the sales that took place.
We can add as many FIELD names as we require in Values. Individual sums would be shown then.
Dragging fields into values will give you the sum of values as a result.
If the entities in the column can’t be summed, it will give us the total count of the entries present in that column. Here as Country and Product do not contain numeric values, it returned the total count of each column.
Dragging Fileds into Values.
In the below image, you can find the Count of the Values.
The data in the pivot table gets grouped (Row-Wise) by the Field Names dragged to the Rows Area.
In this example, we have grouped the sales by the countries.
In the below image, the fields are dragged to Rows.
We can drag as many Fields as we require in this region.
2. Columns Area in Excel
The data in the pivot table gets grouped by(Column-Wise) by the Field Names dragged to Columns Area. As here, row-wise, our data is grouped by Countries and column-wise, it is grouped by Discount Band.
The Fields are dragged to Columns in the below image.
This area can accommodate many Fields.
3. Filter Area in Excel
The filter is an important feature in the pivot table. using which we can filter out the data based on the Field dragged into it. Here, we have filtered the total sales based on one particular product that is only that product is considered while calculating the sales.
You can also apply many features to the Product fields as shown below.
Below is the final output of the above steps.
In this way, using pivot tables, a summary of the data is achieved in the form of a matrix. There are many other tools and features of the Pivot Tables which can be explored.
How to Create a Pivot Table in Excel: A Step-by-Step Guide
Pivot tables are one of the most useful features in Excel. They are used to summarize or aggregate lots of data. The summarization of the data can be in the form of average, count, and other statistical methods. It converts lots of data into a few rows and columns of summary. They make it very easy to view the data from different perspectives and angles and are great for exploring data.
Table of Content
- What is a Pivot Table in Excel?
- What are pivot tables used for?
- How to Create a Pivot Table
- How to Build a Pivot Table Report
- How to Design and Improve Pivot Table
- How to Refresh a Pivot Table in Excel
- How to Copy a Pivot Table
- How to Delete a Pivot Table
- How to Sort a Pivot Table