# How to Delete Blank Rows in Excel (Step by Step)

If you want to know how to delete blank rows in Excel without touching your real data, the fastest reliable method is Go To Special > Blanks followed by Delete Sheet Rows. It finds every truly empty cell in your selected range, and Excel deletes the whole rows that contain them. This guide walks through that method, a filter-based alternative, and the traps that cause people to lose good rows.

## Quick Answer

- Select the range that holds your data, including the header row.
- Go to Home > Find & Select > Go To Special > Blanks. Excel highlights every empty cell in the selection.
- Go to Home > Delete > Delete Sheet Rows. Excel removes the entire rows that contained those blanks.
- If your data has gaps inside real records, filter instead and delete only rows where the key column is empty.
- Always work on a copy or press Ctrl+Z immediately if the result looks wrong.

## Before You Start

Blank rows cause real problems. They break sort ranges, split tables so that filters and pivot tables miss rows below the gap, and make functions like `COUNTA` return misleading counts. Removing them is usually the right move, but the method you pick depends on what "blank" means in your sheet.

There are two different situations, and they need different tools.

The first is a fully empty row, where every cell in that row is empty. Go To Special handles this cleanly.

The second is a row that is empty in one column but has data in another. Go To Special will still flag the empty cell, and Delete Sheet Rows will remove the whole row, including the data you wanted to keep. If your table has legitimate gaps, use the filter method in the "Other Ways to Do It" section instead.

Two habits protect you here. Save a copy of the workbook before you start, or work on a duplicate sheet. And remember that Ctrl+Z undoes a row deletion, so if the sheet suddenly looks wrong, undo before you do anything else.

One more thing to check first. A cell that looks empty may contain a space, an apostrophe, or a formula that returns `""`. Those cells are not blank, so Go To Special skips them. You can test with `=COUNTBLANK(A2:A11)` and compare it to the number of cells that look empty. If the counts differ, you have spaces or other characters to clean up first. COUNTBLANK still counts formulas that return `""` as blank, so check suspect cells in the formula bar too.

## Step by Step

1. Select the full range of your data, including the header row. In the example below that is A1:C11. Selecting the header matters because it keeps the header out of the blank detection and keeps your columns aligned after deletion.

2. Go to Home > Find & Select > Go To Special. In the dialog, choose Blanks and click OK. Excel selects every empty cell inside your range.

3. Go to Home > Delete > Delete Sheet Rows. Because whole rows are selected through the blank cells, Excel removes the entire row for each one, not just the empty cell.

4. Check the result. Your data should now be contiguous, with no gaps between records. Press Ctrl+Z if anything looks wrong.

If you prefer to see the blanks before deleting, apply a filter first. Select your range, go to Data > Filter, open the filter dropdown on a key column, and uncheck everything except the blank entry. That shows you exactly which rows will go.

## Worked Example

Take a small grade sheet with Student, Score, and Result columns. Rows 3 and 7 are completely empty, which splits the table into three blocks. The Result column uses a pass rule.

| Row | A (Student) | B (Score) | C (Result) |
|-----|-------------|-----------|------------|
| 1 | Student | Score | Result |
| 2 | Ana | 78 | `=IF(B2>=70,"Pass","Fail")` -> displays Pass |
| 3 |  |  |  |
| 4 | Ben | 65 | `=IF(B4>=70,"Pass","Fail")` -> displays Fail |
| 5 | Cara | 91 | `=IF(B5>=70,"Pass","Fail")` -> displays Pass |
| 6 | Dan | 54 | `=IF(B6>=70,"Pass","Fail")` -> displays Fail |
| 7 |  |  |  |
| 8 | Eli | 82 | `=IF(B8>=70,"Pass","Fail")` -> displays Pass |
| 9 | Fay | 70 | `=IF(B9>=70,"Pass","Fail")` -> displays Pass |
| 10 | Gus | 88 | `=IF(B10>=70,"Pass","Fail")` -> displays Pass |
| 11 | Hana | 61 | `=IF(B11>=70,"Pass","Fail")` -> displays Fail |

The rule in every Result cell is the same:

$$ \text{Result} = \begin{cases} \text{Pass} & \text{if Score} \ge 70 \\ \text{Fail} & \text{if Score} < 70 \end{cases} $$

Ana scores 78 and passes. Ben scores 65 and fails. Cara scores 91 and passes. Dan scores 54 and fails. Eli scores 82 and passes. Fay scores exactly 70 and passes, because the test is greater than or equal to 70. Gus scores 88 and passes. Hana scores 61 and fails.

Now apply the method. Select A1:C11, then Home > Find & Select > Go To Special > Blanks, then Home > Delete > Delete Sheet Rows. Rows 3 and 7 disappear. The remaining rows keep their original formulas and values, so the pass and fail results do not change. The sheet now reads Ana, Ben, Cara, Dan, Eli, Fay, Gus, Hana with no gaps.

This is the key point about deleting blank rows in Excel. You are removing rows, not shifting values between rows, so the formulas in column C stay attached to their own row and keep returning the same answers.

## Other Ways to Do It

**Filter and delete.** Select your range, go to Data > Filter, open the dropdown on the column that must never be empty, and select only the blank checkbox. Excel hides the rows with data and shows the empty ones. Select the visible rows, right-click, and choose Delete Row. Then clear the filter with Data > Filter. This is safer when some rows are only partly empty, because you decide which column defines "blank."

**Sort to push blanks together.** Select the range, go to Data > Sort, and sort by a column that has no blanks in real records. Empty cells sort to the bottom by default in ascending order, so all the blank rows collect at the end and you can delete them in one block. This changes row order, so use it only when order does not matter.

**Helper column with a formula.** Add a column with `=COUNTA(A2:C2)` next to each row. Rows that return 0 are fully empty. Filter that helper column for 0 and delete the visible rows. This gives you a numeric check you can audit before deleting anything.

**Power Query.** If you import data regularly, load it into Power Query and use Home > Remove Rows > Remove Blank Rows. The step is saved, so every refresh cleans the data automatically. This is the best option for repeated imports.

If your goal is the opposite, restoring rows you hid by accident, see [how to unhide rows in Excel](/blog/data-analysis/how-to-unhide-rows-in-excel). If you need to clear repeated records instead of empty ones, [how to remove duplicates in Excel](/blog/data-analysis/how-to-remove-duplicates-in-excel) covers that. When you want to inspect which rows are empty before deleting, [how to filter in Excel](/blog/data-analysis/how-to-filter-in-excel) explains the filter controls. And if you need to add rows back after cleaning, [how to add multiple rows in Excel](/blog/data-analysis/how-to-add-multiple-rows-in-excel) shows the quick ways.

## Troubleshooting

**Go To Special says "No cells were found."** Your selection contains no truly empty cells. The cells probably hold spaces or formulas returning empty text. Select the range, press Ctrl+H, put a single space in Find what, leave Replace with empty, and replace all. Then run Go To Special again.

**The wrong rows disappeared.** You selected a range that did not cover all your columns, so Excel deleted rows based on blanks outside your data. Undo with Ctrl+Z, then reselect the full range including the header.

**Blank rows remain after deleting.** They may sit outside the range you selected, or they may contain a formula. Click one of the "empty" cells and look at the formula bar. If you see a formula, the cell is not blank.

**Formulas now point at the wrong cells.** Deleting rows makes Excel adjust references automatically, which is usually what you want. If a formula referred directly to a cell in a deleted row, whether the reference was relative or absolute, it returns `#REF!`. Undo and rebuild the reference.

**The table lost its formatting.** Deleting rows can break banded rows or borders. Reapply the table style from the Table Design tab, or reformat the range.

## Common Mistakes

- Selecting only one column before running Go To Special. Excel then deletes rows based on blanks in that column alone and can wipe rows that had data elsewhere. Fix: select the entire data range, header included.
- Using Go To Special on a table with legitimate gaps. A missing middle initial or an optional phone number counts as blank, so the whole row goes. Fix: filter on a column that must always be filled, or use a helper column with `=COUNTA`.
- Forgetting that a cell with a space is not blank. Go To Special skips it and the row survives. Fix: clean spaces with Find and Replace first.
- Deleting rows one at a time from the bottom up. It works but it is slow and error-prone on large sheets. Fix: use Go To Special or a filter to handle all rows at once.
- Not checking the result before saving. Once you close the workbook, Ctrl+Z no longer helps. Fix: verify the row count with `=COUNTA(A2:A11)` before and after, and keep a backup copy.
- Assuming blank rows are harmless. They split sort ranges and cause filters and pivot tables to ignore rows below the gap. Fix: remove them before you build any analysis on top of the data.

## Limitations

Go To Special treats a cell as blank only when it is truly empty. Cells holding a space, a non-breaking space, or a formula that returns `""` are not detected, so rows that look empty to you can survive the cleanup. You have to clean those characters first, and that step is manual.

The method also deletes whole rows, which means any data in those rows goes with them. On a sheet where a row is empty in the columns you selected but populated far to the right, outside your selection, that data is lost. Always confirm your selection covers every column that matters, and keep a backup until you have checked the result. For very large sheets, Power Query is more reliable because the cleanup step is recorded and repeatable.

## Frequently Asked Questions

### How do I delete blank rows in Excel without deleting data?

Select the full data range including the header, then use Home > Find & Select > Go To Special > Blanks, followed by Home > Delete > Delete Sheet Rows. Go To Special selects every empty cell, so Excel removes any row that has at least one empty cell in the range, not only fully empty rows. If your data has legitimate gaps, filter on a key column instead and delete only rows where that column is blank.

### Why does Go To Special say no cells were found?

It means Excel found no truly empty cells in your selection. The cells that look blank probably contain a space, an apostrophe, or a formula returning empty text. Use Find and Replace to remove stray spaces, then run Go To Special again. You can confirm with `=COUNTBLANK(range)` and compare it to the number of cells that appear empty.

### Does deleting blank rows break my formulas?

No, in most cases it helps. Excel adjusts both relative and absolute references automatically when rows are deleted, so formulas stay attached to their own row and keep returning the same results. The exception is a formula that refers directly to a cell in a deleted row, which returns `#REF!`. Undo with Ctrl+Z and rebuild that reference.

### Can I delete blank rows automatically every time?

Yes, with Power Query. Load your data into Power Query, use Home > Remove Rows > Remove Blank Rows, and load the result back to a sheet. The cleanup step is saved in the query, so every refresh removes blank rows again without any manual work. This is the best approach for recurring imports.

### What is the difference between a blank row and a row with empty cells?

A blank row has no content in any cell. A row with empty cells has data in some columns and gaps in others. Go To Special flags both, because it works cell by cell, and Delete Sheet Rows then removes the entire row. If you only want to remove fully empty rows, add a helper column with `=COUNTA(A2:C2)` and delete rows where it returns 0.

## References

This article draws on the standard references listed under Further Reading.

## Further Reading

- [Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician](https://doi.org/10.1080/00031305.2017.1375989)
- [Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology](https://doi.org/10.1186/s13059-016-1044-7)
- [Microsoft Support: Excel help and learning](https://support.microsoft.com/en-us/excel)
- [McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics &amp; Data Analysis](https://doi.org/10.1016/j.csda.2008.03.004)
- [Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1008984)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)

## Related Articles

- [How to Unhide Rows in Excel (Step by Step)](/blog/data-analysis/how-to-unhide-rows-in-excel)
- [How to Remove Duplicates in Excel (Step by Step)](/blog/data-analysis/how-to-remove-duplicates-in-excel)
- [How to Freeze Rows and Columns in Excel (Step by Step)](/blog/data-analysis/how-to-freeze-panes-in-excel)
- [How to Filter in Excel (Step by Step)](/blog/data-analysis/how-to-filter-in-excel)
- [How to Add Multiple Rows in Excel (Step by Step)](/blog/data-analysis/how-to-add-multiple-rows-in-excel)