Excel 2020: Perform Sentiment Analysis in Excel

November 19, 2020 - by Bill Jelen

It is easy to quantify survey data when it is multiple choice: You use a pivot table to figure out the percentage for each answer. But what about free-form text answers? These are hard to process if you have hundreds or thousands of them.

Sentiment analysis is a machine-based method for predicting if an answer is positive or negative. Microsoft offers a tool that does sentiment analysis in Excel. It is called Azure Machine Learning.

Traditional sentiment analysis requires a human to analyze and categorize 5% of the statements. Excel uses MPQA Subjectivity Lexicon. This generic dictionary includes 5,097 negative and 2,533 positive words. Each word is assigned a strong or weak polarity. This works great for short sentences, such as tweets or Facebook posts.

Look in the Add-ins group of the Insert tab. The first icon used to be called Store and now is called My Apps. Click that icon and search for Azure Machine Learning.

Specify an input range and two blank columns for the output range.

The heading for the input range has to match the schema tweet_text.

The results show positive, negative, or neutral and a percentage score. Items near 99% are very likely positive. Items near 0% are very likely negative.

Title Photo: Justin Bashore at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.