How to Summarize Text Data in Excel (with Easy Steps)

While working in Microsoft Excel we need to make a final report. To make the final report often we make a summarized report for final submission. However, summarizing text data becomes difficult as we cannot use any formulas. No worries! Today in this article, I am sharing with you how to summarize text data in Excel. Stay tuned!

How to Summarize Text Data in Excel: 3 Quick Steps

In the following, I have shared 3 simple and easy steps to summarize text data in Excel.

Suppose we have a dataset of some Products according to their Groups. Now we are going to summarize all products.

Summarize Text Data in Excel

Step 1: Create a Pivot Table from Dataset

  • To begin with, we have to create a pivot table.
  • In order to do so, select the whole dataset and go to the “Pivot Table” feature from the “Insert” option.

  • After that, a new window will appear named “PivotTable from table or range”.
  • Then, select “Existing Worksheet” and choose the desired location in your workbook.
  • Most importantly check to mark the “Add this data to the Data Model” option.
  • Hit the OK button to continue.

Summarize Text Data in Excel

  • In summary, we have successfully created our pivot table in the same worksheet.

Summarize Text Data in Excel

Step 2: Apply DAX Formula to Selected Range

  • In this second step, we will apply a formula for the table.
  • Now, from the right pane, right-click the mouse button putting the cursor over the “Range” option.
  • Then, choose “Add Measure” from the options.

  • A new window will appear named “Measure”.
  • Hence, we will put a name in the “Measure Name” section.
  • After that, put the formula down and press OK

Summarize Text Data in Excel

  • This time we will get a new field added to our pivot table field list.
  • To finish, checkmark both the range just like the following screenshot.

  • Finally, we have summarized our text data using the pivot table.

Summarize Text Data in Excel

Step 3: Remove Grand Total from Final Output

  • In this final step, we will remove the “Grand Total” option from the summarized list.
  • To do so, select any cell from the pivot table and choose “Off for Rows and Columns” from the “Design” option.

Summarize Text Data in Excel

  • In conclusion, we have our output in our hands summarizing text data in a handy way.

Summarize Text Data in Excel

Read More: How to Summarize Subtotals in Excel

Things to Remember

  • The VLOOKUP and SUMIF functions are used to summarize data with numeric values. To summarize text data you cannot use the VLOOKUP function or any other functions.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


In this article, I have tried to cover all the methods to summarize text data in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.

Related Articles

<< Go Back to Summarize Data In Excel | Data Analysis with Excel Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF