The Pivot Table is the most technologically sophisticated component in excel. With the usage of pivot tables, we can easily slice and dice our data. The word Pivot means Rotation. So when we have data set to analyze, we could completely rotate it upside down to analyze and recognize things easily.
So let’s start learning about Pivot tables and how they work.
What is a Pivot Table in Excel?
A Pivot table is a dynamic summary table that generates from a large set of data that is already in the form of a table. We can use a database in the form of any worksheet or any external database to make a pivot table and get powerful insights from our data.
With the use of Pivot tables, you can rearrange the given data to you to get useful information from it. Limitation – The only minor limitation of a Pivot table is that it does not automatically update itself if there is any change in the source data. We can solve the issue by manually clicking the Refresh button in the Data Tab.
How to Use a Pivot Table?
The best way to learn about using a pivot table is to make one! Here we have the order details of the different pizza orders as follows:-
Here you can see that we have got the following columns in our data set:-
- Month – The month in which the order was placed.
- Day – Day of the week.
- Pizza – This is the type of pizza ordered.
- Category – The category of pizza.
- Veg/Non Veg – This tells whether the order was vegetarian or nonvegetarian.
- Pizza Size and Pizza Price
- Order Type – This is the way in which the customer preferred their pizza to them.
- Payment Method and Customer Rating
Where each row in the data set has the details for one order placed. In our data set, we have got details of 149 pizza orders.
Making a Pivot Table
Now, the first step to making a pivot table is to get our data into an Excel Table. Select any one cell from the data set, and then click on the Table button in the Insert tab on the ribbon.
As our range contains column headings, so we will mark the option for My table has headers, and then click ok.
Now that we have got the table, we can insert a Pivot table from our data set.
Select any cell from the table and then click on the Recommended Pivot Table button in the Insert tab.
This brings to us the Recommended Pivot tables dialog box that has the list of recommendations on the left and their previews on the right side.
The First Recommendation gives us the Sum of Pizza Sales divided into the Pizza Categories so this way we could get the Sales of Pizza for each category as shown in the preview. Once you click Ok, the pivot table is inserted into a new worksheet as follows:-
Insights – From this pivot table, we have come to know that the sales of Signature Category Pizza are highest while the sales of Classic Pizza Category are minimum.
So here you can see that we have got a Pivot Table Fields pane at the Right of the pivot table. This pane is draggable from the top so that we can change its position. It has two sections:-
- Field List – Name of all the Columns from our table.
- Sections – There are four sections in which we pull our fields. You can see that Rows contain Categories while Values contain the Sum of Pizza Price Field. This is so because we have taken a recommended pivot table.
Checking Other Recommendations of Pivot Table
We have inserted a pivot table in a new worksheet. But there are other useful insights too that we can get from other pivot table recommendations.
In this second recommendation, Sum of Pizza Price by Pizza Size, we have got the sales of pizza divided into Pizza Sizes. You can see that the preview of the Pivot table tells us that the Sales of Large Pizza is 40596 which is the maximum.
Thereafter, we can also get the number of pizzas sold by different payment methods.
As you can see the Count of Pizza by Payment Method tells us that the maximum number of orders were paid via cash (69) orders. Also, the total orders ( Card+Cash+UPI) are 149.
This is how we can make use of the Recommended Pivot Tables in Excel.
We can even start from scratch and then make our own Pivot table manually.
Thank you for reading.