Chris Hatzis:
Excel Tips, a CPA Australia podcast.
Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood. And in this episode, I wanted to talk about something called sentiment analysis. We all know that Excel is really good at working with numbers. It's also really good at manipulating text. So you can split text up, you can join text.
But something Excel can't do is understand text. And that's where sentiment analysis comes in. So, sentiment analysis grew out of social media where you had all of these comments and people wanted to figure out whether these were positive comments or negative or neutral. And so sentiment analysis grew out of that. And basically, the idea was to plot the sentiment over time.
So were things becoming more positive or more negative? Excel can't do any sentiment analysis, but Excel has the ability to use Python now. So, Python is a programming language, and it has a library that allows you to perform sentiment analysis. So that means we can actually bring that library into Excel and use it.
Now, I have covered Python in Excel in a series of three articles back in 2025. So feel free to check those out. They're a good introduction to using Python in Excel. And this article is sort of like Python part 4. So I'm showing you a practical application for Python in Excel, and it fills a gap. So, Excel can't understand text, but sentiment analysis can.
Now, where would you use this apart from social media? You can get feedback from employees, from customers. You can get feedback from training, you might get some help desk feedback, and we can now analyse that. So, in the example in the companion video and article, I used some feedback from a webinar that I ran recently, and I highly recommend you check out the video and the article because they go into a lot of depth in the code, how you install it, how you use it. Sentiment analysis doesn't remove the need for someone to read and action the feedback. It just gives you a way to score the feedback.
Okay, so the library that Python uses is called NLTK, which stands for Natural Language Toolkit. And the sentiment analysis has a weird name called VADER, which is Valence Aware Dictionary and sEntiment Reasoner. And it understands social media posts, including things like slang, capitalization, punctuation, and emojis. Some things it doesn't handle very well are sarcasm, irony, maybe some specific jargon. So it's not perfect, but it does give you a good overall score for the sentiment.
Note, sentiment analysis is designed to be used on short pieces of text, not long-form pieces of text. So it is great for analysing feedback from surveys and things like that.
Now, to see if you have Python in Excel, it's really easy. Just check out the Formulas tab in Excel. And if you've got an Insert Python icon, then you do have Python. And so you can use Python in Excel and you can use the NLTK library.
Now, the scoring system that this VADER uses provides us with four separate scores. There's a score for negative, a score for neutral, and a score for positive. And all of those three scores add up to one. So they're all fractions. Then there's a compound score, and that's, like, the overall score, and that's the score that I used in the example.
And so, I showed you in the video how you can apply Python to a single cell that has some text in it, but also how you can apply it to a table, and you can analyse a whole lot of comments in one single cell, and it will create a spill range. And I showed you how to do that. So it gives you a list of the comments as well as the score for the comments, and that's output from Python.
In terms of the score, -1 is totally negative, 0 is neutral, and +1 is totally positive. Then I showed you how you can actually use Excel to then extend that a little bit further. So rather than just coming up with a raw number, I created a lookup table that took the values from -1 through to +1 and allocated them to different descriptions. So I came up with five different descriptions with the values at each level.
Very negative, mainly negative, neutral, mainly positive and very positive. So I was able to use the XLOOKUP function to look up the raw number in the lookup table and return a description, which then gives you another way to analyse the data. And again, you might want to be analysing this over time to see how the sentiment has changed.
And as I mentioned, the Python created a spill range, so that's a range that automatically extends as things are added to it. So I was able to use that spill range and then use Excel's formulas to then basically take that and extend the table and add an extra column to the table for the description. And I used quite a few different functions that are really powerful.
So, again, it's worth checking out the video to see that. I used the LET function, which allows you to use variables in formulas, the DROP function, which allows you to remove a row because I took the heading out of the Python output. XLOOKUP, obviously, that's the best lookup function in Excel. So that allowed us to look up the number and figure out the description. And then I was able to use the HSTACK and the VSTACK.
So HSTACK stands for horizontal stack. So that joins columns together. And the VSTACK, which is a vertical stack, and that joins rows together. So, using HSTACK and VSTACK, I was able to take the Python output and then create a separate spill range table that had the comment, the score, and then the description of very negative, very positive, those sort of things. And so, that, again, allows you to do slightly different analysis instead of just looking at the raw numbers.
So, Python in Excel allows you to access things that Excel can't do natively. Plus, it uses the Python language, which isn't too bad to learn. It's not quite as easy as VBA, the Visual Basic for Applications, which is the Excel macro language. There are a lot of resources on the internet for Python. And I use those resources to actually get a lot of the code that I use in the examples. Also, AI can write Python code. So you can then apply that to Excel as well.
Stay tuned. I will be looking at Excel and AI in the next episode. Exciting times ahead. So, Python in Excel, worth exploring for those areas that Excel can't do and that Python can do. So check out the video and the article. And there's also the series from 2025, which steps you through all of the how to use Python in Excel. Hope you found that useful. Thanks for listening.
Jackie Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
