Wasim Akram
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.
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.
Read More: How to Summarize Data in Excel Using Pivot Table (2 Examples)
Similar Readings
- How to Group and Summarize Data in Excel (3 Suitable Ways)
- Summarize Data Without Pivot Table in Excel
- Linking Excel Sheets to a Summary Page (4 Easy Methods)
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.
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.
- In conclusion, we have our output in our hands summarizing text data in a handy way.
Read More: How to Summarize Data in Excel (8 Easy Methods)
Things to Remember
- The VLOOKUP and SUMIF functions are used to summarize data with numeric values. To summarize text data you can not use the VLOOKUP function or any other functions.
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.
