Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there a way of using Excel's built in functions to remove all blank cells in a range?

A quick a dirty way would be to sort the range, so that the empty cells ends up last, but if I don't want to change the order of the element that is not a good way of doing it.

I know I can do this in VBA, and already has, but I'm more interested if this functionality is avaliable from within Excel.

Example:

A

C
D


F

E

should end up with

A
C
D
F
E




share|improve this question

migrated from stackoverflow.com Nov 1 '10 at 12:38

This question came from our site for professional and enthusiast programmers.

9 Answers 9

Highlight your range and go to Edit>Go to>Special and select "Blanks" then "Ok" now go to Edit>Delete.

Edit:

No edit menu in Excel 2007? I must be showing my age. Thanks, Jason Z!

share|improve this answer

Put your sparsely populated range in Column C, stating at cell C2.

In B2:Bn put the following formula:

=IF(ISBLANK(C2),"",MAX($B$1:B1)+1)

This will number the populated cells, 1 to x (where x is the number of populated cells)

In A2:An put the following formula:

=IF(ISERROR(MATCH(ROW()-1,B:B,0)),"",VLOOKUP(ROW()-1,B:C,2,0))

This performs a lookup on the rownumber of Cell An-1 and brings the value across.

As the number of populated cells (x) is less than the number of cells in the range (n), you need to have the IF(ISERROR(MATCH(ROW()-1,B:B,0)),"" to avoid cells Ax+1:An being filled with "#NA"

share|improve this answer

The only way I have done this before is to insert another 'key' column (numbers 1 - n) alongside the sparse range. This allows you to reorder the data in its original sequence after you have sorted out the blanks.

share|improve this answer

While I agree with Richard, there is no Edit menu in Excel 2007. You need to be in the Home tab on the ribbon and click the Find & Select icon. Select Go To Special, check Blanks, and click OK. You can now delete the selected cells.

share|improve this answer

You can also use the data filter advanced filter and filter in place the unique items. You will be left with one blank cell however as that is a unique result for that cell value.

share|improve this answer

context:

  • Excel 2003
  • know the filter mode

pre :

  1. insert a row at the top (maybe not necessary)
  2. select the range you want
  3. "put" a filter on it

now two ideas:

a) Non Destructive : in the column containing A B C D F E, tell excel to show the 'Non Empty' cells so they won't be displayed

b) Destructive : if you want to delete those lines definitively, choose 'Empty', the table will look empty. Select lines then delete them. Deactivate the filtering and you should end up with your data in order.

Hope that helps

share|improve this answer
  1. Make another column and fill it with ordered numbers (1,2,3 ...)
  2. Then make one more column with formula:

    =IF(A1="";B1;"")

Where A1 is column from your example and B1 - added colunm with ordered numbers

Last. Sort data by column with formula

share|improve this answer

Highlight the range. Use Goto (F5) Special Constants. Edit Copy . Click in an unused area of the spreadsheet. Edit Paste. Now, if desired, copy the result back over the origin.

share|improve this answer

You have not mentioned what version of Excel you are using.

Apart from all the answers given here, you could try the 'Removing Duplicates Option' which will remove all the blanks and leave you with one blank cell. It will remove the row also which contain the blank.

Here is how you go about it:

Ribbon > DATA Tab > Remove Duplicates

NOTE: Use this with caution, as this will also delete all other non-blank but duplicate cells. I provided this response with respect to your example.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.