One of the most powerful features of Excel is “Pivot Table”. You can still do most awesome things with it even if you are unfamiliar with excel. It is like dragging and dropping rows/columns headers to create reports.
What is Pivot Table
Pivot table is a tool in Microsoft Excel that help to make your worksheets more manageable by summarizing huge dataset with a few clicks in small amount of time. It allows you to extract the significance from a detailed large dataset. Now you understand why the Pivot table is important, so let’s go to see how to create it.
How to Create / Insert Pivot Table in Excel
Here are the steps which should be followed to create pivot table –
In the Pivot Tabe Fields section, you have the fields and the areas (as highlighted below):
- 1. Click anywhere in the dataset.
- 2. Go to Insert –> Tables –> Pivot Table.
- 3. After that a dialog box appears explaining the data set and asking where you want to create the Pivot table.
a) Table/Range : based on your data set, its filled in by default. Excel automatically detect the correct range but you can change it manually if needed.
b) Specify the location where you want to create PivotTable. Else a new worksheet is created with the Pivot Table.
- 4. Click “OK”.
- 5. As soon as you click on “OK”, a new worksheet is created with the Pivot Table in it. On left, you would see the Pivot Table name with a single line instruction and on the right side there is Pivot Table fields.
- 6. Choose the field (column) which you want to summarize. After checking this, you will get the columns with the summary of data set.
Based on the backend data used, Field are created in Pivot Table. Within the areas section, you place the fields and according to that your data updated in the Pivot Table. It’s just a drag and drop things. Drag the field to that area where you want and get the result.
You can also sort the data within the Pivot Table.
- 1. Click any cell inside the field which you want to sort.
- 2. Right click and click on Sort.
Changing Summary Calculation –
Either by summing or counting the items, excel summarize data by default. If you want to change the type of calculation that you want to use, follow these steps -
- 1. Click any cell inside the Sum column.
Two-dimensional Pivot Table
You can create a two- dimensional pivot table, just by dragging a field to the Row area and Columns area.