Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Clean Data for Sentiment Analysis in Excel

Cody Schneider••7 min read

Before you can figure out what customers really think, you need to clean up what they actually wrote. Performing sentiment analysis on raw text data like reviews or survey responses without first tidying it up is a recipe for inaccurate results. This guide will walk you through a practical, step-by-step process for cleaning text data directly in Excel, preparing it for a much more reliable analysis.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Does Data Cleaning Matter for Sentiment Analysis?

Sentiment analysis aims to determine the emotional tone behind a piece of text - is it positive, negative, or neutral? Whether you're using a sophisticated model or a simple keyword-based approach, the saying "garbage in, garbage out" is especially true here.

Imagine analyzing customer reviews for a new product. Your raw data is likely filled with inconsistencies that can confuse any analysis:

  • Typos and misspellings ("greattt prodct")
  • Inconsistent capitalization ("Love it," "love it," "LOVE IT")
  • Extra spaces and punctuation (" Awesome!!! :) ")
  • Irrelevant "stop words" like "the," "a," and "is" that add noise

An analysis tool might see "Love" and "love" as two different words, or get thrown off by punctuation. Cleaning your data standardizes the text, removing this noise so you can focus on the words that actually carry sentiment. Taking these extra steps ensures your conclusions about customer sentiment are based on a solid foundation, not messy data.

Your Data Cleaning Checklist for Excel

We'll tackle the most common data cleaning tasks needed for sentiment analysis. It might look like a long list, but we'll go through each job one by one using simple Excel features and formulas. Here’s our plan:

  1. Create a working copy of your data.
  2. Remove duplicate entries.
  3. Trim leading and trailing whitespace.
  4. Convert all text to a single case (lowercase).
  5. Remove punctuation and special characters.
  6. Get rid of common "stop words."
  7. Correct common and obvious typos.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

A Step-by-Step Guide to Cleaning Text in Excel

Let’s work with a small, familiar dataset: customer feedback. Imagine you’ve exported a list of comments and you need to prepare them for analysis. Our raw data is in Column A.

Example Data:

AWESOMEE product!!! :) The shipping was too slow, unfortunately. i really liked it AWESOMEE product!!! :) great value.. i am a fan Not Bad

Step 1: Create a Safe Working Copy

This is the most critical first step. Never work directly on your original data file. Always make a copy. This gives you a backup to return to if a formula goes wrong or you accidentally delete the wrong thing. You can either copy the entire file or just copy the raw data into a new worksheet within the same workbook. We’ll call our new sheet "Data Cleaning."

Step 2: Remove Duplicate Entries

Duplicate entries can skew your results, giving more weight to a single piece of feedback than it deserves. Excel has a simple, built-in tool for this.

  1. Select the column that contains your text data (in our case, Column A).
  2. Go to the Data tab on the ribbon.
  3. Click on Remove Duplicates.
  4. A small pop-up will appear. Ensure the correct column is ticked and click OK.

Excel will tell you how many duplicate values it found and removed. In our example, it will find and remove one instance of "AWESOMEE product!!! : )."

Step 3: Trim Extra Spaces with the TRIM Function

Unseen spaces at the beginning or end of text (leading or trailing whitespace) and double spaces between words can cause issues. The TRIM function is built specifically to solve this.

  1. In a new column (let's use Column B), click into the first cell (B2, assuming you have a header in B1).
  2. Type the following formula and press Enter:
=TRIM(A2)

This formula takes the text from cell A2 and removes any extra spaces. You can then click the small square in the bottom-right corner of cell B2 (the fill handle) and drag it down to apply this formula to all your rows.

Example: Before: "Not Bad " After: "Not Bad"

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Step 4: Standardize to Lowercase using the LOWER Function

Computers see "Awesome," "awesome," and "AWESOME" as three distinct words. To make sure all variations are treated as one, we'll convert everything to lowercase. This is a critical standardization step.

  1. Click into the next empty column (Column C, cell C2).
  2. This time, we will apply the LOWER function to our already trimmed text in Column B. This is called nesting or chaining formulas. We take the output of one step and use it as the input for the next.
  3. Type this formula and press Enter:
=LOWER(B2)

Now, drag the fill handle down to apply the formula to the entire dataset. All text will be in lowercase.

Example: Before: "AWESOMEE product!!! : )" After: "awesomee product!!! : )"

Step 5: Ditch Punctuation and Special Characters with SUBSTITUTE

Punctuation marks like periods, commas, and exclamation points don't typically carry much sentiment on their own and can break up words we want to analyze. The SUBSTITUTE function is great for finding and replacing specific bits of text.

The syntax is: SUBSTITUTE(text, old_text, new_text). We want to find punctuation marks (old_text) and replace them with nothing (new_text, which we represent as "").

To remove multiple types of punctuation, we need to nest several SUBSTITUTE functions together.

  1. Go to the next empty column (Column D, cell D2).
  2. Let’s start by removing just the exclamation points. Our formula applies to the lowercase text in Column C:
=SUBSTITUTE(C2,"!","")
  1. To remove periods as well, wrap another SUBSTITUTE around the first one:
=SUBSTITUTE(SUBSTITUTE(C2,"!",""),".","")
  1. For a more comprehensive formula to remove several common characters, nest more SUBSTITUTE functions:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"!",""),".",""),",",""),":)","")

Apply this formula down through your column. Your text is starting to look much cleaner and more uniform.

Example: Before: "great value.. i am a fan" After: "great value i am a fan"

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Step 6: Handle Stop Words

Stop words are common words - like "the," "is," "a," "in," "it" - that add little analytical value. Removing them helps you focus on the more meaningful words in the text. This is a more advanced task in Excel, but we can use the same SUBSTITUTE technique we just learned.

Note: When removing a word like "a," search for " a " (with spaces) to avoid accidentally changing "apple" to "pple."

  1. Create a list of stop words you want to remove. For this example, use: the, was, to, i, am, a.
  2. In the next empty column (E, cell E2), build another nested SUBSTITUTE formula, pointing to your data in Column D:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"the "," "),"was "," "),"to "," "),"i "," "),"am "," "),"a "," "))

This wraps the entire formula in a TRIM to clean up double spaces that might result when removing words.

Example: Before: "the shipping was too slow unfortunately" After: "shipping too slow unfortunately"

Step 7: Correct Common Typos with Find & Replace

For misspellings, automated correction can be difficult. The most practical approach in Excel is to identify common, recurring typos in your dataset and use the Find and Replace tool to fix them manually.

  1. First, copy your "Cleaned Text" column (let's say Column F) and paste as values (Paste Special > Values) to lock in the cleaned results.
  2. Scan the column for recurring typos. For example, "AWESOMEE" is spelled with two 'E's.
  3. Select the column.
  4. Press Ctrl + H (or Cmd + H on Mac) to open the Find and Replace dialog box.
  5. Find what: awesomee
  6. Replace with: awesome
  7. Click Replace All.

This method works best for common mistakes. Fix only the frequent typos that could impact your analysis.

Final Thoughts

By following these steps, you've taken a messy, raw dataset and transformed it into a standardized, clean list of text ready for meaningful sentiment analysis. This process ensures your analysis is focused on the actual substance of the feedback, leading to insights you can trust and act on.

While Excel is a powerful tool for manual data prep, the process can become tedious for large datasets or multiple sources. We designed Graphed to eliminate this friction. Instead of formulas, you can connect data sources—like Shopify reviews or HubSpot surveys—and ask in plain English, "Show me the sentiment trend for product reviews this month." Our platform handles connecting, cleaning, and visualizing automatically, turning hours of data wrangling into a 30-second task.

Related Articles