sodcalculator

10 Tech How-To

Excel Delete Rows with Missing Values: 4 Ways

Guide 10-50825 · Updated · Step by step

To delete rows with missing values in Excel, use the Go To Special feature to select blanks, then delete the entire rows. This guide covers the fastest methods, including filtering, sorting, and using formulas for more control.

Why You Need to Remove Incomplete Rows

Missing data is a common problem in spreadsheets. A row might have a blank cell for a phone number, a date, or a price. When you analyze that data with a pivot table, chart, or formula, the blank cells can cause errors or misleading results. For example, a SUM function ignores empty cells, but a VLOOKUP might return an incorrect match. Cleaning your data by removing rows with missing values ensures your calculations and reports are based on complete records only.

Method 1: Go To Special (Quickest for a Single Column)

This is the standard approach when you want to delete any row that has a blank in one specific column.

  1. Select the entire column that you want to check for blanks (for example, column B).
  2. Press F5 or Ctrl+G to open the Go To dialog box.
  3. Click the Special button at the bottom left.
  4. Choose Blanks and click OK. All empty cells in that column are now selected.
  5. Right-click on any one of the selected cells and choose Delete.
  6. In the dialog, select Entire row and click OK.

This instantly removes every row that had a blank in the chosen column. The key limitation is that it only checks one column at a time. If you need to delete rows where any of several columns are blank, use the filter method below.

Method 2: Filter and Delete (Best for Multiple Columns)

When you want to remove rows that have a blank in any of several columns, filtering gives you a clear view of what will be deleted.

  1. Select your entire data range (including headers).
  2. Go to the Data tab and click Filter. Small dropdown arrows appear in each header.
  3. Click the dropdown arrow on the first column you want to check.
  4. Uncheck Select All, then check only (Blanks) at the bottom of the list. Click OK.
  5. Repeat this for each column where a blank should trigger deletion. After each filter, the visible rows will shrink to those with blanks in all selected columns.
  6. Once you see only the rows you want to remove, select those visible row numbers (click the number on the left edge, drag down).
  7. Right-click a selected row number and choose Delete Row.
  8. Clear the filter by clicking Filter again or pressing Ctrl+Shift+L.

Note that this method deletes rows where all of the filtered columns are blank. If you want to delete a row when any column is blank (e.g., column A OR column B is empty), you cannot do that with multiple filters alone. Use the formula method below instead.

Method 3: Formula with COUNTA (Delete Rows with Any Missing Value)

This is the most flexible method. It lets you delete a row if any cell in a range of columns is blank, or if a specific combination of cells is blank.

  1. Add a new column to the right of your data (for example, column E).
  2. In the first data row (say E2), enter this formula: =COUNTA(A2:D2)
  3. Drag the formula down for all rows. The formula returns the number of non-empty cells in the range A2:D2.
  4. Now filter on this new column. Click the filter arrow on column E, uncheck Select All, and check only the number that equals the total number of columns you expect (e.g., if you have 4 columns, check 4). This shows rows where all 4 cells are filled.
  5. Select those visible rows and delete them as in Method 2.

To delete rows with any blank, filter for any number less than the total column count. For example, with 4 columns, filter for 0, 1, 2, or 3. This gives you precise control.

Method 4: Sort and Delete (For Large Datasets)

Sorting is a quick visual way to group all blank cells together, but it changes the order of your data. Use it only if you don’t need the original row order.

  1. Select your data range.
  2. Go to the Data tab and click Sort.
  3. Choose the column you want to check for blanks as the Sort by field.
  4. Set Sort On to Cell Values and Order to Smallest to Largest (for text, this puts blanks at the top; for numbers, blanks also go first).
  5. Click OK. All rows with blanks in that column will now be at the top.
  6. Select those rows and delete them.

This is fast, but it permanently reorders your data. If you need to preserve the original order, use Method 1 or 2 instead.

Common Problems and Mistakes

  • Deleting the wrong rows: Always check the row numbers before deleting. Use the filter to view the rows you’re about to remove.
  • Accidentally deleting headers: Ensure your header row is not included in the selected range when using Go To Special.
  • Blanks caused by spaces: A cell containing a space character looks blank but is not. Use =TRIM(A2) to remove spaces, or use =ISBLANK(A2) to check for true blanks.
  • Formulas returning empty strings: If a cell contains ="", it looks blank but COUNTA counts it as a value. Use =COUNTBLANK(A2:D2) instead to count cells that are truly empty.
  • Losing data permanently: Deleting rows is irreversible unless you press Ctrl+Z immediately. Save a backup copy of your file before cleaning.

Which Method Should You Choose?

Here’s a quick guide:

  • One column, quick fix: Go To Special (Method 1).
  • Multiple columns, but only delete if all are blank: Filter (Method 2).
  • Delete if any column is blank: Formula with COUNTA (Method 3).
  • Large data, order doesn’t matter: Sort and delete (Method 4).

For most users, Method 1 is the fastest. For complex rules, Method 3 is the most powerful.

Frequently Asked Questions

How do I delete rows with blanks in multiple columns at once?

Use the filter method (Method 2) and apply filters to each column. Only rows that are blank in all filtered columns will show. To delete rows with a blank in any column, use the COUNTA formula (Method 3).

Will deleting rows affect my formulas?

Yes, if your formulas reference the deleted rows, they will return errors like #REF!. To avoid this, use tables (Insert > Table) so formulas automatically adjust, or use a formula that ignores blank rows, like =SUMIF.

Can I undo a row deletion?

Yes, immediately press Ctrl+Z to undo. However, if you close and reopen the file, the undo history is cleared. Always save a backup before large deletions.

What if my data has hidden rows or filtered rows?

Go To Special with Blanks only selects visible cells. If you have hidden rows, they will not be selected. Unhide all rows first (select all, right-click, Unhide) before using Go To Special.

Does this work in Google Sheets?

Yes, the same methods work, but the menu names differ. In Google Sheets, use Data > Create a filter and filter for blanks, or use the =COUNTA formula in a helper column.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →