To delete blank rows in Excel quickly, filter a column that every valid record must contain, select the blanks, and delete those rows. If you need to remove only rows that are completely empty, use a helper column to check the whole row first. Make a copy of the workbook before deleting anything; a blank in one column does not always mean the rest of that row is empty.
What You’ll Need
- An Excel workbook with a header row and a clear data range.
- A backup or duplicate of the worksheet, especially if the data is important.
- For the filter method, a column that should never be blank in a valid record, such as an order ID.
- For the helper-column method, an unused column where you can temporarily add a check.
The steps below are for Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web have similar filter controls, but some ribbon labels and shortcuts can differ; use the menu paths if a shortcut does not work.
Step-by-Step
- Save a safety copy and identify your data. Save the workbook, or use File > Save As to make a separate copy. Identify the header row and the first and last columns of the records. If the sheet contains notes, totals, or other material below the list, do not select the entire worksheet for cleanup. Keep the selection limited to the actual dataset so that unrelated information is not included.
- Choose what you mean by a blank row. If a required field such as Record ID should be filled in every legitimate row, a blank in that field can be used to identify rows for removal. This is the fastest method, but it can also find incomplete records that contain information in other columns. If your goal is to remove only rows where every cell in the data range is empty, use the helper-column method in step 5 instead. Decide this before deleting anything.
- Turn on filters for the data range. Click a cell inside the dataset and choose Data > Filter. Excel normally detects the headers and applies filter buttons to the header row. On Windows, Ctrl+Shift+L toggles filters on or off. Check that each filter button is on the correct header; if Excel misses part of the list, undo or clear the filter and select the full range, including headers, before applying it again. In Excel for Mac or the web, use Data > Filter if the shortcut differs.
- Show blanks in a required column. Open the filter dropdown for the field you chose, such as Record ID. Clear Select All, select (Blanks), and apply the filter. Excel now shows records whose chosen field is blank. Look over the visible rows before removing them. If any row has useful data in another column, it may be an incomplete record rather than an empty row; keep it or resolve it separately instead of deleting it automatically.
- Delete the visible rows and clear the filter. If the displayed rows are definitely the ones you intend to remove, select their row numbers along the left edge of the worksheet, taking care not to select the header. On the Home tab, choose Delete > Delete Sheet Rows. You can also right-click the selected row headers and choose Delete. Do not press the Delete key expecting it to remove the rows: that key clears cell contents rather than deleting worksheet rows. Clear the filter afterward using Data > Clear, or reopen the dropdown and select all values. Check that the remaining records and their order look right.
- For fully empty rows, add a helper check instead. This method tests every cell in each record, so it is safer when a blank in one field does not necessarily mean the whole row is empty. Suppose your data occupies columns A through Z, row 1 contains headers, and records begin in row 2. In an unused column such as AA, enter a heading such as Empty row. In AA2, enter
=COUNTA(A2:Z2)=0. For this layout, the formula returns TRUE when every cell from A2 through Z2 is empty and FALSE when at least one cell contains a value or formula. For example, if A2:Z2 contains only empty cells, the result is TRUE; if C2 contains an order number, the result is FALSE. Fill the formula down alongside all records. Use the fill handle or select the formula cell and the cells below it, then press Ctrl+D on Windows. - Filter the helper results and remove the confirmed empty rows. With filters on, open the helper column dropdown and show TRUE only. Review the visible rows, select their row headers, then choose Home > Delete > Delete Sheet Rows. Clear the filter, remove the temporary helper column if you no longer need it, and verify the remaining list. If the data is an Excel table, make sure you are deleting the table rows, not merely clearing the helper cells; the row-header delete command removes the worksheet rows.
- Use Go To Special only when its selection is appropriate. For a small, simple list, you can select the data cells in a required column, excluding the header, then choose Home > Find & Select > Go To Special > Blanks. This selects the blank cells in that column. If those blanks represent rows you have confirmed should be removed, choose Home > Delete > Delete Sheet Rows. Do not select a wide block of data and then delete entire rows just because Go To Special found blanks: it selects individual empty cells, and a row with one missing value could also contain important information. The helper method is safer for checking whether the entire row is empty.
Common Mistakes
- Deleting rows because one cell is blank. A blank in a single column may indicate missing information, not an empty record. Confirm the row meets your removal rule before deleting it. When only completely empty rows should go, check all data columns with the helper method.
- Selecting the header or unrelated rows. If the header is included in a blank-cell selection, or if your selected range extends into notes and totals, deletion can damage the worksheet structure. Keep the selection within the records and leave the header out of the deletion.
- Using Delete instead of deleting sheet rows. The Delete key clears content from selected cells. To remove the rows and shift the worksheet contents up, use Home > Delete > Delete Sheet Rows or the row-header context menu.
- Assuming visually empty cells are truly empty. A cell containing spaces or a formula that displays an empty string may look blank but still contain content. The COUNTA helper formula counts formulas as content, even when their result appears empty. Check such rows individually if they should count as blank for your task.
- Removing filtered rows without checking the result. Filters can reveal valid but incomplete records. Review the visible entries before deleting, and clear filters when finished so hidden records are not mistaken for missing data.
Tips
- Use a stable required field. A unique ID, if every valid record must have one, is usually a better filter column than an optional field such as notes or a secondary address line.
- Convert the list to a table when appropriate. Select the dataset and choose Insert > Table, making sure My table has headers is selected. Tables provide filter buttons and usually expand as records are added. Keep the helper column outside the data range you test, or adjust the formula range so it checks only the record fields.
- Adapt the helper formula to your columns. Replace A2:Z2 with the first and last columns of your actual data, and change the row number for the first record. Do not include the helper column itself in the range, or the formula will count its own result.
- Understand formulas that display nothing. COUNTA treats a cell containing a formula as nonempty, even if that formula displays no visible text. If you intentionally want to treat formulas returning an empty string as blank, review those records carefully before changing the rule; a row may still contain formulas you need to preserve.
- Use Undo promptly if needed. Ctrl+Z on Windows, or Command+Z on Mac, can undo a recent deletion. Undo may no longer be available after closing the workbook or after some actions, so a saved backup is the more dependable safeguard.
Troubleshooting
The filter dropdown has no (Blanks) option. Check that the filter covers the full data range and that the column actually contains empty cells within that range. Cells with spaces are not empty to the filter, and a filter may not include rows added below the original range. Clear the filter, select the complete list with its headers, and apply Data > Filter again. If the cells only look blank, inspect or clean them rather than assuming the filter is broken.
Rows with visible information were marked for deletion. Stop before deleting, clear the filter, and switch to the helper-column method if your intended rule is fully empty rows. If you already deleted the rows, use Undo immediately. Otherwise restore the saved copy if necessary. A required-column filter cannot distinguish a truly empty row from a partially filled record without additional checks.
The helper formula returns FALSE for a row that looks empty. One or more cells in the checked range may contain a formula, a space, or other content that is not visible. Click through the row and inspect the formula bar. COUNTA counts formulas even when they display an empty string. Decide whether those formulas are important before deleting the row; do not remove them just to make the helper return TRUE.
Deleting rows also removed records you meant to keep. Stop editing and press Ctrl+Z on Windows or Command+Z on Mac if the deletion is still the most recent action. If Undo is unavailable, reopen the backup. Before trying again, confirm that the filter is based on the right column and that only intended row headers are selected.
The filter or delete command is unavailable. The sheet may be protected, or the workbook may be in a mode that restricts editing. Check Review > Unprotect Sheet if you have permission to unprotect it, and confirm that the workbook is not read-only. In Excel for the web, use the available filter controls and delete rows through the row header menu; if a command is missing, open the workbook in the desktop app.
Frequently Asked Questions
What is the fastest way to delete blank rows in Excel?
Filter a required column for (Blanks), inspect the visible records, and delete their sheet rows. It is fast when a blank in that field reliably identifies a row you intend to remove.
How do I delete only completely blank rows?
Use a helper column that checks the full width of each record, filter for rows marked TRUE, and delete those rows. This avoids treating a single empty field as proof that the whole row is empty.
Does Go To Special > Blanks delete entire rows?
No. It selects blank cells in the selected range. You must choose Delete Sheet Rows to remove rows, and you should use this approach only when the selected blanks identify rows that are safe to remove.
Will deleting rows change formulas or formatting?
Deleting sheet rows shifts cells below upward and can affect formulas, references, and layout. Excel often adjusts references automatically, but inspect totals, charts, and formulas afterward. Deleting is different from clearing cell contents.
Can I delete blank rows in Excel for Mac or Excel for the web?
Yes. Use Data > Filter to find blanks, then delete the selected worksheet rows. Ribbon placement and keyboard shortcuts can vary, so use the visible menu commands if the Windows shortcut does not apply.
Related
- Copy in Excel Without Hidden Rows: A Practical Guide
- How to Copy in Excel Down a Column
- Copy in Excel Excluding Hidden Cells: A Step-by-Step Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Can You Make a Word Document a Fillable Form in Word?Yes. In Microsoft Word for Microsoft 365 on Windows, add content controls from the Developer tab—such as text…
- Excel Trendlines: How to Add, Format, and Read ThemTo add a trendline in Excel for Microsoft 365 on Windows, select a chart, choose the plus-shaped Chart…
- How to Freeze Column B in Excel and Keep It VisibleTo keep column B visible while you scroll horizontally in Excel for Microsoft 365 on Windows, select cell…
- Change All Text to Lowercase in ExcelTo change text to lowercase in Excel, use the LOWER function in a helper column, fill it down,…
- Excel Data Validation List AutoFill: What Works?Excel can autocomplete some data-validation dropdown lists in current Microsoft 365 versions, but the feature is not available…
- Excel Workbook vs Excel Binary Workbook: Key DifferencesAn Excel workbook is the general name for a spreadsheet file; an Excel Binary Workbook is one specific…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.