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.
| Sales | Month | Salesman |
| 85,187.00 | Jan-17 | A |
| 118,429.00 | Feb-17 | A |
| 36,727.00 | Mar-17 | B |
| 92,243.00 | Apr-17 | A |
| 194,872.00 | May-17 | C |
| 92,670.00 | Jun-17 | A |
| 169,254.00 | Jul-17 | V |
| 54,851.00 | Aug-17 | A |
| 34,792.00 | Sep-17 | A |
| 184,456.00 | Oct-17 | A |
| 20,482.00 | Nov-17 | V |
| 57,871.00 | Dec-17 | A |
Pivot table steps
Goto>insert> click on pivot table(shortcut ALT+N+V+T)
- 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
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.
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.
