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.

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.

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

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],",")

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.

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.

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

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.

Summarize Text Data.xlsx

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

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

Get FREE Advanced Excel Exercises with Solutions!