How to Delete Blank Lines in Excel Safely

How-to basics · Updated November 20, 2026

To delete completely empty rows in Excel, select your data range, use Home > Find & Select > Go To Special > Blanks, and delete the selected sheet rows only if every selected blank is part of a truly empty row. If some records have gaps in individual columns, use a helper column or a filter instead; that prevents Excel from removing records that contain other data.

Before you start, save a copy of the workbook or make sure you can undo the change. Deleting blank rows can make a list easier to read, sort, print, or import, but the right method depends on whether you mean rows that are empty across the whole record or just blank cells in one particular column.

What You’ll Need

  • An Excel workbook with the data you want to clean, ideally with a header row.
  • A clear understanding of which columns make a row a valid record. A blank cell in one column does not necessarily make the whole row empty.
  • For the helper-column method, an unused column beside the data. The example below assumes the data occupies columns A through Z.
  • A backup or a saved copy if the rows are important. Deleting worksheet rows can affect formulas, references, and content elsewhere on the sheet.

Step-by-Step

  1. Check the data and decide what counts as blank. Identify the complete range of your records, including every column that might contain meaningful information. Check for notes, formulas, IDs, or other values to the right of the obvious table: a row that looks empty in the first few columns may still hold data elsewhere. If a record is allowed to have an empty value in a particular field, do not use that field alone to decide whether to delete the record.
  2. Save a recoverable copy. Use File > Save As to create a separate copy, or save the current workbook before editing. Ctrl+Z usually reverses a deletion immediately, but it may not be available after closing the workbook or performing other actions. A separate copy is the safer option when cleaning a large list or a shared workbook.
  3. Use Go To Special only when the rows are completely empty. Select the range covering all columns of your records, excluding the header. On Windows, choose Home > Find & Select > Go To Special, select Blanks, and choose OK. You can also press Ctrl+G, choose Special, then select Blanks. Excel highlights blank cells within the selected range. This does not, by itself, identify rows that are entirely empty: a single blank cell in a partly filled record is highlighted too.
  4. Delete rows only after checking the selection. Look through the highlighted cells and confirm that each one belongs to a row with no data anywhere in the record range. If that is true, choose Home > Delete > Delete Sheet Rows. Another way is to right-click a selected cell, choose Delete, and select Entire row. Do not choose Delete Cells and shift cells up unless you specifically want to move cells within columns; that can misalign records. If any highlighted cell is in a partly filled row, press Esc and use the helper-column approach below instead.
  5. For mixed data, add a helper column to identify fully empty rows. Suppose your records use columns A through Z, with row 1 for headers. Put a heading such as Blank row? in AA1. In AA2, enter =COUNTBLANK(A2:Z2)=COLUMNS(A2:Z2), then fill the formula down alongside all records. The formula returns TRUE when every cell from A through Z is blank, and FALSE when at least one cell has content. COUNTBLANK also treats a formula result of an empty string as blank, so inspect the affected rows if you need to keep rows containing formulas even when those formulas display nothing. Adjust the range to match the full width of your data.
  6. Filter the helper results and review them. Select a cell in the data and choose Data > Filter if filter arrows are not already present. Open the filter on the helper column and display only TRUE. Check the visible rows against the original data, especially if the sheet contains merged cells, notes, or content outside the range used in the formula. If the formula has been filled down past the end of the records, those extra rows may also show TRUE; limit the filter or formula range to the actual list.
  7. Remove the confirmed blank rows. With only TRUE rows displayed, select the corresponding visible worksheet rows and delete them using Home > Delete > Delete Sheet Rows. If the data is an Excel Table, select the visible table rows and use the table-row deletion command, rather than deleting cells and shifting them. Take care not to select the header. Clear the filter afterward, review the remaining records, and remove the helper column if you no longer need it.
  8. Check the result before continuing. Confirm that the records above and below each removed gap still match across all columns. Check totals, formulas, and any references that use fixed row numbers. If a record disappeared by mistake, immediately press Ctrl+Z on Windows or Command+Z on Mac. If you have made several changes since the deletion, restore the saved copy instead of relying on Undo.

On Mac, the ribbon labels are generally similar, but keyboard shortcuts can differ: Command+Z undoes an action, and the Go To dialog may be opened with Control+G depending on your Excel settings. Excel for the web has a more limited set of selection tools; if Go To Special is unavailable, use a filter or open the workbook in the Excel desktop app to follow those steps.

Common Mistakes

  • Deleting rows because one cell is blank. Go To Special selects blank cells, not only rows that are blank from end to end. If you delete entire rows from that selection, a record with a missing phone number, date, or other field could be removed. Select all relevant columns and verify the rows, or use the helper formula to test the entire record.
  • Checking too narrow a range. A row may appear empty if you select only columns A through D, even though it contains a comment, formula, or value in column E or farther to the right. Include every column that could hold data, and inspect adjacent columns before deleting.
  • Using Shift cells up instead of deleting whole rows. Shifting only selected cells upward can make the columns no longer describe the same records. Choose Delete Sheet Rows when removing a whole record row, or the table-row deletion option for an Excel Table.
  • Including the header in the selection. The header usually contains text and will not be marked as blank, but selecting too much of the sheet makes review harder and can lead to an accidental deletion. Select just the data rows whenever possible.
  • Assuming a visually empty cell is truly empty. A space, apostrophe, or other invisible character can make a cell nonblank. Conversely, a formula that displays an empty string can look empty while still containing a formula. Decide whether your cleanup should preserve formulas before using a blank test.

Tips

  • Turn a simple list into an Excel Table. Select the data and choose Insert > Table, making sure the header option is correct. Tables provide filter controls and make it easier to work with a defined set of records. Check the selected range before creating the table so it does not omit relevant columns.
  • Use a required field for a quick filter only when it is reliable. If every valid record must have an order ID, filter that column for blanks to find likely empty or incomplete rows. Review the results before deleting: a blank ID may mean the record needs repair, not removal.
  • Keep an original row number if you need to restore the order. Add an index column before sorting or filtering, then retain it until you have checked the cleaned list. Filtering does not normally change the order, but an index helps if you sort or need to compare against the source.
  • Remove spacing without deleting records when appropriate. If the goal is to make a report look compact, consider adjusting row height or filtering out empty rows for viewing instead of deleting data. Deletion is permanent in the workbook once you save and close it, and can affect formulas that refer to those rows.
  • Repeat the check after cleanup. Filter the helper column for TRUE again or inspect the list for remaining gaps. This can reveal rows missed because the first selected range stopped early or because blank-looking cells contain spaces.

Troubleshooting

Go To Special marks blanks in rows that have data. That is expected: the command selects empty cells, not fully empty records. Cancel the selection without deleting rows. Use the helper-column formula across the complete record width, or carefully verify each selected row before choosing Delete Sheet Rows.

The formula returns FALSE for a row that looks empty. Check whether one of the included cells contains a space, an apostrophe, or another invisible character. Also make sure the formula range includes the columns you intend to test and that the formula was filled through the correct rows. If the row contains a formula returning a visible blank, COUNTBLANK may treat that result as blank; decide whether that row should be retained before removing it.

Filtering does not show the rows you expect. Confirm that the filter covers the helper column and all data rows. Check that TRUE and FALSE are actual formula results, not text typed into the cells, and clear other active filters that could hide records. If the list is an Excel Table, use its filter arrow; for a normal range, choose Data > Filter with a cell inside the range selected.

Rows were removed but the columns no longer line up. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac. The deletion may have shifted cells rather than removing entire worksheet rows. Restore the saved copy if Undo is no longer available, then repeat the process using Delete Sheet Rows or the table-row deletion command.

Excel will not let you delete the selected rows. The worksheet may be protected, the workbook may be shared or restricted, or the selection may include part of a merged range. Check Review > Unprotect Sheet if you have permission to unprotect it, and consult the workbook owner if protection is intentional. Merged cells can complicate selection; avoid unmerging or deleting until you understand how that layout is used.

The web version does not show the same command. Excel for the web may not offer every desktop selection option in the same place. Use Data > Filter and the helper-column method if available, or open the workbook in Excel for Microsoft 365 on Windows or Mac for Go To Special and more complete row-selection controls.

Frequently Asked Questions

Can I delete blank rows without removing rows that have missing information?
Yes. Test the full record across all relevant columns with a helper column, then delete only rows confirmed empty. If a record can legitimately have missing fields, do not treat a blank in one field as proof the whole row should be removed.

Does Go To Special > Blanks find entirely empty rows?
No. It selects blank cells in the selected range, including blanks inside partially completed records. It is safe for deleting whole rows only after you confirm that every selected blank belongs to a row that is empty across the full data range.

Will deleting blank rows change my formulas?
It can. Excel may adjust references when rows are deleted, and formulas using fixed row positions may return different results. Check totals, lookups, charts, and formulas after cleanup, particularly when they reference the affected section.

Can I remove blank lines from an Excel Table?
Yes. Filter the table or use a helper column to identify the rows, review the results, and delete the confirmed empty table rows. Use the table-row deletion command so the table remains structured; avoid shifting individual cells upward.

What if a blank-looking row contains formulas?
Decide whether the formulas are useful before deleting it. A formula may display an empty result but still be part of a calculation or template. The helper formula shown above treats cells whose formulas return an empty string as blank for its test, so review those rows if formulas must be preserved.

Can Excel remove blank rows automatically?
Filtering or using Go To Special can make removal quick, but Excel still needs a reliable rule for deciding which rows count as empty. For a one-time cleanup, review the identified rows before deletion. For recurring imports, keep a documented helper-column check and verify its range each time.

Related

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.