Computer Tutoring Logo

How to Remove Blank Rows in Excel

How to Remove Blank Rows or Empty Lines in Excel

To remove fully empty rows from an Excel list, first check every column that belongs to each record. Identify rows where all those cells are blank, then delete the entire worksheet rows. The video below uses Go To Special > Blanks and Shift cells up; that selects individual blank cells, so it is suitable for an isolated one-column list but can misalign a table with several columns.

Some people call them blank lines or empty lines. In Excel, a blank row is different from a row with just one missing value. Removing genuinely empty rows can help before filtering, preparing a report or building a PivotTable.

Need Help Cleaning Up Excel Data Faster?

We provide online instructor-led and in-person Excel training for teams who need practical help with cleaning spreadsheets, preparing data and working more efficiently in Excel.

  • Useful for admin, finance, operations and reporting teams
  • Online instructor-led or in-person delivery
  • Support with data clean-up, formulas, sorting, filters and more
Best match Intermediate Excel for cleaning lists, filtering data, formulas and PivotTables.
Messy data teams Tailored training using your imports, exported reports and real spreadsheet clean-up tasks.
Next step Power Query training for repeatable data cleaning instead of manual fixes.

To help you remember the important points to this tutorial, please take the opportunity to download the associated exercise file. You will find the exercise file on the link below.

How to delete blank rows in Excel - exercise file

The Excel Blank Row Problem

Here you can see my thorny problem. Below you will see a picture of my Excel worksheet. However, between the teams there are multiple blank rows that I need to remove.

Excel worksheet with blank rows between team records

Before You Delete Blank Rows

Save a copy before you start. Check for formulas, subtotals, notes and intentionally blank separators. Select only the range you want to clean: Excel's Go To Special command selects blank cells, including cells in rows that contain useful data elsewhere.

Remove Fully Empty Rows from a Multi-Column List

  1. Add a temporary helper column beside your list. In its first data row, enter =COUNTA(A2:H2)=0, replacing A:H with all the columns that make up one record.
  2. Fill the formula down and filter the helper column for TRUE. Review the visible rows to confirm that they contain no data you need.
  3. Select the visible worksheet row headers and delete those entire rows. Clear the filter and remove the helper column.

Note: COUNTA treats a cell containing a formula as occupied even if that formula displays an empty string. Check those rows separately if your export uses formulas.

Go To Special Method Shown in the Video

The following steps select blank cells and shift the remaining cells up. Use this method only when cleaning an isolated single-column list, or when you have confirmed that moving cells cannot separate values belonging to the same record.

The first thing I need to do is ensure that I have selected the entire range of data. To do this use the following keyboard shortcut:

Ctrl + End

This moves to the last cell in Excel's used range. Check the result: formatting or previously used cells can make the used range larger than your actual list.

Now, press Ctrl + Shift + Home to select the range.

Remove empty rows - selected range

Next you will want to press Ctrl + G on the keyboard to display the Goto Box.

Get rid of blank rows goto box

Now click on Special.

Remove blank rows goto special box with blanks selected

Now, ensure that blanks are selected. This you can see above. Then click on OK.

Now for the final shortcut hold Ctrl + - this will display the Delete box.

Remove blank rows in Excel delete box

For an isolated single-column list, choose Shift cells up, then click OK. In a multi-column table, this can move values out of alignment; use the full-row method above instead.

Remove blank rows in Excel completed

As shown above, the blank cells have been closed up in the example. Check that any related values still line up correctly.

Delete Blank Rows, Empty Rows or Blank Lines?

People often use these phrases interchangeably, but the distinction matters. A fully empty row has no data in the columns you need; a partly blank row may still contain a useful record. Use the full-row method above for empty rows in a table.

Deleting individual blank cells can shift nearby data up or left and break the layout of your worksheet. If only a few cells are blank, check the surrounding data before deleting anything.

Why should you delete blank rows in Excel?

Blank rows make Excel lists harder to sort, filter and check. They can also interrupt formulas, make shared spreadsheets harder to understand, and stop PivotTables or reports from picking up the complete data range.

In a workplace spreadsheet, removing blank rows is often part of preparing imported data for reporting. A clean list helps admin, finance and operations teams work from one continuous block of data, which makes filters, totals and checks easier to trust.

If you regularly receive messy exports, a manual clean-up may not be the best long-term answer. Our Power Query remove blank rows tutorial shows how to build a repeatable clean-up step for recurring reports.

Further Training

Remove Blank Rows in Excel FAQs

What is the safest way to remove blank rows in Excel?

For a multi-column list, identify rows where every relevant column is empty, then delete those entire worksheet rows. Go To Special selects blank cells, including cells in rows that contain other data.

How do I remove blank rows without deleting data?

Save a copy first. Add a helper column with a formula such as =COUNTA(A2:H2)=0, fill it down, filter for TRUE, and delete the visible worksheet rows. Adjust the range to cover every data column.

Are blank lines and blank rows the same thing in Excel?

People often say blank lines, but Excel calls them rows. Check whether the whole row is empty or only some cells are blank before choosing a deletion method.

Can I remove blank rows before using filters or PivotTables?

Yes. Removing blank rows before filtering, sorting, formulas or PivotTables gives Excel a cleaner block of data to work with and helps reduce reporting errors.

Should I use Go To Special or Power Query to remove blank rows?

Go To Special selects individual blank cells and is suitable for an isolated one-column list. For a multi-column list, identify fully empty rows before deleting. Use Power Query when you receive similar exports regularly and want a repeatable clean-up process.

Why did Excel delete cells instead of whole rows?

Shift cells up moves selected cells within their columns; it does not delete whole worksheet rows. Undo if records no longer line up, then identify fully empty rows before deleting them.

Which Excel training covers data cleaning?

Data cleaning is usually covered in Intermediate Excel, with Power Query covered in more advanced or tailored sessions. You can also browse our Excel training courses for online instructor-led and in-person options.

If you have enjoyed this Excel tutorial, please take the opportunity to view our further tutorials from the links below.

Alternatively, if you would like us to conduct an Excel training course for you or people within your company, then we will be very happy to accommodate your requirements.

TikTok Sneak Peek

Other Excel Videos Tutorials


Other Excel Accounts Tutorials



Close
Thank you for subscribing. You'll receive our newsletter soon.