MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Perform Sentiment Analysis in Excel

November 19, 2020 - by Bill Jelen

Excel Perform Sentiment Analysis in Excel. Photo Credit: Justin Bashore at

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.

Some message board posts appear in A. The Sentiment Analysis Tool categorizes each phrase as positive, neutral, or negative. The Score ranges from 0% to 100%, with 100% being positive.

Title Photo: Justin Bashore at

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

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.