PivotTable is an essential Excel tool used to explore and analyse huge data set in order to generate an interactive and user friendly summary reports.
Its ability to automatically group, sort and filter different subset of data, and ensure data are summarised by categories and sub categories are its cool features that stand it out among excel tools.
More so, pivotTable allows changes to be made to structure of summary table by dragging and dropping the source table column as well switch rows and columns in PivotTable Fields.
It makes provision to drill down to see the detail behind every operation by simply collapse or expand the level.
Organise your Source Data
This is very essential before creating a PivotTable, source data need to be organise into rows and columns, and then convert the cells range into Excel table to make our data more dynamic.
To do this for the purpose of recap,
- Select any cell in the data set,
- Go to INSERT MENU and click on Table in table group or Press CTRL+T to convert the range to Table.
Create a PivotTable
The following steps will guide us to the creation of a PivotTable.
- Select any cell within the source data,
- Go to Insert Menu, and click on PivotTable in table group
- A create PivotTable dialog box will appear, ensure the correct table or cells range is highlighted in the table/Range field.
- Choose the location you want the PivotTable to be placed. Select either New Worksheet to place the PivotTable in a new worksheet, or select the existing worksheet to place the PivotTable at a specified location in an existing worksheet. I will go with default to have my PivotTable on New Worksheet.
- Click OK to create a blank PivotTable in the specified location like what appear in the screenshot below
Arrange / Rearrange Fields in a PivotTable
This can be done on the PivotTable fields list located at right-hand side of the worksheet. It’s made up of field section and layout section as it appears in the screenshot below.
- Field section is where you can find the name of the field that can be add to PivotTable since is correspond to the column names of our source data. Select the checkbox next to the field name in the field section to add it to the layout section. By default, non-numerical field are added to Rows area, numerical fields are added to Values area, and time or date are added to Columns area.
- Layout section at the other hand contains the Filter area, Columns area, Rows area and Values areas. Click on any field name of your choice and drag it to where you want it to be in the layout if you don’t want to go with default.
The good part of it, is that any change made on the PivotTable Field immediately reflect to your PivotTable.
If check my layout section, you will notice those fields appear as chosen. I placed motorcycle on Filter area to extract data from source based on selected Motorcycle Products, with other criteria that include Sale Rep placed in Rows areas, to generate sum of sales in values area within the Selected Period in Columns areas.
Hope this post is helpful?