How to Do Sentiment Analysis in Excel

Transforming a long list of customer reviews or survey responses into clear insights can feel overwhelming, but you already have a powerful tool for the job: Microsoft Excel. This article will show you two effective methods for performing sentiment analysis directly within your spreadsheets, helping you understand what your customers are really saying.

What is Sentiment Analysis (and Why Do It in Excel?)

Sentiment analysis is the process of identifying and categorizing opinions expressed in a piece of text to determine whether the writer's attitude towards a particular topic, product, service, etc., is positive, negative, or neutral. For businesses, this is an incredibly valuable way to tap into the voice of the customer.

Think about these common scenarios:

• Analyzing hundreds of product reviews from your e-commerce site to see if a recent change was well-received.
• Gauging public reaction to a marketing campaign by looking at social media comments.
• Sorting through open-ended responses in a customer satisfaction survey to find common complaints or sources of delight.

While specialized software exists for this, Excel is a fantastic starting point. It's accessible, familiar to most business users, and perfectly capable of handling small to medium-sized datasets. Learning to do this in Excel helps you understand the core mechanics of sentiment analysis without a steep learning curve or high costs.

Method 1: The Do-It-Yourself Keyword-Based Approach

This is the most direct and hands-on way to conduct sentiment analysis in Excel. The logic is simple: we'll create lists of positive and negative words, count how many times they appear in each piece of text, and then calculate a score to determine the overall sentiment.

Step 1: Set Up Your Spreadsheet

First, organize your data. You’ll need one column for your text and several columns for your analysis. Let’s create a simple structure:

• Column A (Text): Paste your raw text data here (e.g., customer reviews, feedback).
• Column B (Positive Score): Where we will count positive words.
• Column C (Negative Score): Where we will count negative words.
• Column D (Overall Score): The difference between the positive and negative scores.
• Column E (Sentiment): A clean label: "Positive," "Negative," or "Neutral."

Step 2: Create Your "Positive" and "Negative" Keyword Dictionaries

The accuracy of this method depends entirely on the quality of your keyword lists. On a new sheet (let's call it "Keywords"), or in columns far away from your main data (e.g., columns J and K), create two lists:

• Positive Keywords: love, amazing, excellent, great, perfect, wonderful, happy, satisfied, easy, best
• Negative Keywords: hate, terrible, bad, awful, poor, disappointed, broken, difficult, worst, problem

Pro Tip: Keep these lists growing! The more relevant words you add, the more nuanced your analysis will be. You can find extensive sentiment word lists (sometimes called lexicons) with a quick online search.

For this tutorial, let’s assume your positive words are in Keywords!A1:A10 and your negative words are in Keywords!B1:B10. Next, create two Named Ranges to make our formulas much cleaner:

1. Select your list of positive words (Keywords!A1:A10).
2. Go to the Formulas tab, click Define Name.
3. Enter the name PositiveWords and click OK.
4. Repeat the process for your negative words, naming the range NegativeWords.

Step 3: Count Your Keywords with Formulas

Now we'll use a clever formula to search each piece of text for every word in our dictionary lists. Click into cell B2 (your first "Positive Score") and enter this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(PositiveWords, A2)))

Let's break that down:

• SEARCH(PositiveWords, A2) tries to find each word from your PositiveWords named range inside the text in cell A2. It's case-insensitive, which is perfect for this. If a word is found, it returns the character position, if not, it returns an error.
• ISNUMBER(...) checks the result. It turns the character positions into TRUE (because it’s a number) and the errors into FALSE.
• The double dash -- is a classic Excel trick that converts TRUE values into the number 1 and FALSE values into 0.
• SUMPRODUCT(...) then adds up all the 1s and 0s to give you a total count of how many positive keywords were found.

Next, do the same for the negative score. Click into cell C2 and enter:

=SUMPRODUCT(--ISNUMBER(SEARCH(NegativeWords, A2)))

Step 4: Calculate the Final Score and Assign a Label

This is the easy part. In cell D2, subtract the negative score from the positive one:

=B2-C2

This gives you an "Overall Score." A positive number suggests positive sentiment, a negative number suggests negative sentiment, and zero suggests neutrality (or that no keywords were found).

To make this score easier to interpret, let's add a clear text label. In cell E2, enter this classic nested IF formula:

=IF(D2>0, "Positive", IF(D2<0, "Negative", "Neutral"))

Now, simply select cells B2 through E2 and drag the fill handle (the small square in the bottom-right corner) down to apply these formulas to all your rows of text.

Method 2: Level Up with AI-Powered Add-ins

The keyword-based method is fantastic for getting started, but it has limitations. It can't easily understand context, sarcasm, or negation (e.g., the phrase "not bad" contains a negative word but expresses a positive sentiment). When you need more sophistication, you can bring the power of AI into your spreadsheets with Excel Add-ins.

Several add-ins connect Excel to robust machine learning models that are purpose-built for text analysis. These tools don't just count keywords—they analyze sentence structure and relationships between words to determine sentiment with much higher accuracy.

How to Find and Use a Sentiment Analysis Add-in

You can find third-party services in the Excel Add-in store. Here’s the general process:

1. Go to the Insert tab on the Excel ribbon.
2. Click on Get Add-ins.
3. In the Office Add-ins store, search for terms like "sentiment analysis," "text analysis," or "AI."
4. Review the options. Many offer free trials but may require a subscription for continued use. Read the descriptions and reviews to find one that fits your needs.
5. Once you've added one, it will typically provide a new custom function. You might be able to simply type a formula like =Sentiment(A2) into a cell, and the add-in will send your text to its AI model and return a "Positive," "Negative," or "Neutral" label.

The primary benefit here is accuracy and speed. You bypass the need to build and maintain keyword lists, and you get a more intelligent analysis of your text. The tradeoff is that these services usually come with a cost and require an internet connection to work.

Visualize Your Results

Once you've categorized your text, the final step is to visualize the results to make them easy to understand. Visuals are far more impactful than a table of numbers for sharing insights with your team.

First, create a summary table. Find an empty spot on your sheet and list the three sentiment categories: Positive, Negative, and Neutral. Beside them, use the COUNTIF formula to tally your results:

• Next to "Positive": =COUNTIF(E:E, "Positive")
• Next to "Negative": =COUNTIF(E:E, "Negative")
• Next to "Neutral": =COUNTIF(E:E, "Neutral")

Now, select your small summary table and go to the Insert tab. Create a simple Pie Chart or Bar Chart. In just a few clicks, you'll have a clean, glanceable summary of your customer sentiment that’s perfect for reports and presentations.

Final Thoughts

Whether you're using simple formulas or leveraging a powerful AI add-in, Excel is a surprisingly effective tool for turning raw text comments into structured, measurable business insights. You can quickly see whether customer feedback is trending positive or negative, giving you the data you need to make better decisions.

Manually creating keyword lists and formulas in Excel is a great way to learn, but it can quickly become cumbersome when you’re pulling feedback from multiple platforms or dealing with thousands of entries. This manual data gathering and spreadsheet wrangling is precisely the drain on time and energy that slows teams down.

That's why we built Graphed to help. Instead of exporting CSVs and wiring up complex formulas, we connect directly to your data sources—like Shopify for reviews, HubSpot for survey responses, Salesforce for customer notes, or even raw Google Sheets. Our AI can automatically handle sentiment analysis in real time. You can simply ask, "What's the sentiment of our product feedback this month?" and get an instant, sharable dashboard. It removes the setup friction so you can focus on making data-driven decisions, not building reports.