How to Delete Blank Rows in Excel in One Go Safely

To delete completely blank rows in Excel at once, add a helper column that marks rows with no data, filter that column for TRUE, select the visible rows, and delete them. This is safer than selecting every blank cell in a large range, because a row can contain a blank cell and still hold important information. Save a copy first if the workbook contains data you cannot easily rebuild.

What You’ll Need

Excel for Microsoft 365 on Windows, with the worksheet open and the data arranged in rows with a header row.
A helper column immediately beside the data, or another empty column where you can temporarily add a check.
A clear definition of what counts as blank. The main method below treats a row as blank when every cell in the chosen data columns is empty.

Step-by-Step

Save a working copy and inspect the data. Use File > Save As if you want a separate copy to fall back on. Identify the first and last columns that belong to the records, and confirm that the top row contains headings. Do not include unrelated notes or totals in the range you plan to check. If there are formulas, decide whether a row whose formulas display nothing should count as blank; the default check treats cells containing formulas as occupied.
Add a helper-column heading. In the first empty column immediately after the data, type a heading such as Blank row? in the header row. For example, if your headings are in A1:F1, enter the helper heading in G1. The helper column should not contain other information you need to keep, because you will remove it after the cleanup.
Enter the blank-row test in the first data row. With the records in A2:F20, enter this in G2: =COUNTA(A2:F2)=0. The formula returns TRUE when all six cells in that row are empty and FALSE when at least one contains a value. For example, if A2:F2 are all empty, the result is TRUE; if C2 contains North, the result is FALSE. Replace the example column letters with the full span of your own data. Keep the row number aligned with the row where you enter the formula.
Fill the test down through the records. Select G2 and use the fill handle, the small square at the selection’s lower-right corner, to copy the formula down to the last data row. You can also select G2 through G20 and press Ctrl+D on Windows to fill down. Check a few results: fully empty rows should say TRUE, and rows with data should say FALSE. On a Mac, the fill-down shortcut is Control+D; if that shortcut is intercepted by macOS or your keyboard settings, use the ribbon or fill handle.
Turn on filters. Select a cell in the header and data area, then choose Data > Filter. You can also press Ctrl+Shift+L on Windows. Filter arrows appear in the headings. On Mac, use Data > Filter; the Windows shortcut may differ depending on the keyboard and Excel settings. If the data is already an Excel table with filter arrows, you can use those rather than adding another filter.
Show only rows marked TRUE. Open the filter arrow in the helper heading, clear Select All, select TRUE, and choose OK. Excel hides rows marked FALSE and shows the rows the helper identifies as blank. If no rows appear, there may be no completely blank rows in the selected columns, or the formula may not cover the full data range.
Select only the visible data rows. Select the filtered data area from the first data row to the last used row, leaving the header out. Include the data columns; including the helper column is fine. Press Alt+; on Windows to select visible cells only, so filtered-out records are not part of the selection. On a Mac, use Home > Find & Select > Go To Special and choose Visible cells only if that option is available in your Excel version. If the selection includes only a few visible rows, confirm they are the intended blank rows before deleting.
Delete the selected rows, not just their contents. Press Ctrl+- on Windows. In the Delete dialog, choose Entire row and select OK. This removes the visible blank rows and shifts the remaining records up. If Excel does not show the dialog, use Home > Delete > Delete Sheet Rows after selecting the visible rows. On Mac, use Home > Delete > Delete Sheet Rows; keyboard behavior can vary by keyboard layout. Avoid pressing the Delete key by itself, which clears cell contents but leaves the empty rows in place.
Restore the full list and remove the helper. Clear the helper-column filter or choose Data > Clear to show the remaining data. Review the top, middle, and bottom of the list and check that records still line up correctly. Delete the temporary helper column when you are satisfied. If you need to keep the test for review, leave the column in place but rename its heading so it is clear what it contains.

The helper method works on ordinary ranges and Excel tables, but take care when selecting rows in a filtered list. The key is to select visible cells only before choosing Entire row. If your worksheet has important content outside the data table in the same sheet rows, deleting entire sheet rows will remove that content too. In that situation, work on a copy or use a table-specific deletion method that preserves the surrounding layout.

Common Mistakes

Selecting all blank cells and deleting their rows. Go To Special can select blank cells inside a range, including gaps in otherwise valid records. If you then delete entire rows, Excel can remove records that merely have one missing field. The helper check avoids this by testing all the columns you specify before marking a row for deletion.

Checking too few columns. If the formula checks A:F but a record has meaningful information in G, the formula will call the row blank when A:F are empty. Expand the range to cover every column that defines a complete record. At the same time, do not include unrelated worksheet content in the test.

Forgetting formulas that display an empty string. COUNTA counts cells containing formulas, even when the formula result looks blank. Such a row returns FALSE with the main method. That may be correct if the formula should be retained. If formulas that display nothing should count as empty, use the alternative below instead.

With the same layout of records in A2:F20 and the helper formula in G2, enter =SUMPRODUCT(--(A2:F2<>""))=0. This returns TRUE if every cell evaluates to an empty string, including formulas that return an empty string; for example, a row of empty cells or empty-string results returns TRUE, while a row with a number or text returns FALSE. Fill it down as before. A cell containing a space is not empty under this test, and a zero is counted as data.

Deleting contents instead of rows. The Delete key removes the selected cell contents but does not remove the row structure. Use Ctrl+- and choose Entire row, or the Delete Sheet Rows command, so the records below move up.

Removing rows beyond the records. A formula filled far below the actual list may mark empty worksheet rows as TRUE. Limit the helper formula and filter range to the real dataset, including only its intended rows.

Tips

Use a unique identifier column as a quick check after deletion, such as an order number or employee ID. Compare the number of remaining records with a count you trust, and scan around where the blank gaps used to be. If the worksheet has totals or footnotes beneath the dataset, keep them outside the filtered range so they are not mistaken for records.

For a small, clean list, Go To Special can be quicker, but use it only when a particular column is guaranteed to be filled in every valid record and a blank in that column means the whole record should be removed. Select only the data cells in that column, not the whole table; press F5, choose Special, select Blanks, and click OK. Then press Ctrl+- and choose Entire row. This shortcut is unsafe when legitimate records may have an empty value in that column. It is also a good idea to save first because deleting rows cannot be reversed after the workbook is closed.

Keep the helper formula limited to the columns that define a record. If the sheet contains optional fields, a row with only optional data still counts as occupied if that data is inside the test range. Decide whether that is the behavior you want before filtering. For recurring cleanup, convert a consistent dataset to an Excel table with Ctrl+T on Windows and use the helper column within the table. The table can expand as records are added, although you should still verify the formula and filter range when new data is appended.

Troubleshooting

The formula says FALSE for a row that looks empty. Check whether a cell contains a formula, a space, an apostrophe, or a nonprinting character. COUNTA treats a formula as content even if its displayed result is blank. The SUMPRODUCT alternative handles empty-string formula results, but spaces and other characters still count. If needed, inspect the cells individually or clean the source values rather than deleting the row automatically.

The helper column does not fill down correctly. Check that the first formula refers to the same row as the first record, then fill to the actual final record. If you copied the formula manually, confirm that its references move from A2:F2 to A3:F3 and onward. If the dataset has gaps, do not rely on double-clicking the fill handle to guess the end; drag it to the last intended row or select the exact range and use Ctrl+D.

The filter arrow or TRUE option is missing. Make sure the helper heading is in the same header row as the other headings and that there is no completely blank header row separating it from the data. Turn on the filter again with Data > Filter. If the worksheet is protected, ask whether you can unprotect it; protection can prevent filtering or deleting rows.

Excel removes the wrong information or nothing seems to happen. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac) if the result is wrong. Confirm that the helper filter is showing TRUE, that only visible cells were selected, and that the Delete dialog was set to Entire row. If unrelated notes share the same sheet rows, do not delete entire worksheet rows; undo and instead restructure or copy the dataset to a separate sheet before removing records.

The rows are hidden but not gone. Filtering hides rows temporarily. Clear the filter to show the full list; then confirm that the blank rows have actually been deleted. If they remain, repeat the deletion step and use Entire row rather than Clear Contents.

Frequently Asked Questions

Can I delete all blank rows without a helper column?
Yes, if a column is always populated in every valid record and blanks in it mean the entire row should be deleted. Select only that column’s data cells, use F5 > Special > Blanks, then Ctrl+- > Entire row. For mixed or optional data, the helper-column method is safer.

Does Excel have a built-in command to remove blank rows?
There is no universal one-click command that reliably identifies fully blank rows in every kind of worksheet. Go To Special selects blank cells, not necessarily rows that are entirely blank. Filtering a helper check makes the deletion criteria visible before you remove anything.

Will this method delete rows that contain formulas returning nothing?
Not with the COUNTA version: formulas count as content. Use the SUMPRODUCT alternative if a formula result of an empty string should qualify as blank. Review the rows it marks before deleting, especially if formulas are important to your worksheet.

Can I use the steps in Excel for the web?
Filtering and deleting rows are available in Excel for the web, but keyboard shortcuts and the Go To Special options may differ from desktop Excel. If you cannot select visible cells only or choose Entire row as described, open the workbook in Excel for Microsoft 365 on Windows and use the desktop steps.

Can I undo deleting blank rows?
Use Ctrl+Z on Windows or Command+Z on Mac right after the deletion. Undo history may be cleared when you close the workbook, so save a copy before a large cleanup and review the results before continuing with other edits.

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.