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

Method 1 – Summarize Data in Excel Using a PivotTable

Let’s assume you have a dataset containing duplicate values, and you want to quickly summarize it using a PivotTable. Follow the steps below to achieve that:


  • Create a PivotTable:
    • First, select the dataset or click anywhere within it.
    • Then, go to the Insert tab and choose PivotTable.

  • Select the location where you want to place the PivotTable and click OK.

  • Now, drag the relevant fields to the appropriate areas in the PivotTable Selection Pane.

  • The resulting PivotTable will display the count of courses taken by each student, eliminating the need to repeat their names.

summarize data in excel using pivot table

Method 2 – Using DAX Functions to Summarize Data in Excel PivotTable

  • Alternatively, you can concatenate the courses into a single cell within the PivotTable.
  • Follow these steps


  • While inserting the PivotTable, check the Add this data to the Data Model checkbox.

  • Right-click on the Range field and select Add Measure.

  • Enter a Measure Name and enter the following formula in the formula box:
=CONCATENATEX(Range,[Course],", ")

summarize data in excel using pivot table

  • Click OK.
  • Drag the appropriate fields to the desired areas.

  • You’ll achieve the desired result.

summarize data in excel using pivot table

More PivotTable Features to Summarize Data in Excel

Here are some advanced PivotTable features to summarize data in Excel.

  • Group Rows: Select specific rows in the PivotTable, right-click, and choose to group them.

  • The summarized data will look as follows.

summarize data in excel using pivot table

  • Customize Summary Functions: Go to “Value Field Settings” and pick a different function to summarize the data.

  • Change the function as required from the Summarize Values By tab.

summarize data in excel using pivot table

  • Show Values Differently: Explore options in the Show Values As tab.

Things to Remember

  • Always select anywhere in the PivotTable to access the editing tools.
  • Drag fields to the appropriate areas until you achieve the desired result.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF