How to Fix Data in Excel

Messed-up data in Excel can bring your analysis to a screeching halt, turning a straightforward task into a frustrating puzzle. Whether you're dealing with extra spaces, weird formatting, or jumbled text, cleaning your data is the first critical step to getting accurate results. This tutorial will walk you through how to identify and fix the most common data problems in Excel, giving you a toolkit of functions and features to clean up any spreadsheet that comes your way.

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

First, Pinpoint the Problem: Common Data Issues in Excel

You can't fix what you don't know is broken. Before diving into solutions, it’s helpful to recognize the typical ways data gets messy. A quick scan of your spreadsheet will often reveal some of these classic data headaches:

• Pesky Extra Spaces: Leading spaces (before the text), trailing spaces (after the text), and double spaces between words are often invisible but can ruin lookups and sorting.
• Inconsistent Formatting: Dates that look like "10/01/2023", "Oct 1, 2023", and "2023-10-01" in the same column are common and make filtering a nightmare. The same goes for numbers (e.g., with and without currency symbols).
• Numbers Stored as Text: That small green triangle in the corner of a cell is a warning sign. When numbers are formatted as text, you can't perform calculations like SUM or AVERAGE on them.
• Mismatched Capitalization: Entries like "Apple", "apple", and "APPLE" are treated as different items by many Excel functions, which can throw off your unique counts and pivot tables.
• Blank Rows and Cells: Empty rows and cells can interrupt formulas, break charts, and give you an inaccurate picture of your dataset's size.
• Duplicate Entries: Duplicate records can inflate your numbers, leading to incorrect sums and skewed reporting. Often happens when combining data from multiple sources.
• Information Jammed in One Column: Data like a full name ("John Smith") or an address ("123 Main St, Anytown") in a single cell can be difficult to use if you need to sort by last name or city.
• Merged Cells: While they might look neat for headings, merged cells are a major obstacle for sorting, filtering, and applying formulas to a range of data.

Your Excel Data Cleaning Toolkit

Excel provides a powerful set of built-in tools designed specifically for cleaning data. Getting familiar with these will save you countless hours of manual editing. Here are the essentials:

• Find and Replace: The simple but mighty solution for mass-correcting typos or standardizing terms. (Shortcut: Ctrl + H).
• Remove Duplicates: A one-click tool on the Data tab that does exactly what it says - identifies and deletes entire rows based on duplicate values in one or more columns.
• Text to Columns: The perfect feature for splitting a single column of data into multiple columns, such as separating a full name into "First Name" and "Last Name".
• Go To Special: A hidden gem that lets you quickly select all cells of a certain type, like all blanks or all cells containing formulas.
• Flash Fill: An almost magical feature that detects a pattern in your data entry and automatically fills the rest of the column for you. For instance, if you start extracting just the first names from a column of full names, Flash Fill will recognize it and finish the job.

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

Key Functions for Data Cleaning

Formulas are your best friend for cleaning operations that require a bit more precision. Here are the core functions to know:

• =TRIM(text): Removes all leading, trailing, and double spaces from text.
• =CLEAN(text): Removes non-printable characters, which are often imported from other systems.
• =UPPER(text), =LOWER(text), =PROPER(text): Changes text to all uppercase, all lowercase, or proper case (capitalizing the first letter of each word).
• = or =CONCATENATE(text1, text2,...): Combines text from two or more cells into one.
• =VALUE(text): Converts a number that is stored as text into an actual number.
• =TEXT(value, format_text): Converts a value to text in a specific number format (e.g., formatting dates consistently).

Step-by-Step Fixes for Common Excel Data Headaches

Now let's put these tools into action. Here’s a walkthrough on how to tackle the most frequent data cleaning tasks.

1. Removing Unwanted Spaces with TRIM

Extra spaces are a silent killer of accurate data analysis. The TRIM function is the best way to get rid of them.

Imagine your data is in column A:

1. Insert a temporary "helper column" next to your messy data (e.g., in column B).
2. In the first cell of the helper column (B2), type the formula: =TRIM(A2)
3. Click the small square at the bottom-right of cell B2 (the fill handle) and drag it down to apply the formula to all rows.
4. Your nice, clean data is now in column B. To make it permanent, highlight column B, copy it (Ctrl + C), then right-click on the original column A and select Paste Special > Values. This replaces the old data with the clean data, removing the formulas in the process.
5. You can now delete the helper column (B).

2. Correcting Numbers Stored as Text

If Excel isn't performing calculations correctly, check for numbers formatted as text. You'll often see a green triangle in the top-left of the cell.

Method 1: The Error Checking Button

1. Select the cell or range of cells with the green triangle.
2. A small yellow diamond with an exclamation point will appear. Click on it.
3. From the dropdown menu, select Convert to Number. Excel will immediately reformat the cells.

Method 2: Using the VALUE Function

If the error button method doesn't work or you want to use a formulaic approach, use the VALUE function. The steps are similar to using TRIM:

1. Create a helper column.
2. In the first cell, enter the formula: =VALUE(A2)
3. Drag the formula down.
4. Copy the helper column and Paste Special > Values 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

3. Standardizing Capitalization

Inconsistent casing makes filtering and summarizing data difficult. Fix it easily with Excel's text functions.

• To make everything "Title Case," use: =PROPER(A2)
• To make everything "UPPERCASE," use: =UPPER(A2)
• To make everything "lowercase," use: =LOWER(A2)

Just like with TRIM, use a helper column for your formula, drag it down, and then copy and paste the values back over the original column.

4. Getting Rid of Blank Rows and Cells

Empty rows can disrupt sorting and filtering. The "Go To Special" command makes quick work of them.

1. Highlight the entire range where you suspect blank cells exist.
2. Go to the Home tab, click on Find & Select, and choose Go To Special...
3. In the dialog box, select Blanks and click OK. Excel will now have all the empty cells in your range highlighted.
4. Right-click on any of the selected blank cells and choose Delete...
5. A new box will appear. Here, you can choose to Shift cells up (if deleting specific cells) or Entire row (if deleting entire blank rows). Be careful with this and ensure it won't misalign your data.

5. Handling Duplicates Once and For All

Excel’s built-in Remove Duplicates tool is fantastically simple and powerful.

1. Select the data you want to de-duplicate. It's often best to highlight the entire table.
2. Go to the Data tab and click Remove Duplicates.
3. A dialog box will pop up, listing all the columns in your selected data. Check the boxes for the columns you want Excel to use for identifying duplicates. For example, checking only the "Email" column will remove any row where the email address is repeated.
4. Click OK. Excel will tell you how many duplicate values were found and removed.

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

6. Splitting and Combining Column Data

Splitting with Text to Columns

Have "First Name Last Name" stuck in one column? Fix it with Text to Columns.

1. Select the column you want to split. Important: Ensure there are empty columns to the right, as this process will overwrite any existing data.
2. Go to the Data tab and click Text to Columns.
3. Choose Delimited because your data is separated by a character (like a space or comma) and click Next.
4. Select the delimiter that separates your data. For "John Smith," you would check the Space box. You'll see a preview of how the data will be split. Click Next.
5. Confirm the destination where the new columns will be placed and click Finish.

Combining with the Ampersand (&)

Have "First Name" in column A and "Last Name" in column B? Let's join them.

1. Create a helper column where you want the combined name.
2. In the first cell, type the formula: =A2&" "&B2
3. Drag the formula down, then copy and paste the helper column as values.

Final Thoughts

Becoming proficient at cleaning data in Excel is less about memorizing every button and more about knowing what to look for and which handful of tools to reach for. Mastering features like TRIM, Find and Replace, and Remove Duplicates saves hours of mind-numbing manual work and ensures your final reports and analyses are built on a solid, accurate foundation.

Of course, while these Excel skills are invaluable, the routine of downloading CSVs and repeating the same cleaning steps every week is exhausting. We built Graphed to automate that entire repetitive process. By connecting directly to your marketing and sales platforms, we pull in your data and keep it refreshed in live, interactive dashboards, so you can spend less time cleaning rows and more time discovering insights that actually grow your business.