How to Summarize Data in Excel Using Pivot Table (2 Examples)

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 shows the count of courses taken by each student instead of repeating their names.

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.

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.

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.

Summarize Data Using PivotTable.xlsx

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. Stay with us and keep learning.

<< Go Back to Pivot Table Field List | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!