Author: CleanSheetHQ Editorial Team

Go To Special > Blanks > Delete Entire Row is the shortcut most people reach for first, but it selects blank cells — not rows that are completely empty. If your range contains partially filled records, that shortcut can delete rows you meant to keep. If another formula explicitly references one of the deleted rows, you can also end up with a #REF! error.

This guide shows why the shortcut is risky and walks through a safer method that deletes only rows that are fully blank.

Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. Both the shortcut and the safer method below use only long-standing Excel features, so the behavior is the same across every version in this list.

The Standard Shortcut (and Why It’s Risky)

1. Select the data range you intend to clean.
2. Press Ctrl+G, click Special, choose Blanks, then click OK.
3. Excel selects every blank cell in that range.
4. If you then choose Delete > Entire Row, every row that contains one of those selected blank cells gets deleted — not just rows that are entirely empty.

That last step is the trap: a row with just one missing field looks nothing like a “blank row” to the person cleaning the data, but Excel treats it the same way once any cell in it is selected as blank.

Why Partially Blank Rows Can Disappear

Suppose a customer list contains:

• a row that is completely blank,
• a record for Bob with a missing phone number,
• a record for Dave with a missing email address.

Bob and Dave are not blank records — they are incomplete records. But Go To Special > Blanks selects Bob’s empty phone cell and Dave’s empty email cell right alongside the cells in the truly blank row. Delete entire rows from that selection, and Bob and Dave can be removed along with the actual blank rows.

That’s a data-loss problem on its own, before any formula is even involved.

If your report prints a group label only on the first row, see our guide on filling blank cells with the value above.

When #REF! Shows Up

Microsoft documents #REF! as the error Excel shows when a formula’s cell reference is no longer valid — most often because the referenced cells, rows, or columns were deleted. Deleting rows doesn’t automatically break every formula in a sheet; Excel adjusts many range references on its own. The failure case is clearest when a formula explicitly names a row that then gets deleted by the blanks shortcut. In that situation, the formula returns #REF! because the exact cell it pointed to no longer exists.

If you catch the mistake right away, Ctrl+Z is the fastest way to recover before doing anything else.

The Safer Method: Delete Only Fully Blank Rows

Add a helper column that counts how many non-empty cells each record has, using COUNTA.

For a three-column dataset in columns A:C, starting at row 4:

=COUNTA(A4:C4)

Fill the formula down the full range. Then read the result:

• 0 — the row is completely blank across the checked columns.
• 1 or more — at least one field has data, so the row is incomplete, not blank, and shouldn’t be deleted by this pass.

Now clean up safely:

1. Filter the helper column to 0.
2. Delete only the visible, fully-blank rows.
3. Clear the filter.
4. Remove the helper column once you’re done.

This keeps Bob’s and Dave’s partially filled records intact while still clearing out the rows that are genuinely empty.

Formula Design Also Reduces the Risk

Beyond the helper-column cleanup itself, how you write formulas in the first place affects how much a future row deletion can break. A continuous range adjusts automatically when rows inside it are removed; a formula built from a list of individual cell references does not adjust the same way. For example:

=SUM(B2:D2)

is generally more resilient to structural edits than:

=SUM(B2,C2,D2)

This is a separate safeguard from the COUNTA helper — the helper stops you from deleting the wrong rows in the first place, while range-based formulas reduce how much damage a future structural change can cause even when it does happen.

One-line Summary

Go To Special > Blanks selects blank cells, so deleting entire rows from that selection can remove partially filled records along with truly empty ones — and can break any formula that explicitly references a deleted row with #REF!. If you only want rows that are completely empty, filter on a COUNTA(...)=0 helper column first.

Sources

• Find and select cells that meet specific conditions in Excel — Microsoft Support. Documents the Go To Special > Blanks behavior this article builds on.
• How to correct a #REF! error — Microsoft Support. Backs the explanation of when and why #REF! appears after a deletion.
• COUNTA function — Microsoft Support. Reference for the COUNTA helper-column method.
• Range-vs-list formula resilience and the exact #REF! reproduction (a formula explicitly referencing a row deleted by the Blanks shortcut) were confirmed by this project’s own reproduction in Excel, recorded in the Pilot G entry of the project decision log and this article’s QA package — not solely from the official pages above.

If a column of numbers is left-aligned, shows a small green triangle in the corner of each cell, or calculations such as SUM ignore the values, Excel may be treating those values as text rather than numbers. This is common after importing data from CSV files, databases, or copy-pasting from a website.

Here are four ways to fix it in bulk, plus a hidden-character case that can make a value look like it’s still text after you’ve tried one of them.

Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. All four methods below use only long-standing Excel features (no new dynamic-array functions), so they work the same way across every version in this list.

Method 1: Error Checking Button (fastest when available)

If Excel’s background error checking is on, cells with text-stored numbers can show a small green triangle in the top-left corner.

1. Select the cells you want to convert.
2. Click the warning icon that appears next to the selection.
3. Choose Convert to Number.

If you do not see the warning icon, background error checking may be disabled under File > Options > Formulas > Enable background error checking.

Method 2: Paste Special → Multiply

This method works even when the error-checking indicator is unavailable:

1. Type 1 in an empty cell.
2. Copy that cell.
3. Select the range containing numbers stored as text.
4. Open Paste Special.
5. Choose Multiply under Operation, then click OK.

Multiplying by 1 forces text-formatted numeric values to become numeric values while leaving their displayed numbers unchanged.

Method 3: VALUE() Helper Column

For mixed or messy imports, a helper column makes the conversion visible and easier to audit:

1. In a new column, enter =VALUE(A2) and adjust the reference as needed.
2. Fill the formula down.
3. Copy the converted results.
4. Use Paste Special > Values if you want to replace the original text values.
5. Remove the helper column when finished.

VALUE converts text that Excel recognizes as a valid number, date, or time into a numeric value.

Method 4: Text to Columns

Text to Columns can also cause Excel to reinterpret simple numeric text using the General column format.

1. Select the column containing the text-stored numbers.
2. Go to Data > Text to Columns.
3. Keep the column format as General.
4. Finish the wizard.

Use this method carefully if the source contains leading zeros, long identifiers, dates, or mixed text-and-number values, because automatic conversion may change how those values are represented.

If Conversion Still Fails, Check for Hidden Characters

A value can look numeric while still containing a hidden character, such as a non-breaking space (NBSP, Unicode character 160) left over from a web copy-paste or an export from another system. When a value contains one, the methods above may not convert it, because Excel still sees a text string rather than a clean number.

TRIM removes ordinary spaces (the character you get from pressing the spacebar, code 32) and collapses multiple spaces between words down to one — but by itself it does not remove the non-breaking space character. CLEAN removes the first 32 nonprinting ASCII control characters (codes 0–31); the non-breaking space falls outside that range, so CLEAN does not address it either. In other words, TRIM and CLEAN are each scoped to a different kind of unwanted character, and neither is designed to target a literal U+00A0.

For a closer look at this, see our guide on why TRIM isn’t removing non-breaking spaces.

To target the non-breaking space specifically, use UNICHAR(160), which returns the Unicode character for code point 160 — the non-breaking space — so it can be matched and replaced explicitly:

=VALUE(TRIM(SUBSTITUTE(CLEAN(A2),UNICHAR(160)," ")))

For readability on smaller screens, here is the same formula wrapped across lines (the single-line version above is the one to copy):

=VALUE(
  TRIM(
    SUBSTITUTE(
      CLEAN(A2),
      UNICHAR(160),
      " "
    )
  )
)

This formula uses CLEAN to strip ordinary control characters, SUBSTITUTE with UNICHAR(160) to swap the literal non-breaking space for a regular space, TRIM to clean up the result, and VALUE to convert the cleaned text into a number.

Which Version of Excel Do You Need?

All five techniques in this article — the four bulk-conversion methods and the UNICHAR fix — work in Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. None of them depend on newer dynamic-array functions like UNIQUE, FILTER, or TEXTSPLIT, so there is no version-specific fallback needed here.

One-line Summary

For a quick one-time fix, use Convert to Number or Paste Special > Multiply. For mixed imported data, use a helper formula such as VALUE() so you can see which rows converted successfully before replacing the source values. If a value still won’t convert, check for a hidden non-breaking space and use the UNICHAR(160) formula above.

Sources

• Convert numbers stored as text to numbers in Excel — Microsoft Support. Backs Method 1 (error-checking indicator, Convert to Number) and Method 3 (VALUE as a helper-column method).
• Fix text-formatted numbers by applying a number format — Microsoft Support. Backs Method 2 (Paste Special with Multiply).
• VALUE function — Microsoft Support. Reference for the VALUE function used in Method 3 and in the hidden-character formula.
• TRIM function — Microsoft Support. Explicitly documents the non-breaking space (decimal 160) and states that TRIM alone does not remove it — the basis for the “Check for Hidden Characters” section.
• CLEAN function — Microsoft Support. Documents that CLEAN removes only the first 32 nonprinting ASCII control characters, which do not include the non-breaking space.
• UNICHAR function — Microsoft Support. Reference for UNICHAR, which returns the Unicode character for a given code point (160 for the non-breaking space).
• Split text into different columns with the Convert Text to Columns Wizard — Microsoft Support. General reference for the Text to Columns wizard used in Method 4. Note: this page does not document the specific behavior of “General” format reinterpreting text-stored numbers — that claim rests on this project’s own reproduction in Excel, not on this official page.