Have you ever had a time in Excel where there were blank rows, and you needed to remove them? This can be especially frustrating when you’re trying to work on a budget and don’t want the expense of extra blank rows. Here we will go through how to remove blank rows in Excel.
There are so many reasons why you may have blank cells in your Excel spreadsheet: a cell may contain a comment and you forgot to remove it, or some data is missing and has been replaced with a blank cell, or the recipients of the spreadsheet had to “undate” some of the data. Whatever the reason, blank cells make spreadsheets look bad and make data analysis more difficult. In this quick tip I’ll show you how to remove blank rows from your Excel spreadsheet.
Whenever I run a report in Microsoft Excel and sort the data by date, there’s always an annoying blank row between each month. Sometimes I want to remove the blank rows and sometimes I don’t. It just depends on the information that needs to be sorted.
- You can remove blank rows in Excel by first doing a “Find & Select” of blank rows in the document. You can then delete them all at once using the “Delete” button on the Home tab.
- Double check that you’re only deleting the rows that you want, as selecting and deleting individual cells will move the data below it up, potentially disrupting the workflow of your Excel sheet.
- Visit Business Insider’s homepage for more stories.
Though having blank rows in an Excel worksheet isn’t necessarily a bad thing, sometimes they’re unnecessary, as they can make a document take up much more space than intended.
Removing blank spaces and rows in Excel is easy and can be done by using “Find & Select” for all of the blank spaces.
Here’s how to do it on a Mac or PC.
Check out the products mentioned in this article:
Microsoft Office (From $149.99 at Best Buy)
MackBook Pro (From 1,299.99 at Best Buy)
Lenovo IdeaPad (From 299.99 at Best Buy)
How to remove blank rows in Excel
1. Click the Home tab in the top menu bar in Excel, and click “Find & Select” on the right side.
2. Select “Go to Special.”
3. A pop-up box will appear. Select “Blanks,” and then hit “OK.”
Is there any way to remove blank rows in excel? If you are an intermediate level Excel user, you must be familiar with this situation. Sometimes when you try to select all non-blank cells, you will find some blank rows sneak into your selection. You can still delete these rows if you like, but what if you want to remove the blank rows entirely on the worksheet?
4. Excel will then highlight all of the blank cells. Before you delete the blank cells, ensure that only the cells that you want to remove are highlighted. If you just want to remove rows and not individual blank cells, you can always select a cell with “control + click” on a PC, or “command + click” on a Mac, to deselect it. Note that when you delete cells, the data below it will be moved up.
5. Once all the blank rows are highlighted, go to the Home tab and find the “Delete” button on the right-hand side. Select “Delete Sheet Rows.” This will remove the blank rows.
A quick way to delete blank rows in Excel
by Susan Harkins in Microsoft Office, in Software on February 11, 2019, 1:21 PM PST
Deleting blank rows in an Excel data range is easy with this technique, but watch out for unintended consequences.
Blank rows aren’t bad, but in most sheets, they’re definitely undesirable. Excel uses blanks to determine data ranges, and a blank row in the wrong place will inhibit many built-in features. Fortunately, there’s an easy way to remove blank rows from a data range, but this easy technique has the potential to destroy data, so you must be careful.
After selecting the data range in Excel, continue as follows:
- Press [F5].
- In the resulting Go To dialog box, click Special.
- Click the Blanks option and click OK. Doing so selects the blank cells (what you might think of as rows) in the selected range. In this case, that’s A7:E7 and A10:E10.
- Now you’re ready to delete the selected cells. On the Home tab, click the Delete dropdown in the Cells group and choose Delete Cells. Excel will display the Delete dialog box, with the Delete Cells Up option selected. Click OK. Or, press [Ctrl]+-.
- Excel will delete the blank cells from the selected data range.
LEARN MORE: Office 365 Consumer pricing and features
Choosing Delete Cells deletes only the blank cells in the previously selected range. If you choose Delete Sheet Rows in step 4, you could potentially destroy data (often unseen) to the right. Choose carefully when using this option to delete blank rows when you really want to delete just the blank cells. It’s easy to think in terms of rows and choose the wrong option!
SEE: 10 Excel time-savers you might not know about (free PDF) (TechRepublic)
Submit your question
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.
Conclusions
Is there any way to remove blank rows in excel? If you are an intermediate level Excel user, you must be familiar with this situation. Sometimes when you try to select all non-blank cells, you will find some blank rows sneak into your selection. You can still delete these rows if you like, but what if you want to remove the blank rows entirely on the worksheet?