How to Delete Blank Rows in Excel

To delete completely empty rows without removing records that have a few blank cells, add a helper column that counts each row’s contents, filter that column to show 0, then delete the displayed rows. Check the filtered rows before deleting, and save a copy first if the workbook is important.

What You’ll Need

An Excel workbook with a header row and a rectangular data range, such as columns A through F.
A spare column beside the data for a temporary helper formula. If no column is free, insert one or use an empty column elsewhere.
Permission to edit the workbook. If the sheet is protected, you may need its password before changing or deleting rows.

Step-by-Step

Save a working copy. Use File > Save As to make a copy before removing rows, especially if you might need to recover the original layout. Deleting rows can shift formulas, references, charts, and data below the list. You can also undo immediately with Ctrl+Z on Windows or Command+Z on Mac, but undo history may be lost after closing the file.
Identify the full width of your data. Find the first and last columns that belong to each record, including columns that may be blank in some rows. For example, if the headers are in row 1 and the data uses columns A through F, the range to check is A:F. Do not use just one column unless every genuine record is guaranteed to have a value in it; otherwise, valid records with an empty value in that column could be mistaken for blank rows.
Add a helper column. In the first empty column to the right of the data, enter a clear header such as Row count. With headers in row 1 and data in A2:F20, enter =COUNTA(A2:F2) in G2. This counts nonempty cells across that row: if A2:F2 is entirely empty, the result is 0; if three of those cells contain values, the result is 3. Copy the formula down through the last data row. You can drag the fill handle at the bottom-right corner of G2, or copy G2, select the remaining helper cells, and paste.
Check that the helper values make sense. Look at several known records and several apparent blank rows. A row with any ordinary value in A:F should have a count above 0; a wholly empty row should show 0. If a supposed blank row shows a positive count, inspect its cells for a space, an apostrophe, or a formula. COUNTA counts a formula as content even when that formula displays an empty string, so decide whether those formula rows should be kept before proceeding.
Turn on filters for the whole range. Select a cell in the data and use Data > Filter. Make sure the range includes the original columns and the new Row count column, and that Excel recognizes the first row as headers. If the filter arrows do not appear on every header, select the complete range, including the helper column and header row, then choose Data > Filter again. In Excel for Mac, the same ribbon command is available; in Excel for the web, use the Data tab’s Filter command.
Show only rows with a zero count. Open the filter arrow in the Row count header. Clear Select All, select only 0, and confirm. The visible records should now be rows Excel considers empty across the columns you included. Review the displayed rows before deleting. If a real record appears, cancel the deletion and investigate the range or formula rather than removing it.
Delete the displayed rows, not just their cell contents. Select the visible row numbers for the zero-count records along the left edge of the worksheet. Choose Home > Delete > Delete Sheet Rows. If you right-click the selected row numbers, choose Delete Row or Delete Sheet Rows, not Clear Contents. With a filtered list, take care to select only the rows currently displayed; if you are uncertain, delete a small test group first and verify that the hidden records remain. Excel tables also offer a Delete Table Rows command for selected table records.
Clear the filter and remove the helper column. Use Data > Clear to show the remaining records again, or open the Row count filter and choose Select All. Confirm that the intended records remain and that the empty rows are gone. Then delete the temporary helper column: click its column letter and choose Home > Delete > Delete Sheet Columns. Save the cleaned workbook with File > Save.

These ribbon paths apply to Excel for Microsoft 365 on Windows. Excel for Mac has the corresponding Data and Home commands, though some menus and shortcuts differ slightly. Excel for the web also supports filtering and deleting rows, but the available controls can depend on the workbook and browser window. If the list is formatted as an Excel table, filter its Row count column and use the table’s row-deletion command when available.

Common Mistakes

Deleting blanks in one column as if they were blank rows. A blank cell in a key field does not mean the entire record is empty. For instance, an order may have no optional note but still contain an order number and amount. Count across every column that belongs to the record, or use a required field only when it is truly filled for every valid record.
Using Go To Special > Blanks on a multi-column range and deleting whole rows. That command selects individual blank cells, not only rows that are completely empty. A record with one empty cell could be selected along with genuinely blank rows. It is safer to use the helper count method for mixed data.
Clearing cells instead of deleting rows. Pressing Delete or choosing Clear Contents removes values but leaves the empty worksheet rows in place. Use Delete Sheet Rows when the goal is to close up the gaps.
Leaving out hidden or distant data columns. A row can look blank in the main columns but contain a note, formula, or identifier farther to the right. Include all columns that belong to the records before counting.
Deleting without checking the filtered result. A wrong formula range or a blank required field can mark valid rows as zero. Review the visible records and verify the filter before choosing a delete command.

Tips

Use a helper column with a meaningful header. A label such as Row count makes the filter easy to identify and reduces the chance of changing the wrong field. Delete the helper after the cleanup unless you want it for an ongoing quality check.
Keep a stable record identifier if you need to compare results. An ID, date, or other unique value can help you confirm which records remain. Do not rely on row numbers after deletion because later rows move up.
Turn the range into a table if you clean it regularly. Select the data and choose Insert > Table, confirming that the table has headers. Tables expand as data is added and include filter arrows, which can make repeated reviews easier. A table does not make it safe to delete based on just one potentially blank field, so retain the helper-count check.
Decide what counts as blank before you start. A cell containing a space, a nonprinting character, or a formula that returns an empty-looking result may appear empty while still being treated as content. Inspect such rows and remove unwanted characters or formulas only if you are sure they are not needed.
Keep the original row order. Filtering hides rows without rearranging the remaining records. After deleting the selected rows and clearing the filter, the original order of surviving records is retained.

Troubleshooting

The helper column shows a number instead of 0 in an apparently empty row. Click the row’s cells and inspect the formula bar. The row may contain a space, a hidden character, or a formula that returns an empty string. COUNTA counts formulas even when they display nothing. If the formula is unnecessary, remove it; if the row has a legitimate formula or value, keep the row. Avoid replacing the method with a quick delete until you know what the content is.
The filter arrow is missing or does not include the helper column. Clear any existing filter, select the complete block from the header row through the last data row and helper column, then choose Data > Filter. Check that there is one header row and no completely blank header cells within the selected range. If the data is split into separate blocks, handle each block individually rather than filtering a partial range.
A valid record appears when the helper filter is set to 0. The formula may not cover all the record’s columns, or the row may contain a value outside the selected range. Cancel the delete, widen the formula to include every relevant column, and fill the corrected formula down again. If you use a required-column method instead, verify that the selected field is mandatory for every record.
Rows seem to disappear beyond the blank ones. If you have not yet saved or closed the workbook, use Ctrl+Z on Windows or Command+Z on Mac immediately. Recheck that the filter was set to 0 and that only the visible row numbers were selected. If the change can no longer be undone, restore the saved copy or use version history if the file is stored in OneDrive or SharePoint.
Excel will not let you delete the rows. The worksheet may be protected, the workbook may be read-only, or the file may be shared with editing restrictions. Check Review > Unprotect Sheet if you are authorized and have the password, or save an editable copy. In Excel for the web, confirm that you have editing permission rather than view-only access.

Frequently Asked Questions

Can I delete blank rows without a helper column?
Yes, if one specific column is guaranteed to contain a value in every genuine record. Filter that column for blanks, review the displayed rows, and delete those rows. If valid records can have a blank in that field, use a helper count across all record columns instead.

Does Go To Special > Blanks delete only completely empty rows?
No. Go To Special selects blank cells within the selected range. A row with a blank cell and other data can therefore be selected too. It is suitable only when you have confirmed that the blanks identify whole rows, not isolated missing values.

Will this remove rows with formulas that display nothing?
Not with the COUNTA helper method shown here. COUNTA treats a formula as content even if its displayed result looks empty. Inspect those rows separately and remove them only if the formulas are not needed; a visually blank result is not the same as an empty cell.

Can I remove blank rows from an Excel table?
Yes. Add the helper field to the table, count across the record columns, and filter the helper field to 0. Review the records, then use the table’s Delete Table Rows command for the selected visible records. Remove the temporary field afterward if you do not need it.

Will deleting blank rows change formulas or references?
It can. Excel adjusts many references when rows are deleted, but formulas, charts, named ranges, and external links may depend on the original layout. Save a copy first and check important calculations and reports after the cleanup.

Can I use these steps in Excel for Mac or the web?
Yes. The helper formula and filtering approach work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon placement and some shortcuts differ, so use the Data and Home tabs or the context menu if a Windows shortcut is unavailable.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.