In this article, I will tell you what is excel pivot table and how to create, and remove excel PivotTable with examples.
1. What is an Excel PivotTable?
- An Excel PivotTable is a powerful feature that allows you to summarize and analyze large amounts of data in a spreadsheet.
- It enables you to quickly create a summary report by grouping data into categories and displaying the results as a table.
- PivotTables allow you to filter, sort, and manipulate data in a variety of ways, making it easy to identify trends, patterns, and outliers.
- It is a useful tool for data analysis and reporting in Excel.
2. How To Create An Excel PivotTable Example.
- Here is an example of how to create an Excel PivotTable.
- We have a large dataset with sales information for a company as below.
- This is a simple dataset that includes sales information for two product categories (Widgets and Gadgets) in two regions (East and West).
A B C 1 Product Category Region Sales 2 Widgets East $10 3 Widgets West $15 4
Gadgets East $20 5
Gadgets West $12
- We want to analyze the sales by product category and region.
- Here’s how we can create a PivotTable to do that.
- Select the entire dataset, including headers.
- Go to the “Insert” tab in the Excel ribbon and click on “PivotTable” in the Tables group.
- It will pop up the PivotTable from table or range dialog window.
- The cell range that you selected is inputted in the Table/Range text box.
- And it checks the New Worksheet radio button by default.
- If you check the Existing worksheet radio button, you should specify the existing worksheet name and the starting cell address such as ‘Sheet5’!$A$1.
- Make sure the starting cell address does not overwrite the existing PivotTable in the existing worksheet.
- Click the OK button, then it will create a new worksheet and display the PivotTable Fields pane on the excel right side.
- In the PivotTable Fields pane, drag the “Product Category” field to the “Rows” area and the “Region” field to the “Columns” area.
- Drag the “Sales” field to the “Values” area.
- Excel will automatically create a table that shows the total sales for each product category and region.
- You can further customize the table by adding filters, sorting, and formatting.
- Here’s what the resulting PivotTable might look like.
- This PivotTable shows the total sales for each product category in each region, as well as the total sales for all regions.
- But you should note if the Region value contains hidden characters such as white space at the beginning or end, it will treat the value as a different region value.
- For example, if one region value is “East “, and another region value is ” West “, the PivotTable will treat the region value as 2 new regions.
- And you will get the PivotTable that is not correct like below.
3. How To Remove an Excel PivotTable?
- To remove an Excel PivotTable, follow these steps.
- Click anywhere inside the PivotTable to select it.
- Click the “PivotTable Analyze” tab in the Excel ribbon.
- Click on “Select” in the “Actions” group and choose “Entire PivotTable” to select the entire PivotTable.
- Click the PivotTable Analyze tab —> Clear —> Clear All in the Actions group to remove the PivotTable.
- Note that clearing a PivotTable will remove all the fields, formatting, and calculations associated with it.
- There is also a quick and easy way to remove all the PivotTables in an excel worksheet, that is delete the newly created worksheet by right-clicking the worksheet name and then clicking the Delete menu in the popup menu list.