How to Automatically Delete Blank Cells to Organize Data

Delete Blanks LeadData doesn’t always import or paste into Excel as nicely as you want. Sometimes, all the information is there, but extra blanks and spaces are carried along with the data cells. At first glance, this looks like it could be a very tedious problem to solve… It could take ages to manually take them out, and sometimes the data order is sensitive, so it can’t be sorted (to bring all the blanks together). Fortunately, there is a way to select all the blanks in a selection simultaneously for deletion. Let’s walk through an example of how…

Examine the Data

Here is a typical import… We have Employee Names, their Roles, and their Hire Dates, but they are not in the proper columns…

Data Set

Select the Blank Cells

Let’s get them sorted out. Select the data range where there are blanks to remove.

Select Data

Press F5 and click Special in the Go To dialog that appears.

F5 Special

In the Go To Special dialog box, choose Blanks and click OK.

Blanks

The spreadsheet should now look like this, with only the blank cells selected.

Blank Selection

Right-click within one of the selected cells, and choose Delete in the drop-down menu that appears.

Delete Blanks

Excel will now ask which direction you want it to shift the cells that remain. In this example, choose Shift Cells Left and click OK.

Shift Cells Left

The result should be the beautifully organized data that you wanted all along!

Ordered Data

Get the latest Excel tips and tricks by joining the newsletter!

Andrew Roberts ThumbnailAndrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it. You can read more of his writing on his personal blog at NapkinMath.io.

Join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!

23 thoughts on “How to Automatically Delete Blank Cells to Organize Data

  1. Thanks a ton! This saves a lot of redundant manual work. I appreciate it. If there are similar excel data procedures that are commonly used, I would highly recommend adding those on the side of the webpage in a column where viewers can go to.

  2. yuhuhhuhuhu after entering a lot of line breaks in a cell and 4 hours of internet search finally I found what I wanted….shift cell left that is all I was looking for :). THANK YOUUUUUUUU

  3. I did exactly that but received a message saying “No cells were found”.
    The cells I copied have formulas and if functions. Is that why “No cells were found” even thou the cells are blank?

    1. Hi AK!

      Yep – that’s why… If you don’t care about preserving those formulas, you can Copy->Paste Values to remove the formulas from the blank cells (will remove the formulas in all the other cells too).

Leave a Reply

Your email address will not be published. Required fields are marked *