Clean Your Data Fast by Removing Spaces and Empty Cells in Excel

Spreadsheets often arrive in a state of chaos. Whether you are importing data from a legacy CRM, scraping information from a website, or consolidating manual entries from multiple team members, "blanks" are one of the most persistent issues. However, in Excel, the term "blank" is ambiguous. It could refer to a cell that looks empty but contains hidden spaces, or it could mean an actual empty cell that disrupts your data's structure.

Failing to address these blanks leads to failed VLOOKUPs, incorrect mathematical totals, and messy reports. To clean your data effectively, you must first identify which type of blank you are dealing with. This deep dive covers every professional method to strip unwanted spaces and remove empty cells from your Excel workbooks.

Identifying the Two Types of Blanks in Excel

Before applying a fix, clarify your objective. Most Excel users fall into one of two camps:

Content Cleanup: You have text like " Product A " and you want it to be "Product A". You need to remove leading, trailing, or extra spaces inside the cell.
Structural Cleanup: You have a list where Row 3, Row 7, and Row 12 are completely empty. You want to delete these empty gaps so the remaining data shifts up into a continuous block.

In our experience managing large-scale data migrations, we have found that mixed datasets often contain both issues. A cell might appear empty but actually contains a "non-breaking space" (common in web data), which prevents Excel from recognizing it as a true blank. We will address both scenarios in detail.

Stripping Extra Spaces Inside a Cell Using Formulas

When your cells contain unwanted spaces, formulas are the safest way to clean them because they preserve your original data in a separate column for verification.

Using the TRIM Function for Standard Spaces

The TRIM function is the industry standard for basic text cleaning. It performs three specific actions:

Removes all leading spaces (before the first character).
Removes all trailing spaces (after the last character).
Reduces multiple spaces between words to a single space.

To use it, assume your messy data is in cell A2. In cell B2, enter: =TRIM(A2)

Drag this formula down your entire column. Once the data looks clean, copy column B, right-click column A, and select Paste Special > Values. This replaces the messy data with the cleaned text, allowing you to delete the helper column.

Removing Every Single Space with SUBSTITUTE

Sometimes, TRIM isn't enough. If you are cleaning product codes or phone numbers (e.g., "123 456 789") and you want to remove all spaces to get "123456789", use the SUBSTITUTE function.

The syntax is: =SUBSTITUTE(A2, " ", "")

This tells Excel to find every instance of a space (represented by " ") and replace it with nothing (represented by ""). Unlike TRIM, this leaves no spaces between words or numbers.

Dealing with Invisible Characters and Non-Breaking Spaces

A common frustration occurs when TRIM appears to do nothing. You run the formula, but the leading spaces remain. This usually happens because the "space" is not a standard space (ASCII character 32), but a Non-Breaking Space (HTML entity or ASCII 160).

Identifying the Hidden Culprit

To confirm if you have hidden characters, use the LEN function. If a cell looks like it has 5 characters but =LEN(A2) returns 8, you have three hidden characters. To see exactly what the first character is, use: =CODE(LEFT(A2, 1))

If the result is 160, you are dealing with non-breaking spaces.

The Ultimate Cleaning Formula

To handle both standard spaces, non-breaking spaces, and non-printing characters (like line breaks), we recommend a nested formula approach: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

How it works:

SUBSTITUTE(A2, CHAR(160), " "): Converts all non-breaking spaces into standard spaces.
CLEAN(...): Removes the first 32 non-printing characters in the 7-bit ASCII code (e.g., line breaks).
TRIM(...): Removes the remaining leading, trailing, and extra internal spaces.

Using Find and Replace for Bulk Removal

If you don't want to use helper columns and formulas, Excel's Find and Replace tool is a powerful alternative for destructive cleaning (meaning it changes the source data directly).

Select the range of cells you want to clean.
Press Ctrl + H to open the Find and Replace dialog.
In Find what, press your Spacebar once.
Leave Replace with completely empty.
Click Replace All.

Warning: This method removes all spaces. Do not use this for sentences or names unless you want "John Doe" to become "JohnDoe".

How to Delete Physically Blank Cells in a Range

Now, let’s pivot to structural cleanup. If your dataset has literal holes (empty cells) that make sorting and filtering difficult, you need to remove the cells and shift the remaining data.

The Go To Special Method (Fastest for Small Ranges)

This is the most popular manual method to highlight and delete blanks simultaneously.

Highlight the data range that contains the blank cells.
Press F5 on your keyboard, then click the Special... button at the bottom left.
In the list of options, select Blanks and click OK.
Excel will now have only the empty cells selected. Do not click anywhere else, or you will lose the selection.
Right-click on one of the highlighted gray cells and select Delete....
A dialog box will ask how to move the remaining data. Choose Shift cells up and click OK.

Pro-Tip from our testing: Be careful with this method if your data is organized in rows where a blank in one column doesn't mean the whole row is blank. If you "Shift cells up" only in Column A, your Column A data will no longer align with the correct data in Column B. In such cases, it is better to delete the Entire row.

The Filter Method (Safest for Large Datasets)

When working with thousands of rows, "Go To Special" can sometimes struggle or crash. The Filter method is more stable and allows you to review what you are about to delete.

Select your entire data table (including headers).
Go to the Data tab and click the Filter button.
Click the filter arrow in the header of the column containing blanks.
Uncheck "Select All," scroll to the bottom of the list, and check only (Blanks). Click OK.
Select all the visible (empty) rows by clicking the row numbers on the left.
Right-click the selected row numbers and choose Delete Row.
Go back to the Data tab and click Filter again to clear the filter. Your blank rows are gone, and your data is now contiguous.

Using Modern Excel Functions to Extract Non-Blank Data

If you are using Microsoft 365 or Excel 2021, you can use dynamic arrays to create a cleaned version of your list automatically. This is ideal if you want a "live" cleaned list that updates when you add data to the original messy source.

The FILTER Function

The FILTER function is a game-changer for this task. If your data is in the range A2:A20, use this formula in a new cell: =FILTER(A2:A20, A2:A20 <> "")

This formula looks at the range and returns only the cells that are "not equal to nothing." It "spills" the results downward, creating a perfect list without gaps.

The Traditional INDEX/SMALL Formula (For Older Excel Versions)

If you are on an older version of Excel (2019 or earlier), you don't have the FILTER function. You must use a more complex array formula. In cell B2, enter: =IFERROR(INDEX($A$2:$A$20, SMALL(IF($A$2:$A$20<>"", ROW($A$2:$A$20)-ROW($A$2)+1), ROWS($B$2:B2))), "")

Note: You may need to press Ctrl + Shift + Enter to activate this as an array formula.

Advanced Cleaning with Power Query

For recurring data cleanup tasks—especially those involving millions of rows or external files—Power Query is the professional's choice. It records your cleaning steps so you can "Refresh" the data whenever the source file changes.

Select your data and go to Data > From Table/Range.
Once the Power Query Editor opens, right-click the column header.
Select Transform > Trim to remove leading/trailing spaces.
Select Transform > Clean to remove non-printing characters.
To remove empty rows, click the filter arrow on the column header and uncheck null or (Blank).
Click Close & Load to return the perfectly cleaned data to a new worksheet in Excel.

In our internal workflows, we prefer Power Query because it handles null values and empty strings much more gracefully than standard Excel formulas.

Automation via VBA: The One-Click Solution

If you find yourself performing these steps every single day, a simple VBA macro can save hours of manual clicking. Below is a script that removes all blank cells in a selected range and shifts the remaining data up.