How to Analyze Text Data in Excel (5 Suitable Ways)

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we take the help of Excel to analyze data that are text in nature. In this article, I will show 5 useful ways on how to analyze text data in Excel.


Download Practice Workbook

Download this workbook and practice while going through the article.


5 Useful Ways to Analyze Text Data in Excel

This is a portion of the dataset that I am going to use for this article. I have the Gender of some employees and the Position they are currently in. I will analyze this dataset now.

how to analyze text data in excel


1. Use of Filter Feature to Analyze Text Data in Excel

The first feature you can use to analyze text data is the Filter.

Steps:

  • Select the headings of your table. Then go to the Data Tab >> select Filter.

  • Excel will create drop-down boxes in your headings. You can now filter your dataset using these drop-down boxes. For example, suppose you want to see the list of all Content Developers. To do so, select the drop-down box of Position >> Check Content Developer. Then click OK.

how to analyze text data in excel

  • Excel will get the list of all Content Developers.


2. Apply Functions to Analyze Text Data in Excel

Excel also offers plenty of functions to analyze text data. In this section, I will use two of them. These are the COUNTIF and IF functions. You can use other functions too.


2.1. COUNTIF Function

I will find out the number of employees in each position. To do so, I will use the COUNTIF function.

how to analyze text data in excel

Steps:

  • Go to E5 and write down the formula
=COUNTIF($C$5:$C$60,E5)

  • Now press ENTER. Excel will count the frequency for “Content Developer” from the range C5:C60.

how to analyze text data in excel

  • Now, use Fill Handle to AutoFill up to F7.


2.2. IF Function

There are also many applications of the IF function to analyze text data in Excel. I will show one such example here.
I have modified the dataset a little. Now I have the Name of some employees and their positions. I will categorize them based on their position. “Content Developer” will be Category A, and “Team Leader” will be Category B.

how to analyze text data in excel

Steps:

  • Go to D5. Then write down the formula
=IF(C5="Content Developer","Category A","Category B")

Explanation:

  • The logical test is C5=”Content Developer”. If the test is TRUE, the output will be “Category A“. If it is FALSE, the output will be “Category B“.
  • Now, press ENTER. Excel will return the output.

how to analyze text data in excel

  • Now, use Fill Handle to AutoFill up to D12.


Similar Readings


3. Use of Pivot Table to Analyze Text Data in Excel

In this section, I will show how to use the pivot table to analyze text data in Excel.

Steps:

  • Select the entire dataset B4:C60. Then go to the Insert tab >> PivotTable >> select From Table/Range.

how to analyze text data in excel

  • A new window will appear. Put the range and then select New Worksheet to get a pivot table in a separate worksheet. Then click OK.

  • Excel will create a pivot table. You can now analyze the way you want. For example, if you drag Position in Rows field and Gender in Values and Columns field, you will get this table.

how to analyze text data in excel


4. Apply Charts to Analyze Text Data in Excel

You can also apply different types of charts to represent your dataset. We calculated the frequency of each position using the COUNTIF function. Now I am going to represent that result using a chart.

Steps:

  • Select E4:F7. Then go to the Insert tab >> select the pie-chart icon >> choose a suitable pie-chart.

  • Excel will create a pie chart.

how to analyze text data in excel


5. Use of Analysis Toolpak for Analyzing Text Data in Excel

Now, I will show how to use Analyze Toolpak to analyze text data.

Steps:

  • Select B4:C60. Then select the Analyze Data from the Home.

  • Excel will show the analysis option in a new ribbon. You can analyze the data the way you can.

how to analyze text data in excel


Things to Remember

  • Use Absolute Reference ($) to lock the cell.
  • You can try different designs and formats for the charts.

  • Excel recommends a list of charts for your dataset.

how to analyze text data in excel

  • Analyze Toolpak may not be available if you are using earlier versions of Excel.

Conclusion

In this article, I have demonstrated 5 useful methods for analyzing text data in Excel. I hope it helps everyone. If you have any kind of suggestions, ideas, or feedback, please feel free to comment down below.


Related Articles

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo