Excel Pivot Table (Table of Contents)
Pivot Table in Excel
Pivot table in excel is used to categorize, sort, filter and summarize any length of data table which we want to get count, sum, values either in tabular form or in form of 2 column sets. To insert the pivot table, select the Pivot table option from the Insert menu tab, which will automatically find the table or range. We can use the short cut keys Alt + D + P simultaneously which we will detect the range of cells and take us to the final pivot option. We can also create a customized table by considering those columns which are actually required.
How to Create a Pivot Table in Excel?
It is very simple and easy to use. Let us see how to create a Pivot table with the help of some examples.
Example #1
In a Company where department work is to mark certain Id’s are they correct or not. They process data which has some sort of id’s and mark it as correct or not.
Below is the screenshot of raw data,
Suppose a company’s manager of the department wants to know the count of how many Prop_ID were correct and incorrect. He can manually count those values but for a large set of data, it will be a slow task. But there is a way to do this easily.
In his excel workbook, he will hit the Insert button and click on the pivot table on the leftmost button of his screen. Now he can choose the same worksheet or a new worksheet to open this pivot table. To know the count of Correct and Incorrect values in that prop_id the Status field which is correct or incorrect will be drag down to rows section and the values the count in reference of the property id’s the Prop_ID will be drag down to Values Section.
- Select the data and go to Insert tab click on pivot tables under the tables section.
- A dialog box appears. In the above image there are few checkboxes, first to select the table range which we did by selecting the data. Now where to insert the pivot table to insert in the same worksheet or different worksheet? If the data is very huge then it is convenient to insert the pivot table in the new worksheet and click OK.
- We get the below result, on the right-hand side we have our fields of the pivot tables which will be moved to rows and columns as the desired report and on the left-hand side, the pivot table will be created.
- Our Task is to check how many property ids were marked as correct and how many were marked as incorrect by the auditor. Drag Auditor fields to Rows section, Property_id to value section whereas the status field to filters section.
- We have made our pivot table which currently shows the total count of property id’s marked by the auditors.
- Now to check the status of how many property ids were marked as correct and how many were marked as incorrect. In the pivot table under the status, section click on it.
- Now check select multiple items and then check Correct and click on ok.
- Now we have a count of property id’s marked as correct by the auditor.
- Similarly, we can have counted for incorrect ones.
Example #2
In a sales company, we have a transactional sales data which contains which product made how many sales under which quarter and in what year.
Below is the screenshot of raw data,
This data is not up to 20 rows but it 66 rows in this example which can go down even further and it would be a tedious task to check for sales of a specific product under any quarter.
- Select the data to insert an pivot table. This time we will use a shortcut key to insert pivot tables, click alt then “D” and then “P”. Another dialog box appears. We have our data in excel and we want to create a pivot table then we have to click the next button.
- In the next step, it asks for a range of data. But as we had already selected the data so it is prefilled. Click on Next.
- Now the last dialog box asks us where we want our pivot tables in the same worksheet or another. We have to select New worksheet and then Click on Finish.
- On the right-hand side we have pivot table fields and on the left-hand side, we have our pivot table report to be made.
- Drag product in the rows section, sales under the values and quarter under the column section whereas year in the filter. And we have our report.
The above pivot table shows which product made how many sales in which quarter.
- If we want to check in the year 2017 what was the sales for the products we simply uncheck the year 2018 in the year tab.
- Now we have the sales for the products in the year 2017.
Explanation of the Pivot Table in Excel
Basically, pivot tables is a powerful Excel tool which helps us to summarize large amounts of data and saves us a lot of time.
Pivot tables are a reporting tool which has fields section which contains four fields:
- Rows: Data which is taken as a specifier.
- Values: Count of the data.
- Filters: Filters to hide out certain data.
- Columns: Values under different conditions.
Things to Remember About Excel Pivot Table
- Pivot tables do not change the values in the database.
- Pivot tables can be inserted in the same worksheet with the data or in another worksheet.
- For convenience, we add pivot tables in a new worksheet.
Recommended Articles
This has been a guide to Pivot Table in Excel. Here we discuss how to create a Pivot Tables in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
