Pivot Table
Pivot table is one of the most significant and useful feature in excel, it enables you to quickly summarize, analyze and transform the data in an efficient way.
Pivot tables are very useful in following scenarios:
- Handling and querying large amount of data
- Aggregation of numeric data by using custom field and formula.
- Several views of data could be produced by using different categories and sub categories.
- Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you.
- Transposing data – moving rows to columns or columns to rows (or “pivoting”) to see different summaries of the source data.
- Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data to enable you to focus on the information that you want, without having to write any formulas.
- Presenting concise, attractive, and annotated online or printed reports.
Below is an example of data set that belongs to a retail chain and we will create a pivot table from this data.
Create a PivotTable from worksheet data
- Select the data
- Go to insert->Pivot Table and press OK, the pivot will be created in a new sheet, if you want to create the pivot table in the existing sheet then choose Existing Worksheet from the radio Button before clicking OK.
| Pivot Table Report | Description |
|---|---|
| Values | Use to display aggregation and summary of numeric values like sum, count etc. |
| Row Labels | Row label is used to display data at row label. If you want to display the revenue of products by city, place city field in row label, and city will be displayed at left side. |
| Column Labels | Column label is used to display data at column label at the top of report. If you want to display the revenue of products by city, place city field in column label, and city will be displayed at top. |
| Report Filter | Use to filter the entire report based on the selected item in the report filter. |
- An empty Pivot Table report is added on the specified worksheet along with the PivotTable Field List from which you can select the fields that you would like to add to create a layout and customize the PivotTable report.
- To place any field in the default area of the layout section, select the check box next to the field name in the field section. By default, nonnumeric fields are added to the Row Labels area, numeric fields are added to the Values area, while date/time hierarchies are added to the Column Labels area.
You can also drag and drop field name to the desired area.
IN the figure below I have dragged Country field to the row label and Amount field to value field as I want to see the sum of amount by country.
Add filter to a Pivot Table
To add a filter to the pivot table ,drag a field name to the Report Filter area.
Suppose I want to see sum of the amount by country only for Automobile products.
Drag Product Category field to the Report Filter area as Automobile is a category.
Select Automobile by clicking the small drop down to filter the data for Automobile category.
Two Dimensional Pivot Table
Lets understand what is a two dimensional pivot table with the example of a scenario-Suppose we want to see amount by country and product category.
To see that drag the Product category field to the column Label area as shown below.
We can see a two dimensional pivot table below where you could easily analyse product wise revenue in each country.
The right most Grand Total column in the pivot table is the sum of amount for the corresponding country.
Sorting Pivot Table
Suppose we want to see the sum of amount by coutry sorted from High to Low(descending).
Select any cell in the grand total column->Right Click-> and select Sort->Sort Largest to Smallest to sort the pivot table frm high to low.
You can see in the figure below the pivot table after sorting.
Click on any value of any field that you want to sort and repeat the same process.
Change Pivot Table Summary Calculation
By default the summary calculation for numeric field in excel is Sum, so whenever you put any numeric field to Values area, excel will do the sum of numeric values and present you the result.
But we can always change the calculation that we want to set for the fields we put in there by changing the Value Field settings.
Click on the field in the Value area->Value Field Settings and then change the calculation for that field.
If there are multiple fields in the Value area, you could individually click on the Value field settings for each of them and change their calculation.
You will see a number of calculation options to choose from.
I have selected Count and pressed OK to get the count of the amounts.
As you could see below, the sum has been changed to count of amount.
