MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sentiment Analysis


November 02, 2017 - by Bill Jelen

Sentiment Analysis

Sentiment Analysis in Excel! There is a free add-in from Microsoft Labs that will let you do sentiment analysis in Excel. What if you have to wade through hundreds of survey comments to see what people think of your company? Excel can assign a probability showing how positive or negative each comment is.


Watch Video

  • It is easy to quantify survey data when it is multiple choice
  • You can use a pivot table to figure out what percentage each answer has
  • 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 - Azure Machine Learning.
  • Traditional sentiment analysis requires a human to analyze and categorize 5% of the statements.
  • Traditional sentiment analysis is not flexible - you will rebuild the dictionary for each industry.
  • Excel uses MPQA Subjectivity Lexicon (read about that at http://bit. ly/1SRNevt)
  • 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
  • It can get fooled by double-negatives
  • To install, go to Insert, Excel Store, 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
  • Companion article at: http://sfmagazine.com/post-entry/may-2016-excel-sentiment-analysis/

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2062 sentiment analysis in Excel
  • I was a Thanksgiving night and we were
  • sitting around the pumpkin pie and Jess
  • friend of ours started talking about
  • doing sentiment analysis on Twitter data
  • and I said hey you know that Excel Excel
  • has a way to do sentiment analysis and I
  • realized I didn't have a good video on
  • this or any video on this so this this
  • video is not doing sentiment analysis in
  • Excel now the first question is what the
  • heck is sentiment analysis and if you do
  • a survey of your customers and they have
  • a multiple choice selection where they
  • can choose from one to five well that's
  • really really easy to analyze you can
  • just create a little pivot table insert
  • pivot table existing worksheet right
  • here click OK we want to know the
  • question they are the answer to the
  • question and then how many how many
  • answers there were for each one that
  • gives us the absolute number you can
  • even come in here and change this from
  • field settings to show values as a
  • percentage of the column total like that
  • all right so you can see for each answer
  • what percentage of the people get that
  • answer all right but sentiment analysis
  • is for when you have a really long
  • answer where you say hey all right well
  • you know tell us tell us why you gave us
  • that answer and they use sentences or
  • paragraphs well if you have hundreds or
  • thousands of these it's very hard for
  • someone to go through and read them all
  • and figure out what's going on all right
  • so there's two different kinds of
  • sentiment analysis typically in the past
  • you'd use a human supervised learning
  • algorithm so if you had 5,000 answers go
  • through you know 200 of those and choose
  • the positive and negative words and
  • phrases you're essentially building a
  • dictionary the positive and negative
  • words but you know this was very
  • limiting if you did this for a place
  • that did car repair and then had a
  • different customer you know who did
  • carpet cleaning those two dictionaries
  • are completely different you have to do
  • the machine learning are the human human
  • supervised learning over and over and
  • over again so Excel uses this thing
  • called the mpq a subjectivity lexicon
  • and you
  • go google it has the info about it 5097
  • negative words 2533 positive words i and
  • so it works great for short sentences or
  • tweets or facebook posts but one thing
  • I've noticed is that if someone is
  • writing a double negatives I cannot say
  • that I do not hate this feature well the
  • machine learning will fail there and
  • heck I fail I can't tell if they're
  • happy or not alright so here's what we
  • do in Excel 2013 or Excel 2016 go out to
  • the insert tab go to the store when the
  • search box comes up search for Azure
  • machine and you'll get Azure machine
  • learning right there we click Add
  • alright and two different tools out here
  • the Titanic survivor projector which is
  • fun and the text sentiment analysis
  • Excel atom which use that one alright
  • here's a couple things that will trip
  • you up you're heading take a paragraph
  • to explain your answer needs to match
  • the schema and the schema says that the
  • heading has to say tweet underscore text
  • so up here tweet underscore text of
  • course case sensitive matters alright
  • and then close the schema and then
  • predict input a 1 to 100 my data has
  • headers output data b1 include the
  • headers they're going to give us two
  • columns make sure that you have two
  • blank columns there over eyes otherwise
  • it's going to override the data you have
  • two choices a few rows at a time or as a
  • batch this is just a hundred so it
  • really doesn't matter i will choose
  • predict and bam just that fast all right
  • now we get two columns we get a
  • sentiment and a score alright so let's
  • represent these scores here as
  • percentages with a bunch of decimal
  • places alright so 47.4 96 this goes from
  • zero to a hundred percent close to 100
  • is extremely positive close to zero is
  • extremely negative right so here we have
  • one where there's a minor problem drives
  • me crazy can't find the solution so you
  • can see why that's being rated as
  • extremely negative let's look at one
  • that comes up extremely positive
  • alright so you know so we have some
  • happy words here please and thank you !
  • and so on that might be contributing to
  • the high score all right so is a perfect
  • no but it will give you a quick quick
  • way to tell you you know how many people
  • are extremely happy or extremely
  • negative about those answers and of
  • course again here we can do this with a
  • pivot table insert pivot table go to an
  • existing worksheet right here click OK
  • and we're interested in the sentiments
  • and then maybe what the average score is
  • for each of those so we'll change this
  • under field settings to be an average
  • click OK and so or maybe even account I
  • guess we'd want to know the count how
  • many how many people so we'll take some
  • other field and so we know how many
  • people were negative how many people
  • were neutral how many people were
  • positive and what the average score of
  • each of those was all right so if you
  • have survey data and it's multiple
  • choice easy to use a pivot table to
  • figure out what the what percentage each
  • answer has but for freeform text answers
  • it's hard to process if you have
  • hundreds or thousands of sentiment
  • analysis it's a machine based method for
  • predicting if an answer is positive or
  • negative microsoft offers a free tool
  • for this works in Excel 2013 or Excel
  • 2016 called asier machine machine
  • learning usually have to go through and
  • categorize five percent of statements
  • manually by hand it's not flexible you
  • have to wreak a tiger eyes for each new
  • data set but Excel is using this mpq a
  • subjectivity lexicon it's a generic
  • dictionary it's going to work for short
  • sentences tweets Facebook posts I can
  • get fooled by double negatives so just
  • go to the Excel store search for Azure
  • machine learning specify an input range
  • and then two columns for an output range
  • don't forget to change the heading to
  • match the schema tweet text in this
  • particular case all right so there you
  • go next time you have a large amount of
  • data to analyze check out using Azure
  • machine learning the free ad in for
  • Excel 2013
  • thanks for stopping by i will see you
  • next time for another net cast from mr.
  • XL

Download File

Download the sample file here: Podcast2062.xlsm

Title Photo: PDPics / Pixabay


Bill Jelen is the author / co-author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.