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. To select the blank row, click on the row heading or click on a cell within the blank row and press shift + spacebar.
- 2. Right-click the selected row and then “Delete” from the drop-down menu.
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 MOREShift + 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. Select the first blank row and then Shift + click the last blank row.
- 2. Right-click anywhere within the selected rows and select “Delete” from the drop-down menu.
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. Click on the first blank row and then Ctrl/Cmd + click the other blank rows you want to delete.
- 2. Right-click anywhere within the selected rows and select “Delete” from the drop-down menu.
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. Select the blank rows.
- 2. Press Ctrl/Cmd + – to delete the rows.
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. Select the range containing the blank rows you would like to remove.
- 2. Go to Data > Filter or simply press Ctrl/Cmd + Shift + L to enable the Filter function in Excel.
- 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.
- 4. Scroll down and tick on the “(Blanks)” option to select all empty rows in the column.
- 5. As you can see only rows 10 and 20 now show since these were the rows containing the blanks in the filtered column.
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. To enable the “Find” command in Excel, you can click on the “Find & Select” in the “Home” tab or simply press Ctrl/Cmd + F.
- 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”.
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 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. To launch the “Go to Special” dialog box, go to “Find & Replace” in the Home tab.
A faster way is to press Ctrl + G and then click on the “Special” button in the dialog box.
- 2. Select the “Blanks” option and then “OK”.
- 3. Excel will automatically select all the blanks in the spreadsheet.
- 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.
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. 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.
- 2. Go to the Data > Sort & Filter and select “Custom Sort”.
- 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”.
- 4. Excel has sorted the data according to “A to Z” order, which immediately groups the blank rows at the very end.
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.
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: