37 Responses to “Excel: Remove blank cells”

1. Rory Says:
   November 7th, 2007 at 1:33 am
   
   

   I would like to use exactly this function, but I cannot get your example to work. Why do you have semicolons in the formula?

2. admin Says:
   November 8th, 2007 at 9:24 am
   
   

   It has to do with regional settings. UK and US have colons, and we use semicolons. I will soon change all formulas to UK/US settings.

3. admin Says:
   November 9th, 2007 at 12:36 am
   
   

   I do not know why but wordpress changes "" (double quotation marks) to

4. Unique values from multiple columns using array formulas Says:
   March 16th, 2009 at 5:25 am
   
   

   [...] G1:G16 is where I create the unique list. The downside is that there are blanks where a duplicate is found. See this article on how to remove blanks: Remove blank cells [...]

5. Delete blanks and errors in a list Says:
   March 20th, 2009 at 11:32 pm
   
   

   [...] Filed in Uncategorized on Mar.20, 2009.  Email This article to a Friend In a previous article Remove blank cells, I presented a solution for removing blank cells. This only worked for cells containing text. In [...]

6. Alex Says:
   December 22nd, 2009 at 1:41 pm
   
   

   How can I use these formulas in case if cells are only virtually blank, i.e. "" values are retruned in particular cells by IF functions? Is there any way to remove such cells from the list?

7. Oscar Says:
   December 23rd, 2009 at 9:42 am
   
   

   Alex,

   This formula removes cells that seem to be blank but contains a space character:

   =INDEX($A$1:$A$8, SMALL(IF(TRIM($A$1:$A$8)="", "", ROW($A$1:$A$8)-MIN(ROW($A$1:$A$8))+1), ROW(1:1))) + CTRL + SHIFT + ENTER copied down as far as needed.

8. Greg Says:
   January 17th, 2010 at 5:37 pm
   
   

   This is excellent. Thank you!

   Can you think of any way to do this within a Named Range?

   E.g., so I can define a range called "FilteredList" which only contained the cells with values, and then refer to that list elsewhere in the sheet?

   thanks!

9. Greg Says:
   January 17th, 2010 at 5:38 pm
   
   

   (sorry: I should have been clearer. I want to do it with a named range only -- without creating a hidden sheet containing the filtered list or anything like that.)

10. Oscar Says:
    January 17th, 2010 at 10:14 pm
    
    

    Greg,

    I am using the same example as in this blog post and Excel 2007.

    1. I copied this formula

    =INDEX($A$1:$A$10,SMALL(IF(ISTEXT($A$1:$A$10), ROW($A$1:$A$10),""),ROW(1:10)))

    2. I created a new named range (rng) in the "Name Manager" and pasted the formula into the "Refers to:" field.

    3. Click OK!

    3. I then selected a new range (D1:D9) and typed in formula field: =rng + CTRL + SHIFT + ENTER

    The result were a list without blanks. I have never tried this before but it seems to work.

11. Arielle Says:
    August 9th, 2010 at 8:31 pm
    
    

    Hi! can you do this using the transpose function? so when you transpose a column to a row it removes the blanks in the process?

    Thanks!

12. Arielle Says:
    August 9th, 2010 at 8:46 pm
    
    

    Instead of the column to a row, do a row to a column.

13. Oscar Says:
    August 9th, 2010 at 9:47 pm
    
    

    Arielle,

    =INDEX($E$1:$K$1, 1, SMALL(IF(ISTEXT($E$1:$K$1), COLUMN($E$1:$K$1)-MIN(COLUMN($E$1:$K$1))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.

14. Arielle Says:
    August 10th, 2010 at 2:45 pm
    
    

    That is great thanks!! it works perfect!!! Now is it possible to do that exact formula to ignore cells that have the virtual blank ""?

15. Arielle Says:
    August 10th, 2010 at 2:55 pm
    
    

    not ignore i mean remove im sorry

16. Oscar Says:
    August 10th, 2010 at 4:21 pm
    
    

    Arielle,

    =INDEX($E$1:$L$1, 1, SMALL(IF(LEN($E$1:$L$1), COLUMN($E$1:$L$1)-MIN(COLUMN($E$1:$L$1))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.

17. Arielle Says:
    August 10th, 2010 at 4:36 pm
    
    

    Oscar,
    unfortunately that did not work :/. It did the same thing as the previous formula you gave me, which works great, but the new formula isn't removing the cells that have a space in it

18. Oscar Says:
    August 10th, 2010 at 8:20 pm
    
    

    Arielle,

    Now I understand, try this formula:

    =INDEX($E$1:$L$1, 1, SMALL(IF(LEN(TRIM($E$1:$L$1)), COLUMN($E$1:$L$1)-MIN(COLUMN($E$1:$L$1))+1, ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.

19. Arielle Says:
    August 10th, 2010 at 8:30 pm
    
    

    thanks that works great!!!!!

20. Laura Says:
    August 11th, 2010 at 5:54 pm
    
    

    I have extracted a unique list from a list containing blanks ("LIST" is the named range), using this formula in Excel 2007:

    =INDEX(LIST, SMALL(IF(TRIM(LIST)="", "", ROW(LIST)-MIN(ROW(LIST))+1), ROW(1:1)))

    This works beautifully, except for one small thing. I need the unique list to display within a set number of rows, however the range "LIST" varies in size. I do not want #NUM! to display in the cells below the unique list. How can I make the cells below the unique list look blank, rather than displaying #NUM! ?

21. Oscar Says:
    August 12th, 2010 at 10:11 pm
    
    

    Laura,

    =IFERROR(INDEX(LIST, SMALL(IF(TRIM(LIST)="", "", ROW(LIST)-MIN(ROW(LIST))+1), ROW(1:1))),"") + CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed.

22. Laura Says:
    August 12th, 2010 at 10:37 pm
    
    

    Oscar, I just changed your semicolon (near the end of the formula) to a comma, and it worked PERFECTLY. Thank you for your help!

23. sam Says:
    June 11th, 2011 at 6:48 pm
    
    

    This is awesome!! I have been searching the internet looking for just this. One question though why does this not work?

    =IF(SUM(IF(A1:A10"",1,0))>=ROW(),IF($V$4:$V43="space","",INDEX($V$4:$V43,SMALL(IF(TRIM($V$4:$V$43)="","",ROW($V$4:$V$43)-MIN(ROW($V$4:$V$43))+1),ROW(1:43)))),"")

    Basically i took the formula that removes seemingly blank cells and tried to combine it will the one that removed the #NUM!?

    The formula with out the beginning IF part works great.
    Can this be done to remove the #NUM!? too?

    Thank you!

24. sam Says:
    June 11th, 2011 at 6:50 pm
    
    

    Additional... i did make the initial IF statement have the array v4:v43 i just forgot to put that correct in the post. Thank you

25. Oscar Says:
    June 13th, 2011 at 9:51 am
    
    

    Sam,

    Read this: Delete blanks and errors in a list

    Excel 2007:

    IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

    Thanks for commenting!

26. JOshua Says:
    July 21st, 2011 at 10:51 am
    
    

    I dont know why it is not working on me "+ CTRL + SHIFT + ENTER. Copy cell and paste down as far as needed." What I do is just drag the formula down up to the last cell I need. I am not sure if it is correct.

27. JOshua Says:
    July 21st, 2011 at 11:08 am
    
    

    I really dont know what is wrong please help thanks

28. Oscar Says:
    July 21st, 2011 at 6:03 pm
    
    

    JOshua,

    I am not sure what is wrong.

    =INDEX($B$3:$B$10, SMALL(IF(ISBLANK($B$3:$B$10), "", ROW($B$3:$B$10)-MIN(ROW($B$3:$B$10))+1), ROW(A1)))

    $B$3:$B$10 is the cell range. Adjust range to your sheet.

    A1 (bolded) is a relative cell reference. This cell reference changes when you copy the formula.

    Yes, you can also copy the formula by click and hold and drag the cell down to the last cell you need.

    

29. Mike Says:
    September 19th, 2011 at 4:07 am
    
    

    Wondering if you can help.

    Easiest to put an example down:

    CURRENT TRYING TO DO WOULD BE IDEAL
    A B A B A B
    Name 1 Data 1 Name 1 Data 1 Name 1 Data 1
    Name 2 Name 3 Data 2 Name 4 Data 1
    Name 3 Data 2 Name 4 Data 1
    Name 4 Data 1 Name 6 Data 3
    Name 5
    Name 6 Data 3

    Where the name stays matched up with the data item in the same row. I would think using hlookup or vlookup in some fashion might help but I can't wrap my brain around it. Any thoughts?

30. Mike Says:
    September 19th, 2011 at 4:24 am
    
    

    Well that doesn't look pretty how about this:

    CURRENT TRYING TO DO WOULD BE IDEAL
    --A--------B-----------A--------B-----------A--------B
    Name 1...Data 1------Name 1...Data 1------Name 1...Data 1
    Name 2...______------Name 3...Data 2------Name 4...Data 1
    Name 3...Data 2------Name 4...Data 1
    Name 4...Data 1------Name 6...Data 3
    Name 5...______
    Name 6...Data 3

31. Mike Says:
    September 19th, 2011 at 4:31 am
    
    

    Basically I'm looking to do the same thing the filter does but what I want to end up with is a single sheet that has 10 of these filterd lists above and next to each other.

32. Mike Says:
    September 19th, 2011 at 6:31 am
    
    

    Sorry - I found a work around using your current formulas above, glad I found it! Going to take some doing to set it up but it will be worth it.

    Thanks!

33. Marcol Says:
    October 23rd, 2011 at 10:43 am
    
    

    Great to see a non-volatile solution to this problem.

    If the apparently empty cell is "" returned by a formula then the "blanks" will not be removed,in fact as they are all read as different "blanks" and the list can/will remain unchanged.
    This mod seems to handle this situation.

    =INDEX($B$2:$B$200, SMALL(IF(($B$2:$B$200="")+ISERROR($B$2:$B$200), "", ROW($B$2:$B$200)-MIN(ROW($B$2:$B$200))+1), ROW(1:1)))

    Wrap in IFERROR("formula","") for 2007 and above, or IF(ISERROR("formula"),"","formula") for 2003 and earlier.

34. Oscar Says:
    October 25th, 2011 at 9:26 am
    
    

    Marcol,

    Yes you are right. ISERROR($B$2:$B$200) removes formulas that return an error.

35. Create a list with no blank cells | Online Excel Courses Says:
    December 31st, 2011 at 6:11 am
    
    

    [...] how it can be done using formulas. One of them that explained how it can done can be found a http://www.get-digital-help.com/2007/09/16/excel-remove-blank-cells/ modified the formula to do it for columns. Thennbsp;I setup the formulanbsp;such thatnbsp;he [...]

36. June Says:
    January 12th, 2012 at 10:54 pm
    
    

    Oscar,

    Thank you sooo much~!The formula removes cells that seem to be blank but contains a space character solve all my problems~!

37. Ted Says:
    January 21st, 2012 at 12:21 am
    
    

    Thank you SO MUCH for this!!!!!!!!!! I've been trying to write if statements for the last 2 days to try to filter out unneeded data. I just converted the garbage to blank cells then used your equation..... ITS Beautiful!!!!!!!

    Thanks so much!!!

Leave a Reply