Delete Blank Rows in Excel All at Once

To delete fully blank rows in Excel all at once, add a temporary helper column, mark rows with no content, filter for that mark, and delete the matching rows. This method checks every column in your data, so it is safer than selecting blank cells in a range where some records may legitimately have empty fields. Save a copy first, and confirm which columns belong to the data before removing anything.

What You’ll Need

Excel for Microsoft 365 on Windows, or a version with AutoFilter and the COUNTA function. The main steps also work in recent desktop versions of Excel.
A worksheet with one header row and a rectangular data range, such as columns A through F. The columns should contain all the fields you want checked for blank rows.
A spare column beside the data for a temporary helper formula. If there is no spare column, insert one temporarily or place it elsewhere on the sheet.
A saved copy of the workbook, especially if the rows contain important or hard-to-recreate information.

Step-by-Step

Save a backup and check the data range. Save the workbook with a new name or make a copy of the worksheet. Identify the header row and the first and last columns that make up each record. For example, if the headers are in A1:F1 and records run from row 2 to row 500, the data range is A1:F500. Include every column that should prevent a row from being treated as blank. Do not include unrelated notes or totals outside the records.
Add a helper heading. Type a heading such as Blank row? in G1, immediately to the right of the example data. If column G already contains important information, use another empty column instead. Keep the helper heading in the same header row as the other column names so that Excel can include it in the filter.
Enter the blank-row check in the first data row. With the data layout A2:F500 and the helper column in G, enter =COUNTA(A2:F2)=0 in G2. COUNTA counts cells that contain values or formulas; the comparison returns TRUE only when it finds no content in A2:F2. For a genuinely empty row, the count is 0 and the result is TRUE. If A2 contains a value, the count is at least 1 and the result is FALSE. This checks all six data columns in that row, rather than treating a single empty field as proof that the whole row is blank.
Copy the formula down the data. Select G2 and double-click the small square at the lower-right corner of the cell, called the fill handle. Excel normally fills the formula down alongside the neighboring data. If it stops early or fills too far, drag the fill handle to the last data row, or copy G2 and paste it into G3:G500. Check a known blank row and a known populated row: the blank one should show TRUE, and the populated one should show FALSE. If your data uses different columns or row numbers, adjust the range in the formula. For example, with records in B3:H120, the first formula would refer to B3:H3.
Turn on AutoFilter. Click a cell in the header-and-data range, then choose Data > Filter. Excel adds a dropdown arrow to each heading. If the arrows do not include the helper heading, turn the filter off, select the full range including the helper column, and turn it on again. Avoid selecting only the helper column: the filter should cover the complete records so rows remain together.
Show only rows marked TRUE. Open the dropdown in the Blank row? heading. Clear Select All, select TRUE, and click OK. Excel now displays rows that have no counted content in any of the checked columns. If no rows appear, either there are no fully blank rows in the range or the helper formula or filter range needs checking. Do not delete rows just because some individual cells are empty.
Select and delete the filtered rows. Select the visible row numbers for the rows marked TRUE, excluding the header. If there are many rows, click the first visible row number, then hold Shift and click the last visible row number. Choose Home > Delete > Delete Sheet Rows. In a filtered list, Excel removes the selected visible records; confirm that the displayed rows are the ones you intend to remove before choosing the command. Do not press the Delete key by itself: that clears cell contents but leaves the blank rows in place.
Clear the filter and check the result. Open the helper dropdown and select Clear Filter From that heading, or choose Data > Clear to clear worksheet filters. Review the surrounding records and confirm that data is still aligned across columns. When you are satisfied, delete the temporary helper column by selecting its column heading and choosing Home > Delete > Delete Sheet Columns. Save the cleaned workbook.

If your data is an Excel Table: Tables have their own filter controls. You can add the helper as a table column and use the formula for each table row, then filter for TRUE. Select the visible table records and use the table-row deletion command, such as right-clicking a selected visible table row and choosing Delete > Table Rows, where available. Do not choose to delete the worksheet rows if you only intend to remove table records and the surrounding sheet contains other content.

Common Mistakes

Using Go To Special on a multi-column range without checking the consequences. Home > Find & Select > Go To Special > Blanks selects empty cells, not rows that are entirely empty. A row with a customer name but no phone number contains a blank cell; deleting its entire row would remove a valid record. Use this approach only when a particular column is guaranteed to be filled for every real record, and select blanks in that column alone.
Checking too few columns. If the helper formula checks A:F but a record has important content in G, Excel may classify it as blank. Include every field that belongs to a record. Conversely, exclude unrelated material that should not determine whether the record is empty.
Including the header or totals in the check. Start the formula in the first data row, not the heading row. Keep totals, footnotes, and separate notes outside the filtered data range where possible. Filtering a range that includes summary rows can make them appear in the results or disrupt the layout.
Confusing empty-looking cells with truly empty cells. A cell containing a space, an apostrophe, or a formula that displays an empty string may look blank but still contain something. COUNTA can count formulas even when their displayed result is empty, so a row with such formulas may return FALSE. Check these rows before deleting them.
Pressing Delete instead of deleting rows. The Delete key clears selected contents; it does not remove worksheet rows. Use Home > Delete > Delete Sheet Rows for an ordinary range, or the appropriate table-row command for a table.
Deleting without a backup or checking the visible selection. Removing rows changes the sheet structure and can affect references, charts, or formulas. Confirm that only the intended records are visible and selected, and keep a recoverable copy until you have reviewed the result.

Tips

Use a key column for a quicker check when it is reliable. If every legitimate record must have an ID in column A, you can filter that column for blanks and delete those rows. This is quicker than adding a helper, but it removes incomplete records too if their ID is missing. Use it only when a blank ID definitely means the entire row should go.
Make the helper formula match the real record width. If your records span A:F, check A:F. If they span A:K, use A:K. Do not automatically check all 16,384 worksheet columns: unrelated content far to the right can cause a visually empty row to be marked as populated.
Keep filters and hidden rows in mind. Existing filters can limit which records you see, and manually hidden rows may not appear in the selection. Clear old filters or inspect the range before starting. After deletion, clear the helper filter and review the full data set.
For a very large list, use an Excel Table. Select the data and choose Insert > Table, making sure My table has headers is checked. Tables expand formulas and filters more reliably as records are added. Keep a backup and remove only the table rows marked blank.
Know the shortcut difference. On Windows, Ctrl+Shift+L toggles AutoFilter for a selected range. On Mac, use Command+Shift+F or the Data tab’s Filter control, depending on your Excel version and keyboard settings. Ribbon commands are the clearest option if the shortcut behaves differently on your device.

Troubleshooting

The helper says FALSE for a row that looks empty: Click cells in that row and inspect the formula bar. They may contain spaces, hidden characters, or formulas that return an empty display. A formula is still content for COUNTA, even when it shows nothing. Decide whether those cells should count as data before changing the test. If the row should be removed, clear its contents or verify it manually rather than deleting every row that merely looks empty.

The filter dropdown is missing: Select a cell in the data, then choose Data > Filter. If the range is split by an entirely empty column or row, Excel may have detected only part of it. Turn the filter off, select the full header-to-last-row range including the helper column, and turn it back on.

TRUE does not appear as a filter choice: Confirm that the formula was entered with an equals sign and copied down. Look for errors such as #NAME? or references that point to the wrong row. If the filter was applied before the helper formulas were filled, clear and reapply it or refresh the filter by turning it off and on.

Rows with data are being marked TRUE: Check whether the formula stops before the last data column, or whether it refers to the wrong row. In G2, the example formula must check A2:F2; when copied down, the next row should check A3:F3. Also confirm that the data you want to preserve is inside the checked range.

More rows were removed than expected: Stop editing and use Ctrl+Z on Windows or Command+Z on Mac immediately, if the deletion is still the most recent action. Then clear filters, inspect the helper results, and repeat with a corrected range. If you have already saved or made further changes, restore the backup or use the workbook’s version history if available.

Frequently Asked Questions

Can I delete all blank rows without a helper column?
Yes, if a specific key column is always filled for every record. Filter that column for blanks, select the visible rows, and delete the sheet rows. For a multi-column list where some fields may be empty, a helper check is safer because it distinguishes an empty cell from an entirely blank record.

Does Go To Special > Blanks delete whole blank rows?
No. It selects blank cells in the selected range. If you then delete entire rows, records with any empty field can be removed too. Use it only on a column that must contain a value for every valid record, or use the helper method to check all record columns.

Will this remove rows hidden by a filter?
The steps show only rows marked TRUE and delete the visible selection. Other filters can hide additional rows, so clear existing filters first and inspect the range. If you intentionally have hidden records, make sure the selection and deletion behave as intended before continuing.

What if a blank row contains formulas?
It is not truly empty to COUNTA, even if the formulas display blank results. Review the row and decide whether those formulas should be preserved. If you want to remove formula-only rows, use a carefully tailored test or check them manually; do not treat a displayed blank as proof that the row has no content.

Can I do this in Excel for Mac or Excel for the web?
Filtering and deleting rows are available in both, though ribbon placement and shortcuts can differ. On Mac, use the Data tab if the Windows shortcut does not match your keyboard. Excel for the web may offer fewer selection and table-row options; if the filtered row selection is difficult to verify, open the workbook in desktop Excel before deleting.

Can I undo the deletion?
Usually, use Ctrl+Z on Windows or Command+Z on Mac immediately after deleting. Undo may no longer be available after closing the workbook or performing other actions, so save a copy before starting and check the result before overwriting your original file.

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.