To delete completely blank rows in Excel safely, add a temporary helper column with =COUNTA(A2:F2)=0, filter that column for TRUE, then delete only the visible rows. Replace the example range with the full width of your data. This method avoids removing rows that have a blank cell but still contain useful information elsewhere.
Before you begin, save a copy of the workbook or confirm you can undo the change. The steps below are for Microsoft Excel for Microsoft 365 on Windows. They work similarly on Mac and Excel for the web, though keyboard shortcuts and some ribbon labels may differ.
What You’ll Need
- The worksheet containing the rows you want to clean up.
- A clear idea of the first and last columns in the data, so the helper test checks every relevant cell.
- A spare column for the temporary helper formula. If the worksheet has no spare column, add one temporarily and remove it when finished.
- A saved copy or a recent backup, especially if the sheet contains important or difficult-to-rebuild data.
Step-by-Step
- Check the data and its boundaries. Identify the first row of records and the complete set of columns that make up each record. For example, if names, dates, and amounts occupy columns A through F, the blank-row check must include A:F. Include any columns beyond F if they contain data that should keep a row. Do not include unrelated notes or a separate report elsewhere on the sheet.
- Save a working copy. Use File > Save As to create a copy, or save the workbook before making changes. Deleting whole worksheet rows affects every column on those rows, not just the visible data table. A backup is a useful safeguard if the wrong records are selected.
- Add a helper column beside the data. In the first row of the data, enter a clear heading such as Blank row? in an unused column. Keep the helper column adjacent to the data if possible so it is included when you apply a filter. If your data is an Excel Table, add the helper as a new table column; Excel may automatically copy the formula down the table.
- Enter a formula that checks every data column. With records in columns A:F, and the first record on row 2, enter
=COUNTA(A2:F2)=0in the helper cell for row 2. The formula returns TRUE when all six cells are empty and FALSE if at least one contains a value or formula. If your records run from A to K, use A2:K2 instead. Do not use a range that stops before the last relevant column. - Fill the formula down through the records. Select the cell with the formula and use its fill handle, the small square at the lower-right corner, to copy it down to the final row. You can also copy the cell, select the remaining helper cells, and paste. Check a few results: a genuinely empty record row should show TRUE; a row with even one entry should show FALSE.
- Turn on filters. Click a cell in the data and choose Data > Filter. If filter arrows already appear in the header row, leave them on. Confirm that the filter covers the helper heading and the complete data range. If it does not, select the full range, including the header and helper column, then turn on Filter again.
- Show only rows marked TRUE. Open the filter arrow on the helper heading, clear Select All, select TRUE, and choose OK. The worksheet now displays rows whose checked cells are all empty. Review the visible rows before deleting anything. If a row that should contain data appears, cancel and check that your formula includes the right columns and that it was filled correctly.
- Select only the filtered rows. Select the visible helper cells for the TRUE records, excluding the heading. In Windows Excel, press Alt+; to restrict the selection to visible cells only. You can also use Home > Find & Select > Go To Special > Visible cells only. This matters because filtered-out records are hidden, not removed, and you do not want them included in the selection.
- Delete the corresponding worksheet rows. With the visible TRUE cells selected, choose Home > Delete > Delete Sheet Rows. This removes the entire worksheet rows corresponding to the blank records, including any content in other columns on those same rows. Avoid choosing Delete Cells unless you specifically want to shift cells within the range rather than remove full rows.
- Clear the filter and check the result. Open the helper filter and choose Select All, or use Data > Clear to show all records again. Confirm that the blank rows are gone and the records that had data remain intact. Delete the temporary helper column when you are satisfied. Save the cleaned workbook.
On Mac, the ribbon approach is usually the same, but the Windows Alt+; shortcut may not apply. Use Home > Find & Select > Go To Special > Visible cells only instead, then use Home > Delete > Delete Sheet Rows. In Excel for the web, use the ribbon menus if a keyboard shortcut is unavailable. Always check that the selected cells are the visible filtered rows before deleting.
Common Mistakes
- Deleting blanks from just one column. A blank in column A does not mean the whole row is empty. If you select blank cells in that column and delete their entire rows, you could remove valid records that have information in columns B through F. Use the helper formula across the full record instead, or use a key column only if every valid record is guaranteed to have a value there.
- Checking too few columns. If the formula tests A:F but a note or identifier is in G, a row with content in G may be marked TRUE and deleted. Make the formula range cover every column that counts as part of the record.
- Using Go To Special > Blanks on the full table and deleting rows. That command selects blank cells, including individual empty cells in otherwise populated records. It does not identify only entirely empty rows. Use it for a single required field only when you have confirmed that a blank in that field means the whole record is disposable.
- Deleting hidden rows by mistake. When a filter is active, nonmatching records are hidden but still present. If the selection includes hidden cells, a delete operation may affect more than the displayed rows. Select visible cells only and verify the selection before choosing Delete Sheet Rows.
- Assuming formatting means a row has data. A row can have borders, fill color, or number formatting while containing no values. The COUNTA test ignores formatting, which is generally useful when cleaning empty records. Conversely, a formula in a cell counts as content even if it displays nothing.
Tips
- Decide what “blank” means first. The formula in this guide treats a row as blank only when all checked cells have no values or formulas. Cells containing spaces, zeroes, or formulas are not empty to COUNTA. If imported data contains cells with spaces, inspect or clean those cells separately rather than assuming they are truly blank.
- Pay attention to formulas that display an empty result. COUNTA counts a formula as content even if the formula displays an empty string. This prevents you from deleting a row that still has a formula. If your goal is instead to remove rows where formulas return visually blank results, verify the formula logic and results before using a different test; do not change the blank-row rule without checking what the formulas represent.
- Keep header rows out of the formula fill. Start the helper formula on the first record row, not the heading row. Filtering works best when the range has one clear header row and no completely empty rows separating the header from the records.
- Use an always-filled ID column for a quicker check when appropriate. If every real record must contain an ID, filter the ID column for blanks and inspect the results. This can be faster than a helper formula, but it is safe only if the ID requirement is enforced and no valid row is missing an ID.
- Check for other content on the same worksheet rows. Deleting sheet rows also removes cells outside the apparent table on those rows. If another independent list or notes occupy the same rows in other columns, do not delete whole worksheet rows; move the data into separate areas or make a backup and plan the layout first.
- For recurring cleanup, keep a repeatable process. A helper formula and filter are easy to review each time new data is added. For an Excel Table, use a helper column within the table and confirm new rows receive the formula before filtering.
Troubleshooting
The formula marks a row TRUE even though you see data. Check that the range reaches the last relevant column and that the formula refers to the same row as the record. A value outside the range will not be counted. Also check for merged cells, which can make data layout and selection confusing; unmerge them if appropriate and verify which cell actually holds the value.
Rows with formulas that look empty are not selected. This is expected with COUNTA: a formula is content even when its displayed result appears blank. Decide whether those formulas should be retained. If they are important, leave the row. If they are disposable, inspect the formulas and make a separate, carefully reviewed cleanup plan rather than deleting them as if they were empty cells.
The filter arrow or TRUE option does not appear. Confirm that the helper column has a heading, that the filter range includes it, and that the formula cells contain Boolean TRUE or FALSE results rather than text. Reapply the filter by selecting the complete header-and-data range and choosing Data > Filter. If the worksheet is protected, you may need permission to change filters or delete rows.
Deleting rows affects records that were hidden by the filter. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac) if the result is wrong. Reapply the filter, select the visible helper cells only, and use Go To Special > Visible cells only before deleting sheet rows. If undo is no longer available, restore the saved copy.
Nothing happens when you choose Delete Sheet Rows. Make sure a cell selection is active and that you selected the worksheet-row deletion command rather than Clear Contents. In a protected sheet, deletion may be blocked. On a Mac or in Excel for the web, use the ribbon command instead of relying on a Windows keyboard shortcut.
Frequently Asked Questions
Can I delete all blank rows without a formula?
Yes, if one column is guaranteed to contain a value in every legitimate record. Filter that column for blanks, review the visible rows, select visible cells only, and delete the corresponding sheet rows. If no column is guaranteed to be filled, a helper formula that checks the whole record is safer.
Does this remove blank rows from the entire worksheet?
The helper method removes rows that are blank across the columns included in its formula. It does not automatically assess every column on the worksheet. Include all relevant record columns, and remember that deleting sheet rows removes content from every column on those rows.
Will it delete rows that contain spaces?
No. A cell with a space is not empty, so COUNTA counts it as content and the row is marked FALSE. Remove unwanted spaces separately after checking that they are not meaningful data.
Can I recover deleted rows?
Usually, press Ctrl+Z on Windows or Command+Z on Mac immediately after the deletion. If you have made other changes or closed the workbook, undo may no longer be available; restore a saved copy or use the workbook’s version history if it is available.
Is it safe to use Go To Special and select Blanks?
It is safe only when blank cells in the selected field reliably identify rows you intend to remove. Selecting blanks across several columns can include empty cells inside valid records, so it is not a dependable way to identify entirely blank rows by itself.
Do I need to remove the helper column?
It is temporary, so delete it after checking the result unless you want to keep it as an audit aid. Removing that helper column does not restore the deleted rows; save the cleaned workbook once the final data is confirmed.
Related
- How to Copy Only Filtered Data in Excel Without Hidden Rows
- How to Copy Only Selected Cells in Excel Without Extra Data
- Copy Link in Excel: Steps for Cells, Files, and Sheets
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Search Archive Emails in Outlook: A Practical GuideTo find archived email in Outlook, search the Archive folder or set the search scope to include it,…
- Binary Excel Solver: How to Build and Solve 0-or-1 ModelsYes. In Excel for Microsoft 365 on Windows, use the Solver add-in and set the decision-cell range’s constraint…
- How to Create an Outlook Signature on an HP LaptopTo create an Outlook signature on an HP laptop, open Outlook, go to its signature settings, write and…
- How to Set Out of Office in Outlook on the WebTo set an out-of-office reply in Outlook on the web, open Settings, find Automatic replies, turn them on,…
- Excel File Format or File Extension Corrupted? What to DoIf Excel reports that a file format or file extension is not valid, the extension may not match…
- How to Add a Calendar Drop-Down in ExcelTo add a usable date drop-down in Excel for Microsoft 365 on Windows, put a date list on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.