ThisCalculator

Everyday

Remove Empty Rows in Excel Fast (Step-by-Step)

Updated · Standard formula, shown below

This guide shows you the fastest ways to remove rows that contain empty cells in Excel or Google Sheets. You’ll learn keyboard shortcuts, filters, and formulas that clean your data in seconds, even for large spreadsheets.

What You’ll Need

  • A spreadsheet with the data you want to clean (Excel 2016 or later, or Google Sheets)
  • Your data should have a header row (like “Name”, “Date”, “Amount”) so you can use filters safely
  • A backup copy of the file (optional but recommended before deleting anything)

Step-by-Step

Method 1: Filter and Delete (Fastest for Most Users)

  1. Select your data range – click any cell inside your table. If your data has blank rows or columns around it, select the entire range manually (e.g., A1:D100).
  2. Turn on filtering – go to the Data tab and click the Filter icon (funnel). In Google Sheets, it’s the same icon under Data → Create a filter.
  3. Find the column most likely to have blanks – look for a column where every row should have a value (like an ID or email).
  4. Filter out blanks – click the drop-down arrow in that column’s header, uncheck Select All, then scroll down and check (Blanks) (in Excel) or Blanks (in Google Sheets). Click OK.
  5. Delete the visible rows – with the blank rows now shown, select all those row numbers by clicking the first row number, holding Shift, and clicking the last visible row number. Right-click a selected row number and choose Delete Row. In Excel, you can also press Ctrl + – (minus) and choose Entire row.
  6. Clear the filter – click the filter icon again or press Ctrl + Shift + L (Excel) to turn it off. Your data now has no rows with empty values in that column.

Method 2: Go To Special (Excel Only, No Filter Needed)

  1. Select the entire data range (including headers).
  2. Open Go To Special – press F5 or Ctrl + G, then click Special…. Alternatively, go to Home → Find & Select → Go to Special.
  3. Choose Blanks – select the Blanks option and click OK. All empty cells in your range are now selected.
  4. Delete the rows – right-click any selected cell and choose Delete…. In the dialog, select Entire row and click OK. This removes every row that had at least one blank cell.

Method 3: Use a Helper Column (For All Blanks in Any Column)

  1. Add a new column to the right of your data (e.g., column E). Label it “Check” or “Delete?”.
  2. Enter the formula in the first data row (e.g., E2): =COUNTA(A2:D2) – this counts non-empty cells in that row.
  3. Copy the formula down – double-click the fill handle (small square at the bottom-right of the cell) to fill the column for all rows.
  4. Filter on the helper column – turn on filters, then filter the helper column to show only rows where the count is less than the number of columns (e.g., if you have 4 columns, filter for values < 4).
  5. Delete those rows – select the visible row numbers, right-click, and delete. Then clear the filter and delete the helper column.

Method 4: Sort (Quick but Risky)

  1. Select all your data including headers.
  2. Sort by a column – go to Data → Sort, choose any column, and sort A to Z. In most cases, blank cells will be pushed to the bottom (or top, depending on your sort).
  3. Delete the rows with blanks – once the blanks are grouped, select those rows and delete them.

Warning: Sorting changes the order of your data. Only use this if the order doesn’t matter or you can re-sort later.

Common Mistakes

  • Deleting only the selected cells, not entire rows – if you right-click and choose Delete… but select Shift cells up, you’ll leave gaps or misalign your data. Always choose Entire row.
  • Filtering on the wrong column – if you filter on a column that rarely has blanks, you might miss rows that have blanks in other columns. Use a column that should never be empty (like an ID).
  • Not including all columns in the filter – if you only select a few columns and then delete rows, you might delete rows that had data in unselected columns.
  • Forgetting to clear filters – after deleting, if you don’t clear the filter, you might think data is missing when it’s just hidden.
  • Using Go To Special on a single column – if you select only column A, you’ll only delete rows where column A is blank, not rows with blanks in other columns.

Tips

  • Always back up your data before deleting rows. A simple Ctrl+C and paste to a new sheet is enough.
  • Check for hidden spaces – a cell that looks empty might contain a space character. Use =TRIM(A2) if you suspect this, or use Find & Replace to replace spaces with nothing.
  • Use a helper column with =COUNTBLANK(A2:D2) to count blanks per row, then filter for rows with >0 blanks.
  • If you have a table (Excel Table), use the filter arrows directly – they are already there. Just click the drop-down in the header and uncheck (Blanks).
  • For very large datasets (100k+ rows), use Power Query (Excel) – go to Data → From Table/Range, then filter out nulls in the query editor. This is faster and doesn’t modify your original data until you load it back.

Troubleshooting

  • Filter doesn’t show “(Blanks)” option – This happens when there are no blank cells in that column, or the blanks are actually spaces. Try selecting the entire column and using Find & Select → Go to Special → Blanks to check.
  • Deleting rows causes data to shift incorrectly – This usually happens when you selected only a range of cells instead of entire rows. Undo (Ctrl+Z) and repeat, making sure you select the row numbers on the left side.
  • Go To Special selects blanks in hidden rows – If you have hidden rows, Go To Special will include them, which might cause unexpected deletions. Unhide all rows first (select all, right-click, Unhide).
  • My data has merged cells – Merged cells can confuse filters and Go To Special. Unmerge all cells first (select all, click Merge & Center to unmerge).
  • Rows with formulas that return “” (empty text) – These are not considered blank by Excel’s Blanks filter. Use a helper column with =COUNTBLANK(A2:D2) or replace the formulas with actual blanks before deleting.
  • Google Sheets: Filter doesn’t show blanks – In Google Sheets, the filter drop-down has a Blanks option at the bottom. If you don’t see it, click the filter icon again and look for “Filter by condition → Is empty”.

Frequently Asked Questions

How do I remove rows with any empty cell in Excel?

Use the Go To Special method: select your data, press F5 → Special → Blanks, then right-click any selected cell and choose Delete → Entire row. This removes every row that has at least one blank cell.

How do I remove rows with empty cells in Google Sheets?

Google Sheets doesn’t have Go To Special, but you can use the filter method: add a filter, click the drop-down in a column that should never be empty, uncheck Select All, then check Blanks. Delete the visible rows.

Can I undo row deletion?

Yes, immediately press Ctrl+Z (Cmd+Z on Mac) to undo the deletion. If you’ve closed and reopened the file, you might be able to recover from the autosave or backup.

What if my data has blanks in multiple columns?

Use the helper column method with =COUNTBLANK(A2:D2) to count blanks per row, then filter for rows with count > 0 and delete them.

Will deleting rows affect my formulas or charts?

Yes, formulas that reference deleted rows will return errors (like #REF!). Charts will automatically update to exclude deleted data. Always check your formulas after deletion.

Is there a way to remove blanks without deleting the entire row?

Yes, if you only want to remove the blank cells and shift the remaining data up (not delete rows), select the blanks with Go To Special, right-click, and choose Delete → Shift cells up. This will remove the blank cells but keep the row structure.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.