Pivot Tables in Excel
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.
How to create a Pivot Table?
Follow the below steps to create a Pivot table:
- Select any cell inside the data-set of which pivot table has to be created.
- Go to the Insert tab.
- In the tables group, click on the Pivot table tool.
- A dialog box would open where we have to fill the two choices for the data to be analyzed and the place where we wish to have the pivot table. After filling in the options, click on OK.
- In the new sheet, we can see the pivot table and other options.
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.
Values sum up all the entries in the FIELD NAME dragged in it. Here, as Sales is 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.
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.
The data in the pivot table gets grouped (Row-Wise) by the Field Names dragged to Rows Area.
In this example, we have grouped the sales by the countries.
We can drag as many Fields as we require in this region.
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.
This area can accommodate many Fields.
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.
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.