To install the add-in, go to the Insert tab in Excel 2013. Choose the Store icon from the Apps group. In the Store dialog, search for Azure Machine Learning. You will see an app called Azure Machine Learning from the AML Team. Choose to install that add-in. Click the green Trust It button.
For the Input range, select a column that contains text entries. For the output range, select a blank section of the worksheet next to your text entries. The add-in provides several columns of results, so be sure that several columns are blank. Otherwise, the add-in will overwrite existing data.
If you have a small number of cells, you can use the Predict button. If there are thousands of rows, however, you should open the Predict dropdown and choose “As a batch (Batch).”
Click the Predict button (see Figure 1). A scientific beaker animation will appear, with animated bubbles rising from the beaker. Take a walk or get a cup of coffee, as this can take a few minutes. Eventually, two new columns will appear. The first column categorizes each phrase as positive, neutral, or negative. The second column gives each cell a score ranging from 0 to 1. Scores such as 0.949446 are very likely to be positive. Scores such as 0.001647 are very likely to be negative (see Figure 2).
TWO METHODS FOR SENTIMENT ANALYSIS
In the past, sentiment analysis required a human-supervised learning algorithm. If you have 5,000 statements, you would manually go through a sample of 200 statements and choose the positive and negative words and phrases. You’re essentially building a dictionary of positive and negative words. The process takes a long time. Unfortunately, if you train your sentiment analysis tool based on sentences about car repair, it will be very inaccurate if you try to use the tool to analyze sentences about carpet cleaning.
The Excel add-in uses the 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.
This free tool does have some limitations. One known weakness with the method is a sentence that contains a double negative. While the writer might be trying to say something positive with one negative word negating the other (“I don’t hate the feature”), the sentiment analysis will see this as something that’s very negative.
Despite the shortcomings, however, the Sentiment Analysis Tool from Microsoft Azure Machine Learning at least gives you a starting point for getting a sense if most of your comments are positive or negative. In the past, I have frequently ignored columns of long text because I had no way to begin analyzing thousands of sentences. After all, no human can take the time to read through thousands of comments. This tool provides a quick analysis of how many are likely positive or negative.
HIGHLIGHTING POSITIVE ROWS
The colors in Figure 2 aren’t automatically applied by the add-in. I used conditional formatting to apply the colors to the whole row. Coloring cells A2, B2, and C2 based on a value in B2 requires a specialized form of conditional formatting.
Follow these steps to make the positive statements green:
- Select all of your data.
- Use Home, Conditional Formatting, New Rule, Use a Formula to Determine Which Rows to Format.
- The formula needs to use a single dollar sign before column B, since column B is the column that might contain the word “positive.” Don’t put a dollar sign before the 2. The formula should be =$B2= “positive”.
- Click the Format button. In the Format Cells dialog, choose the Font tab and select a green color for the text. Click OK to close the Format Cells dialog and then OK again to create the new rule.
Repeat the steps to build a second rule to color the negative rows in orange. In step 3, the formula would be =$B2=“negative”. The Conditional Formatting Rules Manager should look like Figure 3.
WHAT'S THE APP STORE IN EXCEL?
When introduced in Excel 2013, the App Store offered lightweight apps that weren’t allowed to write to the Excel spreadsheet. That’s now changed, and there are some interesting apps available. Take some time to scroll through the free apps to extend functionality in Excel.
SF SAYS
Unlike traditional VBA macros, the apps in the App Store use JavaScript.
May 2016
