Learn Excel – Sentiment Analysis – Podcast 2062

Learn Excel – Sentiment Analysis – Podcast 2062


Learn Excel from MrExcel Podcast, Episode
2062: Sentiment analysis in Excel Oh hey, it 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 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 video is about 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 1 to 5, 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 there or the
answer to the question, and then how many answers there were for each one, and that
gives us the absolute number. You can even come in here and change this
from Field Settings to Show values as a % of the Column Total, like that. Alright, so you can see for each answer what
percentage of the people get an answer. Alright, but sentiment analysis is for when
you have a really long answer where you say, “Hey, alright, well you know, tell us why
you gave us that answer?” And they, you know, use sentences or paragraphs. Well, if you have hundreds or thousands of
these, it’s very hard for someone to go through it and read them all and figure out what’s
going on, alright? 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 of
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 or the
human supervised learning over and over and over again. So, Excel uses this thing called the MPQA
Subjectivity Lexicon and you can go Google this. It has the info about it – 5,097 negative
words, 2533 positive words. 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 in 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 get Azure Machine Learning right there. We click Add. Alright, and two different tools out here:
the Titanic Survivor Predictor, which is fun; and, the Text Sentiment analysis Excel Add-in. Let’s use that one. Alright, here’s a couple of things that will
trip you up. Your heading: Take a paragraph to explain
your answer. It needs to match the Schema and the Schema
says that the heading has to say tweet_text. So, up here: tweet_text, of course, case sensitive
matters, alright. And then close the Schema, and then Predict,
Input: A1 to 100, My data has headers, Output: DataB1, Include the headers. They’re going to give us 2 columns. Make sure that you have 2 blank columns there;
otherwise, it’s going to override the data. You have 2 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. Alright now, we get 2 columns: we get a Sentiment
and a Score, alright. So, let’s represent the scores here as percentages
with a bunch of decimal places. Alright, so 47.496, this goes from 0 to 100%. Close to 100 is extremely positive, close
to 0 is extremely negative, alright? 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, exclamation points and so on. That might be contributing to the high score. Alright, so is it perfect? No, but it’ll 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 Sentiment, and then maybe with 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 a Count. I guess we’d want to know the Count, how many
people. So we’ll take some other field, and so, we
know how many people were negative. Ooh, how many people were neutral, how many
people were positive and what the average score of each of those was. Alright, so if you have survey data and it’s
a multiple choice, easy to use a pivot table to figure out what percentage each answer
has. But for free-form text answers, it’s 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 free tool for this. Works in Excel 2013 or Excel 2016, called
Azure Machine Learning. Usually have to go through and categorize
5% of the statements manually by hand. It’s not flexible, you have to re-categorize
for each new data set, but Excel is using this MPQA 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 and a two columns for an
output range. Don’t forget to change the heading to match
the Schema, tweet_text, in this particular case. Alright, so there you go. Next time you have a large amount of data
to analyze, check out using Azure Machine Learning, the free Add-in for Excel 2013. Thanks for stopping by, we’ll see you next
time for another netcast from MrExcel.

20 thoughts on “Learn Excel – Sentiment Analysis – Podcast 2062

  1. Very interesting video. It would be great and much appreciated if you could do a video on how to pull in tweets, Facebook posts, etc. into Excel so we could do a Sentiment Analysis on them.

  2. How would you visualize all emails on your corporate email? Can that be done using a network charts ??? 🤔🤔🤔

  3. Hi Bill
    Very Informative Video, Please help me to do the conditional formating on below scenario in an easy way,
    Task is to find minimum and maximum value in a row and format those cells in Green and Red colour filled. ( conditional formating )
    This formatting should be copied in ann easy , may be drag & drop to all rows with filler handle( I tried in Excel 2013, to copy formating to all rows by draging with filler handle(+) – with RH click, fill Formating only, but formating not happened.

    Please help.

    Thanks

  4. Thanks for sharing, Bill. Certainly seems that text-based analytics is becoming more in-demand. Great demonstration in Excel, I would not have thought about its capabilities in this area.

  5. hi
    i have formola

    =CONCATENATE("=","VLOO","KUP(D2,A","1:B2,2,0)")
    or
    ="="&"VLOO"&"KUP(D2,A"&"1:B2,2,0)"

    the result is
    =VLOOKUP(D2,A1:B2,2,0)
    but it only text

    ="1"&"+"&"2"
    or
    =CONCATENATE("=","1","+","2")

    the result is
    =1+2
    but it only text

    so how to convert result to be formula not text

  6. Mr. Excel have a case of study for you:

    I have multiple YouTube Channels (13 to be exact) and want to consolidate all data from the analytics module. They allow me to download a workbook for each one, all them have the same strcture but is very difficult to me to have all them in a single workbook.

    It is also possible to make the consolidation dynamic? because I have to make a report every 1st of each month, and is a nightmare for me 🙁

    Thank you in advance.

  7. Hello Bill!

    I use excel on a daily basis to create reports.
    Usually a report is presented in one sheet with 5 pivot tables placed one under another.
    Additionally I use slicers that are connected with all pivots.
    Whenever I use slicers, pivots "shrink" and there is some space created between them.
    I could use some macro that hides/unhides those rows, every time slicers are used.
    I'm not happy with one I am using now.

    Could you help me with this problem or direct me where to look answer?

    Have a great day!

  8. How can I know if there's a double negative appearance? btw, what it is really actually so that the definition can be clear to me.

  9. Error! {"error":{"code":"LibraryExecutionError","message":"Module execution encountered an internal library error.","details":[{"code":"TableSchemaColumnCountMismatch","target":" (AFx Library)","message":"data: The table column count (0) must match the schema column count (1)."}]}} – what should I do with that?

Leave a Reply

Your email address will not be published. Required fields are marked *