Don’t forget to share this post

Spreadsheets are an excellent tool for data storage and visualization. Excel offers a great variety of functionalities to organize data according to your preferences. Having a dataset with blank or empty rows can lead to confusion or give the impression that data is missing when it might not be.

On top of this, a blank row or cell can affect the correct functioning of an Excel formula or rule. Unless you want to leave these blank rows to show that data is missing, there is a set of quick and easy ways to remove empty cells or rows in Excel.

In this article, you’ll learn how to delete empty rows using Excel keyboard shortcuts, the find and replace function, sorting, and filtering.

How to select and manually remove blank rows in Excel?

If you only need to remove one or two blank rows on a single spreadsheet, then manual work might be easier. However, if this is a daily task, then these keyboard shortcuts will speed up manual work. Let’s explore how you can select blank rows to remove in Excel using keyboard shortcuts.

Use keyboard shortcuts to select blank rows

There are several keyword shortcuts to help you select and then remove empty rows; here are 5 different methods.

Shift + Spacebar

This is the most basic method and can be applied to spreadsheets containing one blank row. This is how you can quickly select a blank row using the shift + spacebar shortcut.

  1. 1. To select the blank row, click on the row heading or click on a cell within the blank row and press shift + spacebar.
How to select and remove blank rows Shift Spacebar
How to select and remove blank rows - Shift + Spacebar
  1. 2. Right-click the selected row and then “Delete” from the drop-down menu.
How to select and remove blank rows Delete
How to select and remove blank rows - Delete
How to lock cells in Excel
How to Lock Cells in Excel? (Cells, Sheets & Formulas)

Cells in Excel spreadsheets can be locked to prevent accidental edits. Here's how to lock specific cells, worksheets and formulas in Excel

READ MOREREAD MORE

Shift + Click last row

This next method is similar but can be applied to spreadsheets where there are several contiguous rows. This is how you can select a blank row using Shift + click the last row.

  1. 1. Select the first blank row and then Shift + click the last blank row.
How to select and remove blank rows Shift click last row
How to select and remove blank rows - Shift + Click last row
  1. 2. Right-click anywhere within the selected rows and select “Delete” from the drop-down menu.
How to select and remove blank rows Delete 2
How to select and remove blank rows - Delete

Ctrl/Cmd + Click rows

This third method applies to spreadsheets where there are several non-contiguous blank rows. This is how you can select non-contiguous blank rows using Ctrl (Windows)/Cmd (MacOS) + click rows.

  1. 1. Click on the first blank row and then Ctrl/Cmd + click the other blank rows you want to delete.
How to select and remove blank rows Ctrl Cmd Click rows
How to select and remove blank rows - Ctrl/Cmd + Click rows
  1. 2. Right-click anywhere within the selected rows and select “Delete” from the drop-down menu.
How to select and remove blank rows Delete 3
How to select and remove blank rows - Delete

Ctrl/Cmd + Minus sign (–)

Before removing the rows, remember that you can select contiguous blank rows by using the shortcut Shift + click last row or Ctrl/Cmd + click rows if they are non-contiguous. This is how you can remove using Ctrl/Cmd + - (minus sign at the top right of the keyboard) for contiguous blank rows.

  1. 1. Select the blank rows.
  2. 2. Press Ctrl/Cmd + – to delete the rows.
How to select and remove blank rows Ctrl Cmd Minus sign
How to select and remove blank rows - Ctrl/Cmd + – (Minus sign)

For non-contiguous blank rows, simply remember to select using the shortcut Ctrl/Cmd + click rows. Excel instantly deletes the selected rows and shifts the rest of the rows up.

Although the methods illustrated so far save a considerable amount of time when working with small or medium datasets, you may need to apply more advanced methods. If you work with larger datasets and various spreadsheets daily, you will find these methods more useful, since they allow you to remove isolated blank rows or cells that are harder to spot.

How to use the Filter command to remove blank rows in Excel?

A great way to remove blank rows in Excel is by using the Filter function, especially when you are dealing with non-contiguous blank rows across the whole spreadsheet.

  1. 1. Select the range containing the blank rows you would like to remove.
How to select and remove blank rows Filter command
How to select and remove blank rows - Filter command
  1. 2. Go to Data > Filter or simply press Ctrl/Cmd + Shift + L to enable the Filter function in Excel.
How to select and remove blank rows Shift L
How to select and remove blank rows - Shift + L
  1. 3. Click on the arrow that appears next to the column names to open the Filter dialog box. You can do so on any of the columns containing the rows to remove. Below the “Search” box, untick the “(Select All)” option.
How to select and remove blank rows Untick Select All option
How to select and remove blank rows - Untick Select All option
  1. 4. Scroll down and tick on the “(Blanks)” option to select all empty rows in the column.
How to select and remove blank rows Untick Select All option
How to select and remove blank rows - Scroll down
  1. 5. As you can see only rows 10 and 20 now show since these were the rows containing the blanks in the filtered column.
How to select and remove blank rows Select Blanks option
How to select and remove blank rows - Select Blanks option

You can now select the blank rows following the Ctrl/Cmd + - method; you can disable the “Filter” function in Excel by clicking on the “Filter” button in the toolbar.

How to use the Find command to remove blank rows in Excel?

Similar to the “Filter” command, this method is also a fast way to spot the blank rows across a large dataset.

  1. 1. To enable the “Find” command in Excel, you can click on the “Find & Select” in the “Home” tab or simply press Ctrl/Cmd + F.
How to select and remove blank rows Select Find
How to select and remove blank rows - Select Find
  1. 2. In the Dialog Box, leave the “Find what” field empty, tick the “Find entire cells only” box. Set the remaining options to “Sheet”, “By Rows”, and “Values”.
How to select and remove blank rows Specify Find what options
How to select and remove blank rows - Specify Find what options

Click on “Find Next” to get all the blanks in the spreadsheet. To delete, remember that you can right-click to delete from the drop-down menu or simply press Ctrl/Cmd + -.

How to Combine Multiple Excel Columns Into One?

There are many ways to combine multiple columns into a single column in Excel. Here's how to do it without losing any data

READ MORE
How to Combine Multiple Excel Columns Into One

How to use the Go to Special command to remove blank rows in Excel?

In the previous example, we only removed the blank rows. The "Go to Special" command in Excel allows you to remove all blanks, including isolated cells.

  1. 1. To launch the “Go to Special” dialog box, go to “Find & Replace” in the Home tab.
How to select and remove blank rows Select Go to Special
How to select and remove blank rows - Select Go to Special

A faster way is to press Ctrl + G and then click on the “Special” button in the dialog box.

How to select and remove blank rows Ctrl G
How to select and remove blank rows - Ctrl + G
  1. 2. Select the “Blanks” option and then “OK”.
How to select and remove blank rows Select Blanks
How to select and remove blank rows - Select Blanks
  1. 3. Excel will automatically select all the blanks in the spreadsheet.
How to select and remove blank rows Automatic selection of blanks
How to select and remove blank rows - Automatic selection of blanks
  1. 4. As in the previous examples, you can delete by pressing Ctrl/Cmd + -; however, in this case, we recommend looking carefully at the following options as “Shift cells up” might distort your data.
How to select and remove blank rows Shift cells up
How to select and remove blank rows - Shift cells up

The methods shown throughout this quick guide are more specific to blank or empty rows rather than empty cells; unless the empty cells or blanks do not align with the rest of the spreadsheet data, you’re strongly advised to restrict these methods to blank rows only.

How to use the Sort command to remove blank rows in Excel?

The Sort command is the best way to select and arrange all blank rows in your spreadsheet to remove them in bulk.

  1. 1. Select the range in your spreadsheet containing the blank rows. So as not to lose the original sorting, I will use the first column ‘VIN’ as the index or reference column.
How to select and remove blank rows Index or reference column
How to select and remove blank rows - Index or reference column
  1. 2. Go to the Data > Sort & Filter and select “Custom Sort”.
How to select and remove blank rows Sort Filter
How to select and remove blank rows - Sort & Filter
  1. 3. Since the ‘VIN’ column will act as the index to keep our original order, change the “Column” option for sorting to “NAME” and then “OK”.
How to select and remove blank rows Change sorting column
How to select and remove blank rows - Change sorting column
  1. 4. Excel has sorted the data according to “A to Z” order, which immediately groups the blank rows at the very end.
How to select and remove blank rows Blanks rows at bottom
How to select and remove blank rows - Blanks rows at bottom

You can now delete the rows and be sure that you have kept your original order; if you want to re-sort the data according to any of the columns, remember to always select “Expand the selection” to keep your dataset intact.

How to select and remove blank rows Expand selection
How to select and remove blank rows - Expand selection

How to manage Excel data with Layer?

Layer is a spreadsheet platform that works on top of Excel and Google Sheets. It allows you to easily manage and automate spreadsheet workflows. Using Layer, you can:

  • Upload or connect your existing Excel or Google Sheets-based budget.
  • Share different sheets or even cell ranges of your spreadsheet with various stakeholders or departments involved in the budgeting process.
  • Automate your communication flows and keep track of your data submissions, contributors, and deadlines.
  • Review every single change made and decide which ones to merge with your spreadsheet or discard.
  • Eliminate errors in your budget or manually copying and pasting data across files.

Sign up and schedule an onboarding call to get started with Layer right now.

Conclusion

This quick and easy guide has shown you how to select and remove blank rows in Excel using a variety of methods using keyboard shortcuts. For more advanced selection and removal, we have also offered various ways based on the Find, Sort, and Filter commands.

For more tips and tricks to sort and manage data in a fast and efficient way in Excel, go through any of the following articles available on the Layer blog.

Looking for more spreadsheet-related content? Take a look at some of our other posts:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Mar 10 2022, Updated Mar 18 2022