To delete completely empty rows in Excel safely, add a helper column with =COUNTA(A2:Z2)=0, fill it down, filter for TRUE, and delete only the visible rows marked TRUE. First save a copy of the workbook, and adjust the formula range to cover every column that might contain data. If you only need to remove rows with a blank value in a required column, Go To Special can be quicker—but it is not safe for finding fully empty rows across a whole sheet.
What You’ll Need
- An Excel workbook with the data you want to clean. These steps are for Microsoft Excel for Microsoft 365 on Windows.
- A spare column beside your data for a temporary helper formula. If no column is free, insert one or place the helper in an unused column outside the data range.
- A saved backup or a duplicate worksheet, especially if the workbook contains important information. Deleting worksheet rows can affect content in every column on those rows.
Step-by-Step
- Save a copy and identify the full data range. Use File > Save As to make a working copy, or duplicate the worksheet by right-clicking its tab and choosing Move or Copy. Identify the header row and the first and last columns that belong to the dataset. Include columns that may contain formulas, notes, dates, or other information, not just the columns that look important. A row is only completely empty if no cell in that full range contains data.
- Add a helper column next to the records. Give the column a clear heading such as Row is empty. If the headers are in row 1 and the data starts in row 2, enter the formula below in the first helper cell. This example checks columns A through Z on row 2, so change A:Z if your data extends beyond those columns or uses a narrower range.
With the data in A:Z and the first record on row 2, enter=COUNTA(A2:Z2)=0in AA2. It returns TRUE when all cells from A2 through Z2 have no content, and FALSE if any of them contains a value or formula. For example, a fully blank row returns TRUE; a row with a date in C2 returns FALSE. - Copy the formula down to the last data row. Select the helper cell and drag its fill handle down, or double-click the small square at the bottom-right corner of the cell if the neighboring column has continuous data. Check the last few rows to confirm the formula was filled through the full dataset. Each row should test its own row number—for example, the next formula should check A3:Z3, not repeat A2:Z2.
- Turn on filtering for the complete range. Click inside the dataset, including its headers and helper column, then choose Data > Filter. Excel should add a drop-down arrow to each header. If the helper heading does not get an arrow, undo or clear the filter, select the entire data range including the helper column, and turn Filter on again. Do not include unrelated tables or notes when selecting the range.
- Show only rows marked TRUE. Open the drop-down on the helper-column heading, clear Select All, select TRUE, and choose OK. The visible records should be the rows the formula identified as empty. Before deleting anything, scan the visible rows and check that no cell in the dataset has information you need. If a row appears here but contains data, correct the helper formula range before proceeding.
- Select the visible records, not the header. Select the row numbers for the filtered data rows, taking care not to select the heading. In Windows Excel, press Alt+; to limit the selection to visible cells only. Then press Ctrl+- (the minus key). In the Delete dialog, choose Entire row and select OK. This removes the filtered empty rows while keeping the other records. If your worksheet has unrelated content in other columns on those same sheet rows, do not delete entire worksheet rows; remove the corresponding rows from the data table instead, or clean a copy of the dataset.
- Clear the filter and verify the result. Open the helper-column drop-down and choose Clear Filter From, or use Data > Clear. Confirm the remaining records are intact and in the expected order. Remove the temporary helper column when you are satisfied, then save the workbook. If you discover a problem immediately, use Ctrl+Z on Windows to undo; after further edits or saving and closing, restoring the backup may be safer.
Mac and web note: The ribbon commands are broadly similar in Excel for Mac, but keyboard shortcuts can differ with the keyboard and version. Use the Home or Data ribbon commands instead of relying on Alt+; or Ctrl+- if those shortcuts do not work. Excel for the web may not offer the same Go To Special and visible-cells-only workflow as desktop Excel; for a large cleanup, open the workbook in the desktop app or work on a duplicate and carefully use the filter commands available in the browser.
Common Mistakes
- Deleting blanks in just one column and assuming entire rows are empty. A blank cell in column A does not mean the rest of its row is blank. The helper formula should cover all columns in the dataset. Otherwise, a row with data in a later column could be wrongly marked for deletion.
- Using Go To Special > Blanks across a multi-column range. This selects individual blank cells, not only rows that are completely empty. Choosing to delete entire rows from that selection can remove records that have data elsewhere. Use this method only when checking a single required field, or use the helper method for whole-row checks.
- Forgetting formulas and hidden content. A cell that looks empty can contain a formula, a space, or content in a hidden column. COUNTA counts formulas even when they display an empty string, and counts spaces. That is generally safer than deleting a row that contains a formula or other content, but it means the row may not be classified as empty.
- Filtering an incomplete range. If the helper column is outside the filtered range, or a data column was left out, the filter may not represent the records you intend to clean. Include the header, every relevant data column, and the helper column in the same range.
- Deleting whole worksheet rows when other material shares those rows. Entire row deletion affects every column on the sheet, including side notes or another small table. Check the full worksheet width before confirming, and use a copy if the sheet contains separate content.
Tips
- Convert a range to a Table when appropriate. Select the data and choose Insert > Table, confirming that the table has headers. Tables make it easier to filter a named helper column and keep the records together. Avoid converting a range that contains multiple unrelated blocks.
- Keep an original-row number if order matters. Before sorting or performing a complicated cleanup, add an index column with sequential numbers. It gives you a reference for restoring the original sequence if records are rearranged. Filtering and deleting does not normally reorder the remaining records, but a separate index is useful for quality checks.
- Use a required field for a quick targeted cleanup. If every legitimate record must have an ID in column A, filtering that column for blanks can find records with missing IDs. It does not prove those records are empty; review them as incomplete records rather than deleting them automatically.
- Save and check totals. Note the number of data rows before deleting, then compare it with the remaining count afterward. If the workbook has totals, formulas, or charts, inspect them after cleanup because deleting rows may change references or the data they include.
- Remember that formatting is not cell content. A row may look colored, bordered, or unusually tall even when all its cells are empty. COUNTA checks cell contents, not formatting, so it can identify such a row as empty. If the visual formatting itself matters, inspect the affected area before removal.
Troubleshooting
The helper column says FALSE for a row that looks blank. Check each cell in the formula range, including hidden columns. A formula that displays nothing, a space character, or a nonprinting value can make COUNTA return a count greater than zero. Click a suspicious cell and inspect the formula bar. If formulas that return an empty string should count as blank for your purpose, decide that explicitly before changing the test; removing a row with a working formula may break later calculations.
Rows with data are being marked TRUE. The formula probably does not cover the complete data range, or its references do not move down correctly. Edit the first helper formula to include all relevant columns, fill it down again, and verify several rows with known values. Make sure the helper formula does not include the helper column itself, which could cause a circular reference.
The filter shows no TRUE values, or the drop-down is missing. Check that the helper formula was copied through the last record and that TRUE is selected in its filter menu. If the range has blank lines that interrupted automatic selection, select the entire data block manually and choose Data > Filter again. A table or filtered range can also have an existing filter; clear it before applying the helper-column filter.
Ctrl+- does not open the expected Delete dialog. On Windows, select the visible row numbers first, then use Ctrl+-; make sure the minus key is being recognized by your keyboard. Alternatively, use Home > Delete > Delete Sheet Rows after selecting only the intended visible rows. If Excel warns that the selection is not suitable, cancel and reselect; do not guess at a deletion option. On Mac, use the ribbon or context menu if the Windows shortcut does not apply.
Some blank-looking rows remain after the cleanup. They may contain spaces, formulas, or content outside the range you checked, so COUNTA can correctly treat them as nonempty. Conversely, they may only look blank because of formatting. Inspect the cells before removing them, and do not expand the formula range into unrelated worksheet columns unless those columns belong to the records.
Frequently Asked Questions
Can I delete all empty rows in Excel at once?
Yes. The helper-column formula identifies rows with no content in the checked range, and filtering TRUE lets you select and delete that group. Review the filtered results before confirming, especially in a workbook with hidden columns or other data on the same worksheet rows.
Why not select the whole sheet and use Go To Special > Blanks?
That command selects blank cells individually. A mostly populated row usually contains many blank cells, so deleting entire rows based on that selection can remove valid records. Use it on one required column only when a blank in that column is a reliable indicator, or use the helper formula to test the complete row.
Does COUNTA treat a formula that displays nothing as empty?
No. COUNTA counts a cell containing a formula even if the formula displays an empty string. It also counts spaces. This behavior helps protect rows containing formulas or hidden text, but it can leave visually blank rows in the sheet.
How do I delete rows with a blank ID but keep other blank-looking rows?
Filter the ID column for blanks and review the results. Delete only the records that should be removed. A missing ID may indicate an incomplete record rather than an empty row, so it should not automatically be treated as a fully blank row.
Can I undo deleting rows?
Usually, press Ctrl+Z on Windows immediately after the deletion. If you have made more changes, closed the workbook, or cannot undo far enough, restore the saved copy. Keeping a backup is the most reliable safeguard for a large cleanup.
Related
- How to Copy an Entire Column in Excel Without Errors
- How to Copy Cells in Excel by Dragging
- Copy Data Into Multiple Cells in Excel: A Quick Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make Serial Numbers in Excel: 4 Easy MethodsTo make serial numbers in Excel, enter 1 and 2 in the first two cells, select both, then…
- Power Query Excel: Five Ways to Prevent Refresh ErrorsTo prevent errors in Power Query for Excel, use five safeguards: keep the source stable, set data types…
- Validate Data in Google Sheets: Rules and Drop-DownsTo validate data in Google Sheets, select the cells, choose Data > Data validation, then add a rule…
- AG Grid Hide Column Menu: How to Use and Customize ItTo hide a column in AG Grid, open the column’s header menu and choose its hide option, if…
- How to Delete a Blank Page in Word: Step-by-StepTo delete a blank page in Microsoft Word, turn on formatting marks, find the paragraph mark, page break,…
- Excel Cell Margins: Add Top and Bottom SpaceExcel does not have a separate top or bottom cell-margin setting. To create space inside a cell, adjust…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.