Introduction To Pivot Table

You are here:
< Back

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.

data to create pivot table

Create a PivotTable from worksheet data

  1. Select the data
  2. 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.

creating a pivot table from excel data

Pivot Table ReportDescription
ValuesUse to display aggregation and summary of numeric values like sum, count etc.
Row LabelsRow 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 LabelsColumn 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 FilterUse to filter the entire report based on the selected item in the report filter.
  1. 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.

pivot table is created from data table

  1. 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.

a simple example of pivot table

doing sum in a pivot table

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.

adding report filter to a pivot table

Select Automobile by clicking the small drop down to filter the data for Automobile  category.

filtering data in a pivot table

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.

two dimensional pivot table

We can see a two dimensional  pivot table below where you could easily analyse product wise revenue in each country.

adding column label to pivot table

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.

sorting a pivot table

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.

changing calculation field in pivot table

You will see a number of calculation options to choose from.

changing calculation in pivot table from sum to count

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.

value field in pivot table is changed to count

 

 

 

 

 

Random Posts