Pivot table helps the user to expand, isolate, sum, and group the particular data in real time. It can show you the differences in very big data sets and helps in organization of large amounts of data in Excel
Steps to create a Pivot Table:
- Select any Cell from the table you want to create the Pivot Table
We have the above data set with us for this example.
- Go to Insert Tab -> Click on Pivot Table
- A dialog box, Create Pivot Table appears:
Here Select the table/range by first clicking inside the Table/Range field and dragging and selecting the required range.
Then choose where you want to display the pivot table. Here we have chosen to place the table in the existing worksheet to help us analyze more effectively.
And then Click on OK.
- Now the option to choose Pivot Table Field Comes:
We will choose Date, City and Quantity fields for now.
Now that your pivot table has been created it will look something like this:
Here, you can see how effectively we can analyse important information such as on a particular date, we can see the number of quantities of products sold in stores of each city.
This task would be daunting to analyse from the plain tabular database we had.
So, you have the features understood now, let us dive into making reports more fun by inserting slicers in Pivot Tables.
Insert a Slicer
Slicers are interactive elements of a pivot table added after Excel 2010. Slicers are like interactive buttons that allow the user to see what has been chosen in the pivot table.
Steps to insert a slicer:
- Click anywhere inside your Pivot Table
- Go to the Pivot Table tools tab on the Ribbon and Click on Analyse
- Click on Insert Slicer under the Filter category
- Select the desired fields to insert the slicers
We have selected Product and Store as our slicers
Yay! You now have these slicers inserted:
Now you can click on the data elements inside these slicers to show you the relevant information