I can’t believe I haven’t already written a tutorial about Excel’s Go To Special feature. It is one of my favourite (a.k.a. favorite if you’re in the U.S.) closely behind VLOOKUP.
If you import data from other systems you’ll invariably have to manipulate it to get it the way you need before you can analyse it.
And data interspersed with blank cells/rows is one of the most common problems.
Thankfully there is a dead easy tool you can use to get rid of these, like the blank rows below, in seconds.
How to Remove Blank Rows with Go To Special
- Select range of data containing spaces. Taking the example above just the cells A1:A30 will do (row 30 is the end of my table), you don’t need to select the whole table.
- CTRL+G to open the Go To dialog box.
- Click the ‘Special…’ button and select ‘Blanks’ > click OK.
- Your table should now look like this with the blank cells selected.
- Now you can right-click your mouse and choose Delete > Entire Row.
- Voila, blank rows are gone.
Now you know about the Go To Special tool why not take a look at some of the other criteria you can Go To.
Like Go To Special > Objects.
I use Shapes quite a bit and Grouping them together makes them easier to handle. A quick way to select them all so they can be Grouped is to use Go To Special > Objects.
And then there’s Go To Special > Conditional formats. This makes it quick and easy to edit cells containing Conditional Formats.
If you liked this tool then you'll probably like Text to Columns too. It's great for splitting data from one column to multiple, which is often required when you import data from other systems.
Namrata says
Hi,
How to add the intermitant text of one cell in a column to below cells of the same column?
Mynda Treacy says
Hi Namrata,
I’m sorry I don’t understand your question. Are you able to send me an example Excel file and specific instructions on what you’re trying to do?
Thanks,
Mynda.
Jaz says
Hi,
How do I eliminate all empty rows (even the ones that come after my data rows)? I’m trying to import names and addresses into my database program and all the empty rows (after my data rows) are there being imported. I just want my excel sheet to contain rows with data and not empty rows. Thanks in advance for your help. Jaz
Mynda Treacy says
Hi Jaz,
Excel by default has thousands of rows. Your database isn’t importing blank rows, they’re already there. If you really want to remove them then you can hide them – they’re still there, you just can’t see them.
My advice is to get used to them. They’re on every sheet. You’ll be wasting your time hiding them every time you set up a new worksheet.
Kind regards,
Mynda.
Peter says
can you please tell me how to get rid of all the entries in the go to dialog box? they just seem to accumulate and can’t declutter them; thank you!
Mynda Treacy says
Hi Peter,
Those ‘entries’ are the named ranges in your workbook. If you want to delete them you have to delete the named ranges in the Name Manager, which you’ll find on the Formulas tab of the ribbon.
Note: you should check the names aren’t in use before you delete them otherwise you’ll break something you might later find you need.
Kind regards,
Mynda.