How To Use Pivot Table In Microsoft Excel

Create a Pivot TableRefresh a Pivot Table | Filter a Pivot TableChange data source for pivot table | Remove column/row Grand TotalsChange pivot table name | Sort pivot table resultsChange the type of calculationTwo-dimensional Pivot table

A Pivot Table allows you to extract certain data from a much larger data set to summarize complex data. This post will guide you how to use pivot table in Microsoft excel from the below subjects:

  • Create a Pivot Table
  • Refresh a Pivot Table
  • Filter a Pivot Table
  • Change data source for pivot table
  • Remove column/row Grand Totals
  • Change pivot table name
  • Sort pivot table results
  • Change the type of calculation
  • Two-dimensional Pivot table

Create a Pivot Table

The below data set will be used in the following pivot table examples.

excel pivot table data set1

To create a new Pivot table, just follow the below steps:

#1 Click any single cell in which you want to insert pivot table (select B16 in this example).

#2 Click “INSERT” in the Ribbon tab, then clicking “Pivot Table” button in the “Tables” group.

excel pivot table data set1

#3 A “Create PivotTable” window will appear.

excel pivot table data set1

#4 Select a range “A1:E13” for the pivot table and click on the OK.

excel pivot table data set1

#5 A Pivot Table will appear and “Pivot Table Fields” Layout also will appear in the right of window.

excel pivot table data set1

excel pivot table data set 6

#6 Drag “Product” field to the Filters area, “Team” field to the Row area and “Cost” field to the Values area.

excel pivot table data set 6

Now the PivotTable report is generated as follows:

excel pivot table data set 8

Refresh a Pivot Table

If the data source make some changes, then you need to refresh your pivot table to take effect. To refresh pivot table, just following the below steps:

1# right-click on the pivot table, then click “refresh

excel pivot table data set 9

2# You will see that the pivot table refreshed. The sum of cost value have been changed from 410  to 470.

excel pivot table data set 9

Filter a Pivot Table

When creating pivot table, we need to drag fields to the Filters area, so we can filter this pivot table by this field that you dragged. In the above example, the “Product” field is dragged to the Filters area, so we can filter this pivot table by “Product” field. Just following the below steps:

#1 Click the filter drop-down button

excel pivot table data set 11

#2 select one item from the drop-down list. Such as: select “Ball pen”. Then click “OK” button.

excel pivot table data set 11

#3 let’s see the filter result.

excel pivot table data set 13

Change data source for pivot table

After created a PivotTable, you can change the range of its source data, such as, you can expand the source data to include more rows of data.

To change the data source for pivot table, just following the below steps:

#1 click any cell inside the pivot table, then the “PivotTable Tools” tab will show on the ribbon.

excel pivot table data set 14

#2 click “ANALYZE” Tab, then click “Change Data Source”.

excel pivot table data set 14

#3 the window of “Change PivotTable Data Source” will appear, then enter the range that you want to use.

excel pivot table data set 14

Remove column/row Grand Totals

If you want to remove grand totals for columns, just do the following:

#1 click “DESIGN” Tab under “PivotTable Tools” in Ribbon.

excel pivot table data set 17

#2 click “Grand Totals” button and then select “On for Rows Only”.

excel pivot table data set 17

#3 let’s see the result:

excel pivot table data set 17

Change pivot table name

By default, the first pivot table you create is named as “PivotTable1”, the second is “PivotTable2”… so on.  The below steps will guide you how to rename the existing pivot table, just do the following:

#1 Right click any cell inside the pivot table and then select “PivotTable Options

Change pivot table name 2

#2 enter into the pivot table name that you want to use in the “PivotTable Name” textbox. Then click “OK” button.

Change pivot table name 2

Sort pivot table results

To sort the pivot table result, just following the below steps:

#1 right click any cell inside the “sum of Cost” field in the pivot table.

#2 click “Sort”, then click “sort Largest to Smallest” or “sort Smallest to Largest” from the popup menu

Sort pivot table results1

#3 the results of “sum of Cost” will be sort.

Sort pivot table results1

Change the type of calculation

By default, Excel will summarizes value field by summing the items. To change the type of calculation that you want to use to summarize data from the selected field, just following the below steps:

#1 click any cell inside the “Sum of Cost” column, then click “Value Field Settings…

Change the type of calculation 1

#2 the window of “Value Field Settings” will appear.

Change the type of calculation 1

#3 choose one type of calculation you want to use under “Summarize Values By” Tab. Then click OK button.

Change the type of calculation 1

#4 let’s see the result:

Change the type of calculation 1

Two-dimensional Pivot table

Two-dimensional Pivot table can be created by dragging a field to the Rows area and Columns area.  The following steps will guide you how to create a two dimensional pivot table:

#1 insert a pivot table, then drag “Product” field to the Filters area, “Team” field to the Row area, “Quarter” field to the Columns area and “Cost” field to the Values area in the “PivotTable Fields” dashboard.

Two-dimensional Pivot table 1

#2 let’s see the result:

Two-dimensional Pivot table 1

 

 

Related Posts

How to Change Multiple Pivot Table Fields in Excel
change multiple pivot table fields6

This post will guide you how to change the format of all pivot table fields at once in Excel. How do I change multiple pivot table fields from the default sum of function to the count of function at once ...

How to Create a Chart with Two-level Axis labels in Excel
create chart with two level axis14

This post will guide you how to create a column chart with two-level Axis labels based on your original data in Excel. How do I create a Two-level X axis labels in your Chart with Pivot table in Excel 2013/2016. ...

How to Repeat Row Labels in Pivot Table
repeat row lables in pivot table8

This post will guide you how to repeat row labels in same row in your Pivot table in Excel. How do I repeat all row labels in your pivot table in Excel 2013/2016. How to repeat item labels in a ...

How to Hide Zero Values in Pivot Table in Excel
hide zero values in pivottable4

This post will guide you how to hide zero values in a pivot table in Excel. How do I remove rows with zero values in your pivot table. How to hide rows with zero values in pivot table in Excel ...

Sum Values in a Column based on Criteria in Another Column
sum values based another column12

This post will guide you how to sum values in a column based on criteria in another column in Excel. How do I sum values based on criteria in another column with Pivot table in Excel. How to sum all ...

Count Unique Values Using Pivot Table
count unique values pivot table14

This post will guide you how to count unique items in Excel Pivot Table. How do I use Pivot Table to count unique distinct values in Excel. How to get a distinct count in a pivot table in Excel. Assuming ...

Remove Grand Total in a PivotTable
remove grand total5

This post will guide you how to remove grand total from pivot table in excel 2013/2016. How do I remove column grand totals and row grand total in a pivot table in excel. How to hide grand total in an ...

Sidebar