Md. Shamim Reza
This article illustrates how to summarize data in excel Pivot Table. PivotTable is a great tool in excel to quickly summarize huge datasets even if they are unorganized. It can detect the datasets and often summarize them automatically. This saves a lot of time and effort. Follow the article to learn how to use this feature to summarize your dataset in excel.
1. Summarize Data in Excel Using a PivotTable
Assume you have the following dataset containing duplicate values. You can quickly summarize the dataset by inserting a PivotTable.
Follow the steps below to do that.
📌 Steps:
- First, select the dataset or click anywhere on it. Then select Insert >> PivotTable.
- Next, choose the location for the table. Click OK after that.
- Now, drag the fields between appropriate areas in the PivotTable Selection Pane as shown below.
- Finally, you will see the following result. Here, the PivotTable is showing the count of courses taken by each student instead of repeating their names.
Read More: How to Summarize Data Without Pivot Table in Excel
Similar Readings
- How to Create a Summary Sheet in Excel (4 Easy Ways)
- Linking Excel Sheets to a Summary Page (4 Easy Methods)
- How to Summarize a List of Names in Excel (5 Effective Ways)
2. Using DAX Functions to Summarize Data in Excel PivotTable
Alternatively, you can make PivotTable concatenate the courses in a single cell. Follow the steps below to do that.
📌 Steps:
- First, check the ‘Add this data to the Data Model’ checkbox while inserting the PivotTable.
- Then, right-click on the Range field. Next select ‘Add Measure’.
- Now, enter a Measure Name. Then, enter the following formula in the formula box. Click OK after that.
=CONCATENATEX(Range,[Course],", ")
- Next, drag the appropriate fields between areas as shown below.
- Finally, you will get the desired result as follows.
Read More: How to Create Summary Table in Excel (3 Easy Methods)
More PivotTable Features to Summarize Data in Excel
Here are some advanced PivotTable features to summarize data in Excel.
- Select some rows in the PivotTable and right-click to group them.
- After that, the summarized data will look as follows.
- Besides, you can also pick a different function to summarize the data. Go to the Value Field Settings to do that.
- Now change the function as required from the Summarize Values By tab.
- You can also show the values in different ways from the Show Values As tab.
Read More: How to Group and Summarize Data in Excel (3 Suitable Ways)
Things to Remember
- You must select anywhere in the PivotTable to access the editing tools.
- Drag the fields to the appropriate areas until you get the desired result.
Download Practice Workbook
You can download the practice workbook from the download button below.
Conclusion
Now you know how to summarize data in excel using Pivot Table. Do you have any further queries or suggestions? Please let us know using the comment section below. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
