Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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. But summarizing text data becomes difficult as we can not use any formulas. No worries! Today in this article, I am sharing with you how to summarize text data in excel. Stay tuned!


Download Practice Workbook

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


3 Quick Steps to Summarize Text Data in Excel

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

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


Similar Readings


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
=CONCATENATEX(Range,[Products],",")

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

Read More: How to Summarize a List of Names in Excel (5 Effective Ways)


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 Data in Excel (8 Easy Methods)


Things to Remember


Conclusion

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo