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.
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.
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.
Related Articles
- How to Summarize a List of Names in Excel
- How to Group and Summarize Data in Excel
- How to Create a Summary Sheet in Excel
- How to Make Summary in Excel From Different Sheets
- How to Summarize Data by Multiple Columns in Excel
- How to Summarize Data Without Pivot Table in Excel
- How to Create Summary Table in Excel
- How to Create Summary Table from Multiple Worksheets in Excel
