If you have large data tables to handle, learning how to create a Pivot table in Excel is something, you should never overlook.
Pivot tables are one of the most important and powerful tools of excel which lets us handle a large amount of data seamlessly. With a Pivot table, you can easily calculate, analyze and summarize important data tables within seconds.
To understand pivot tables better, take this example for instance. Suppose you are a businessman and selling multiple products in your day to day job. You have an excel sheet of total products sold in a year with the total revenue made, along with their product ids and name.
If you have to calculate the average, sum, max or min of the revenue of a particular product sold in that year, how will you do it?? If you have been working on excel, I’m sure you will opt for putting a formula and so??
But if you ask me, my answer would be Pivot tables. If you are hearing the term pivot for the first time in Excel, I’m sure you are gonna love this Excel Pivot Table Tutorial.
How to Create a Pivot Table in Excel
After getting to know about what actually pivot tables are, I know you desperately want to explore this excel function.
This guide is dedicated to teaching you how to use pivot tables in Excel and how to create them to analyze and summarize big data seamlessly.
Let’s find out the steps involved in this process.
Step 1. Since we are learning to create the Pivot table in Excel, the first step is to open it, of course. So click on the start menu, go to MS office and open excel.
Once the Excel is opened, you need to open the large data files which you would like to have sorted, analyzed and summarized. Yes, having large data tables is a must to leverage the pivot table feature to the fullest. You can even import access data tables to your excel sheets if you wish to.
Besides the large table along with multiple rows and columns, it’s also important to have at least one column with numerical values. Once you have your excel sheet ready fulfilling all the requirements and opened, you are good to go.
The screenshot below is to show the example table we will be working on, to create and use the pivot table.
As in the above field you can see, we have four fields Product Name, Product ID, Sales Quantity and Total revenue; we will be creating a pivot table to sort these data out.
Step 2. Now its time to build the pivot table. Click on any cell in the table and click on the Insert tab from the top. In the Tables group, click on the Pivot table to insert the pivot table in the sheet. Have a look at the below screenshot.
Step 3. After clicking the pivot table option as said above, a new Create Pivot Table dialogue box will pop-up. You don’t have to do anything there, just click Ok and you Pivot table will be inserted in the sheet.
Step 4. Once the pivot table is created, the Pivot Table fields window will appear showing all the fields present in the sheet. The page looks like the below image.
Step 5. Now we need to drag and drop the data fields into the Row, columns, and Values area respectively. Or you can directly select them to make things easier.
For instance, I selected the fields Product name and Sales Quantity and got the below result.
You can drag and drop more fields under Values if you wish to. And the result will be changed to the following image.
Step 6. By default, the result we got was the sum of Sale quantity. If you want the average number of the to total sales made instead of their sum, click the small down arrow next to the Sum of sales option under the values field, and select Value Field Settings.
Step 7. In the new window, customize the table options the way you want to. We want to calculate the average of the number, for instance, then the option we need to select from the drop-down list under Summarize value field by.. is average and click Ok.
Have a look at the below image to see the result of selecting average instead of Sum.
Step 8. Also, if you want your data to be sorted in ascending or descending order, you can simply do that by selecting any cell from the column and then clicking Sort under the Options menu at the top. Select the order from A to Z or Z to A, as per your choice.
Step 9. That’s not it. If you don’t want total or average of the entire column, instead you want the result for just one product on the list, you can do that too.
Just click the drop-down arrow next to the first field i.e., Row Labels and un-check the rest of the options except the one you want results for. For instance, I want a total of only computers sold in that year; I will do the following customization.
The result will look like the below image.
After getting to know about how do Pivot tables are created in Excel, let me illuminate some basic uses of Pivot tables. Below are some places where Pivot tables make sense the best.
- If you are a salesperson, you can easily calculate, analyze and summarize your total sales made.
- The Pivot tables help you sort the duplicate data with ease and combine them into one.
- You can easily compare your sales or revenue of different months or year in pivot tables with just a few simple clicks.
- Moreover, you can also create pivot charts of the data sorted and analyzed in pivot tables.
- Also, you can easily change the location and order of fields in pivot tables.
These are some basic places where Pivot tables come out really handy!
If you have more questions to ask on How to Create a Pivot Table in Excel and to leverage this excel feature, drop us a comment down below. We will assist you as soon as possible.
