How to apply Pivot table in excel

Excel Pivot tables

Below sales data in sheet 1. We want to know how much each salesman did for the overall year 2017.

Before applying the pivot table it suggested converting the entire data into table format(select entire data and do CTRL+T) so that pivot table data will update automatically.

SalesMonthSalesman
     85,187.00Jan-17A
   118,429.00Feb-17A
     36,727.00Mar-17B
     92,243.00Apr-17A
   194,872.00May-17C
     92,670.00Jun-17A
   169,254.00Jul-17V
     54,851.00Aug-17A
     34,792.00Sep-17A
   184,456.00Oct-17A
     20,482.00Nov-17  V
     57,871.00Dec-17  A
Sample Table format

Pivot table steps

Goto>insert> click on pivot table(shortcut ALT+N+V+T)

Pivot dialogue box new worksheet option
  • You will get dialogue box select new worksheet.
  • The new sheet will be added to the workbook before sheet1.

Application of pivot table

 Drag  salesmen to row labels

Pivot table result

 Drag month to row labels

 Drag sales to ΣValues

 Now the pivot table is ready

Refresh or Refresh All

This option is used when your source data is not in table format. If source data is in table pivot automatically get updated.

When you add new data then need to refresh the data.

To get refresh Option-click anywhere in pivot then you will see a new tab with Pivot table tools. Then click on options you will get the refresh option.

Pivot table refresh option

You can do a refresh to a particular one column info or refresh all to get update all information on that table.

Slicer

You Can insert Slicer for better data presentation.

To get Slicer Option-click anywhere in pivot then you will see a new tab with Pivot table tools. Then click on slicer you will get the insert slicer option.

How to select slicer option
Usage of slicer option to know the sales of each salesman

 

 

Click here to get the sample copy