How to Scrub Data in Excel

Wrestling with a messy spreadsheet is a universal experience. One column has names in all caps, another has annoying extra spaces, and duplicates are hiding everywhere, silently wrecking your totals. This is where data scrubbing comes in. This guide will walk you through the most essential data cleaning techniques in Excel, transforming your messy files into a reliable source for clear analysis and reporting.

Graphed

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.

Build Your First Agent

What is Data Scrubbing?

Data scrubbing, or data cleaning, is the process of finding and fixing errors, inconsistencies, and inaccuracies in a dataset. Think of it like washing fresh vegetables before you start cooking. You’re removing all the dirt (bad data) so that the final dish (your report or analysis) is perfect.

Why bother? Clean data leads to:

• Accurate Reports: If you have duplicate sales records, your revenue numbers will be inflated. Cleaning the data ensures your summaries, pivot tables, and charts reflect reality.
• Reliable Analysis: Trying to analyze customer data with fields like "USA," "U.S.," and "United States" is impossible. Standardizing them allows you to group and compare data correctly.
• Fewer Errors: Clean data prevents failures in other systems. For example, scrubbing your email list of typos and extra spaces helps reduce bounce rates for your marketing campaigns.

In short, spending a little time cleaning your data upfront saves you hours of headaches and bad decisions down the line.

7 Essential Data Scrubbing Techniques in Excel

Let's get into the step-by-step methods for tackling the most common data messes. For best results, always work on a copy of your original file. You can do this by right-clicking on the worksheet tab and selecting "Move or Copy," then checking the "Create a copy" box.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

1. Removing Duplicate Rows

Duplicate entries are one of the most common issues, especially when combining data from multiple sources. They can inflate your counts, metrics, and give a false impression of your data's volume.

How to fix it: Use Excel’s built-in "Remove Duplicates" tool.

1. Click anywhere inside your dataset.
2. Go to the Data tab on the Ribbon.
3. In the "Data Tools" group, click Remove Duplicates.
4. A dialog box will appear, showing all the columns in your data. It's often best to leave all columns checked to ensure you are only removing rows that are identical across the board. If you only want to check for duplicates in a specific column (like an email address), you can unselect the others.
5. Click OK. Excel will tell you how many duplicate values were found and removed, and how many unique values remain.

This is the quickest way to get a clean list of unique records, whether it's contacts, orders, or product SKUs.

2. Cleaning Extra Spaces with the TRIM Function

Hidden spaces are the silent killer of data accuracy. A leading space before a name (" John") or a trailing space after it ("John ") will make Excel see it as a completely different value than just "John". This often causes VLOOKUP formulas to fail and messes up sorting and filtering.

How to fix it: Use the TRIM function.

The TRIM function removes all extra spaces from text, leaving only single spaces between words.

1. Insert a new temporary column next to the column you want to clean (e.g., if your names are in column A, insert a new column B).
2. In the first cell of your new column (e.g., B2), type the formula:

=TRIM(A2)

1. Press Enter. You'll see the cleaned-up version of the text from A2.
2. Click on cell B2 again, and then double-click the small square (the "fill handle") at the bottom-right corner of the cell. This will automatically copy the formula all the way down your list.
3. Finally, you need to replace the old, messy data. Select the entire new column (B), copy it (Ctrl+C), then right-click on the header of the original column (A) and select Paste Special > Values. This pastes the clean text without the formula. You can now delete your temporary column.

3. Fixing Inconsistent Text Casing (PROPER, UPPER, LOWER)

Inconsistent capitalization—like "Nike," "NIKE," and "nike" in the same column—can prevent you from properly grouping data in a pivot table. It looks unprofessional and makes your data harder to read.

How to fix it: Use Excel's text functions.

• =PROPER(A2): Converts text to "Proper Case," where the first letter of each word is capitalized (e.g., "new york city" becomes "New York City"). This is perfect for names, cities, and titles.
• =UPPER(A2): Converts all text to "UPPERCASE." Useful for things like state abbreviations or SKUs.
• =LOWER(A2): Converts all text to "lowercase." Ideal for email addresses or when a uniform lowercase format is needed.

The process is the same as with the TRIM function: create a helper column, enter your chosen formula, drag it down, and then copy and paste the values back over the original column.

Graphed

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.

Build Your First Agent

4. Splitting One Column into Many (Text to Columns)

It's incredibly common to get data where multiple pieces of information are jammed into a single cell, like a "Full Name" column you need to separate into "First Name" and "Last Name".

How to fix it: Use the "Text to Columns" feature.

1. Select the column of data you want to split.
2. Go to the Data tab and click Text to Columns. A Wizard will pop up.
3. Choose Delimited if your data is separated by a consistent character like a space, comma, or semicolon. This is the most common choice. Click Next.
4. On the next screen, check the box for the delimiter your data uses. If splitting "John Smith," you would check "Space." You'll see a preview of how the data will be split. Click Next.
5. On the final screen, you can specify the data format for each new column, but the "General" setting usually works fine. Choose a destination for where you want the new split data to appear. It's safest to pick a blank column to the right of your data.
6. Click Finish. Your data is now neatly separated into new columns.

5. Standardizing Categories with Find and Replace

Your "Country" column is a mess: "USA," "U.S.A.," "US," and "United States" all refer to the same place but are treated as different categories by Excel. This will completely skew any summary report by country.

How to fix it: Use "Find and Replace."

1. Select the column you want to clean up.
2. Press Ctrl+H (or go to Home > Find & Select > Replace).
3. In the "Find what" box, type the term you want to get rid of (e.g., "US").
4. In the "Replace with" box, type the standardized term you want to use (e.g., "United States").
5. Click Replace All. Excel will swap out all instances instantly.
6. Repeat this process for other variations like "U.S.A." until your column is completely uniform. Use the filter dropdown on the column to quickly check for any remaining variations.

This is incredibly effective for standardizing categories, correcting common misspellings, or updating product names.

6. Dealing with Blank Cells

Blank cells can break formulas, misrepresent averages, and create gaps in charts. How you handle them depends on what the blank means— is it a zero, or is the data genuinely missing?

How to fill or find all blank cells:

1. Select the range of data where you want to find the blanks.
2. Press F5 to open the "Go To" box, then click Special....
3. In the "Go To Special" dialog, select Blanks and click OK.
4. Excel has now selected every empty cell in your range. Don't click anywhere else. All the blank cells are active, with one being the primary active cell.
5. Now you can take action. To fill them all with "0" or "N/A," simply type that value and press Ctrl+Enter instead of just Enter. This will fill every selected blank cell with what you typed.

This trick is a massive time-saver for filling in missing data in a large dataset.

AI Agents for Marketing

Crash Course

Graphed

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.

Download the PDF

7. Converting Text That Looks Like Numbers

Sometimes numbers get imported into Excel as text. You can spot these because they often align to the left of the cell instead of the right. When this happens, you can't perform any mathematical calculations like SUM or AVERAGE on them.

How to fix it: Use the "Convert to Number" error helper.

1. Select the column of text-formatted numbers. You might see a little green triangle in the top-left corner of the cells, along with an error icon.
2. Click the error icon (a yellow diamond with an exclamation point).
3. From the dropdown menu that appears, simply choose Convert to Number.

Excel will instantly convert the entire selection into proper numeric values that you can now use in calculations.

Final Thoughts

Scrubbing data in Excel might not be the most glamorous task, but it’s a foundational skill for anyone who works with data. By using tools like Remove Duplicates, the TRIM function, and Find and Replace, you can bring order to chaotic spreadsheets and feel confident in the accuracy of your reports and analysis.

While mastering Excel is powerful, we know that spending hours exporting CSVs, performing manual cleaning steps, and updating reports every week is a major drain on your time. At Graphed we automate this entire process by connecting directly to your live data sources like Google Analytics, Shopify, and Salesforce. Your data is always clean, current, and ready for analysis, allowing you to generate real-time dashboards and get answers simply by asking questions in plain English—no manual scrubbing required.