Have you ever ended up with an Excel spreadsheet that contains a bunch of blank cells? Maybe you assembled the sheet by pasting in text from other sheets, or deleted a lot of rows. You could go through the list and individually delete every blank row, but this is a huge waste of time14 Tips to Save Time in Microsoft Excel14 Tips to Save Time in Microsoft ExcelIf Microsoft Excel has consumed a lot of your time in the past, let us show you how to get some of it back. These simple tips are easy to remember.Read More.
Next, Click the “Options” button, and select “Values” from the “Look in” dropdown. Let the “Find What” textbox remain blank and click “Find All”. After this the “Find and Replace” dialog will display all the blank cells. Click any one record and press Ctrl + A to select all the blank cells. Re: Find last empty cell from range, then delete rows? Hi, Can I suggest you upload the workbook and clearly explain which rows you want to delete and why you choose them.
There’s a better way.
First, open the spreadsheet you need to edit and select every cell. You can do this quickly by clicking the little triangle in between the A column and the 1 row. Next, press F5 and click the Special button to open the Go To Special menu. Select Blanks from the list here and hit OK. This will select all the blank cells in the spreadsheet.
Now you can delete them. Make sure you select the Home tab on the Ribbon, then look for the Cells section on the right side. Click Delete here, then Delete Cells. You can try Delete Sheet Rows or Delete Sheet Columns, but those didn’t work properly in our testing. After you click Delete Cells, Excel will ask you how to process the deletion. If you’re deleting rows, leave it at Shift cells up. For column deletion, choose Shift cells left.
After this, Excel will delete the blanks out of your spreadsheet and leave you with just the text. Have a quick look to ensure that nothing was deleted in the process — sometimes the selection you make has strange effects on the deletion.
If you have a Google Sheets spreadsheetExcel vs. Google Sheets: Which One Is Better for You?Excel vs. Google Sheets: Which One Is Better for You?Do you really need Excel? Both desktop and online solutions have their advantages. If you have trouble choosing between Excel and Google Sheets for managing your spreadsheets, let us help you decide.Read More that’s full of blanks, you can paste it into Excel, then apply this method to remove them. Keep the formatting when you do so and you won’t lose anything. For more on Excel, check out some fun things you can create inside it7 Fun & Weird Things You Can Create With Microsoft Excel7 Fun & Weird Things You Can Create With Microsoft ExcelImagine Excel was fun! Excel offers plenty of scope for projects that go beyond its intended use. The only limit is your imagination. Here are the most creative examples of how people are using Excel.Read More.
Do you often run into blank rows in Excel sheets? Let us know if you have a different method in the comments!
Image Credit: GEORGII MIRONOV via Shutterstock
Explore more about: Microsoft Excel, Productivity.
Interesting. I usually just use Sort, which sounds a lot quicker than this method, too. I'll keep it in mind, though as there may be sometime it might be handy.