Cleaning data quickly in Excel

It is often the simplest things in life that are most rewarding. Finding a new function in Excel that saves you time doing a repetitive task is something I count as rewarding, and this tip about deleting multiple blank rows in a spreadsheet is incredibly simple yet a function that not many know about.

We have all been there. You receive data in Excel that requires some cleaning up before you can do anything with it. There are thousands of rows with intermittent (and often inexplicable) gaps between the data in the column you need. You can either go through and delete each blank row manually or you can use the fantastic Go to Special function.

You will find the Go to Special function on the Home ribbon under Find and Select (it’s on the far right).

The Go to Special function allows you to select certain cells based on various criteria. For the purpose of this exercise, we are looking to select the Blanks. To start this you need to select the row or column you are looking to alter by clicking on the column header, in this example we want to select column A which is a list of UK Prime Ministers.

After this go to Find & Select > Go to Special > Select ‘Blanks’ from the pop up box > Select ‘OK’.

This highlights all of the empty or blank rows in the data set you have selected. All you have to do then is right click on one of the highlighted cells, click delete and select ‘Shift cells up’.

All the blank rows in that column are then deleted and you have saved yourself some serious time in your data cleaning process.

Simple, effective and quick.

AAT members have free access to this and many other Excel Tips through their MyAAT account. To continue exploring more Excel tips please click here.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles