Textual Analysis/Theme Analysis in Excel without an outside AI software?

Copper Contributor

I'm wondering if there's a way for Excel to look at comments with a formula, and then automatically label themes with a 1 in Excel if they fall into a specific theme category. For example, please look at the below. This is what I'm hoping to accomplish, but with hundreds of comments, I am hoping to find an automated way to label the themes. I want my excel to automatically look at the comment and then properly put a 1 in the correct theme category:

 

Customer CommentsTheme 1 - ProductTheme 2 - Service

I really like the product.

1

 

I had a good experience with the call team.

 

1

 

I'm having a hard time figuring this out, as most online resources usually refer me to an outside AI software and I want to be able to do this directly in Excel. Thank you for your time and assistance!

9 Replies
I think it may be do-able but I'd need to see more examples of comments. I can see where the first comment relates to Theme 1 (Product), but I see the next comment goes with Theme 2 (Service) but does not contain "service" in the wording. It's possible to search for multiple words or phrases. A larger sample of comments would help.

@Patrick2788 : Thank you so much for the reply! I've provided a more robust example below. Please let me know if I can provide anything additional. Thank you!! - Julie

 

Customer Comments

 

Theme 1 - Product

Theme 2 - Service 

Theme 3 - Billing

Theme 4 - Paperless

Theme 5 - Frequency of Communications

You email too much.

 

 

 

 

1

I enjoy the product.

1

 

 

 

 

I had a great call with the service team.

 

1

 

 

 

I'm confused about my bill.

 

 

1

 

 

I don't want to go paperless.

 

 

 

1

 

I prefer regular mail.

 

 

 

1

 

I can't reach a representative.

 

1

 

 

 

I can't log on.

1

 

 

 

 

I love the app.

1

 

 

 

 

I want to receive more updates.

 

 

 

 

1

My bill was very easy to understand.

 

 

1

 

 

I had a great experience with the call center.

 

1

 

 

 

I was quoted too high.

 

 

1

 

 

I need someone to call me.

 

1

 

 

 

I'm confused about my coverage.

1

 

 

 

 

I don't want anymore emails.

 

 

 

 

1

I understand how to use everything perfectly.

1

 

 

 

 

I need to talk to someone about my bill.

 

 

1

 

 

I love the product.

1

 

 

 

 

I enjoy having paperless notifications.

 

 

 

1

 

@JELM_ 

I can see how your searches kept leading to AI. I went through your list of comments and pulled words or phrases and put them under the respective themes 1-5:

Patrick2788_0-1671657949669.png

I can see how one approach would be to continue to build a list of words and phrases for each theme and then create formulas to assign themes using lookups and matching. The part Excel doesn't understand would be the context of words in a given sentence.  For example, a comment may mention "update" or "updates".  The comment could be filed under theme 1 (product) or even theme 5 (frequency of communication) depending on the other words in the sentence.

 

Theoretically, it's possible with functions in Excel but the accuracy would always be a question.  You could make extensive pool of words and phrases but then you'd have to account for sentence structure and context and that's endless. 

Thank you so much for taking the time to look into this! My team and I are okay with the accuracy not being perfect, and we'd like to find a way to do the first approach that you mentioned: build a list of words or phrases for each theme, and then create formulas to assign themes using lookups and matching. Do you have any resources on how to do this? Or are you able to provide additional direction to accomplish this? Thank you SO much!

Which version of Excel do you have? If you go to File | Account, in the section where it says About Excel, which 'channel' are you on?

Version 2202, and the Semi-Annual Enterprise Channel

@JELM_ 

I had to get a bit creative because there's a big difference in functions available in Semi-Annual vs Current/Monthly.  The formula is not as elegant as I'd like but it gets the job done. Please see attached demo workbook.

Thank you so much for your help! I was able to set it up how I'd like. I really appreciate your assistance!! Best wishes and happy holidays,
Julie
You're welcome! Glad to help. Happy Holidays!