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.