Excel: Remove blank cells
Problem: Remove blank cells from a list of values? How to create a list with no empty cells? I want to create a new list without blanks.
Answer:
In this blog post I´ll provide two solutions on how to remove blank cells:
Remove blank cells (array formula)
Column B is the list with random blank cells. Column D is the list without the blank cells.
Array formula in cell D3:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See picture below.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
How to copy array formula
- Copy (Ctrl + c) cell D3
- Paste (Ctrl + v) array formula on cell range D3:D8
Remove #num errors (excel 2007)
Download excel sample file for this tutorial:
remove-blanks_new.xls
(Excel 97-2003 Workbook *.xls)
Functions used in this tutorial:
SMALL(array, k) returns the k-th smallest number in this data set.
ROW(reference)
returns the row number of a reference
INDEX(array;row_num;[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Remove blank cells (keyboard shortcut F5)
- Select range
- Press F5
- Click "Special"
- Click "Blanks"
- Click OK!
- Right click on one of the selected blank cells and select "Delete.."
- Click "Shift cells up"
- Click OK!
Welcome! I am Oscar and here to help you! Feel free to comment and ask excel questions.Don´t forget to add my
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?
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.
November 9th, 2007 at 12:36 am
I do not know why but wordpress changes "" (double quotation marks) to
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 [...]
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 [...]
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?
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.
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!
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.)
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.
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!
August 9th, 2010 at 8:46 pm
Instead of the column to a row, do a row to a column.
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.
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 ""?
August 10th, 2010 at 2:55 pm
not ignore i mean remove im sorry
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.
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
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.
August 10th, 2010 at 8:30 pm
thanks that works great!!!!!
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! ?
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.
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!
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!
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
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!
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.
July 21st, 2011 at 11:08 am
I really dont know what is wrong please help thanks
July 21st, 2011 at 6:03 pm
JOshua,
I am not sure what is wrong.
$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.
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?
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
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.
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!
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.
October 25th, 2011 at 9:26 am
Marcol,
Yes you are right. ISERROR($B$2:$B$200) removes formulas that return an error.
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 [...]
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~!
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!!!
March 5th, 2012 at 6:56 pm
[...] available options and did not have blank spaces between them. I hunted on the internet and found an example, but it used functions that are not available in the Xcelsius-enabled list of Excel [...]
April 6th, 2012 at 9:50 am
Hi Oscar,
I read your blog almost everyday. I found a lot of solution for interesting task here. I want to ask you for help. I tried to find solution for removing blank cells not in one column, but in range with few columns. Because the range is dynamic it possible to be from 1, 2, 3 or more columns.
I have a range similar like this:
A B C D E F
1 x x
2 x x
3 x
4
5 x
6
7
8 x x
The result must be:
1
2
3
5
8
Do you think that is possible without VBA?
April 6th, 2012 at 5:52 pm
One way might be to try this variation with the aid of a helper column.
Add two columns before Column A in your worksheet, or start your data table in Column C. Put as many as many headers as you need in Row 1 (These must be text strings for this example, dates as headers require a slightly different formula)
In B2
=IF(C2="","",COUNTIF(D2:INDEX(2:2,1,MATCH(REPT("z",255),$1:$1,1)),">"""))
Drag /Fill Down as required
In A2, this Array formula
=INDEX($C$2:$C$200, SMALL(IF(($C$2:$C$200="")+ISERROR($C$2:$C$200)+(--($B$2:$B$200=0)), "", ROW($C$2:$C$200)-MIN(ROW($C$2:$C$200))+1), ROW(1:1)))
Confirm with Ctrl+Shift+Enter, not just enter
Drag /Fill Down as required
Column B is a count of your "x" "flags", it could be hidden.
If your "flag" headers are serial dates change this
"REPT("z",255)" to "99^99" in B2.
April 6th, 2012 at 5:58 pm
Sorry, should have added this to the above post ...
Wrap (A2) in IFERROR("formula","") for 2007 and above, or IF(ISERROR("formula"),"","formula") for 2003 and earlier.
April 7th, 2012 at 6:19 am
Hi Marcol,
In fact with using helper formula the task is the same - "Remove empty cell ls in one column (helper)". I wonder if there is way to find non-blank lines from multi column table.
With helper column is easy
In fact yesterday I found way to do that, but the way is not so good in my opinion. I mean that if table is with a lot of columns it will be not possible to use the formula. I tried to made the formula unique - for 2, 3, 4 or more columns, but in the beginning it works for 2 or 3 columns, but for 4 failed to give results. I made some modifications and the formula start to work for 2, 3 or 4 columns... But the number of columns is still limitation - if I need to check more than 4 column table I need to integrate in formula more IFs (the same number as columns).
On Monday I will post the formula to see what I mean.
April 7th, 2012 at 11:12 am
@ BatTodor
I'm not sure that I'm following you.
See if you can download this sample file from my skydrive.
https://skydrive.live.com/?cid=1760EAB0F9AE526F#!/view.aspx?cid=1760EAB0F9AE526F&resid=1760EAB0F9AE526F%21125
Add as many column headers as you want/need, then put "x" in any row in any column.
April 7th, 2012 at 2:58 pm
Hi Markol,
Thank you for solution!
I will try to find solution without helper column
April 9th, 2012 at 7:35 am
BatTodor ,
I am not sure I understand.
What is x in your table?
Can you provide the formula you are working with?
April 9th, 2012 at 9:08 am
Hi Oscar,
In general I want to know all rows which are not empty. "X" mean that (for example) number 1 is appeared in Column B and C. The task is became more complicated because I work with dynamic ranges and it could be one range to be with 1 or 2 or 3 or more columns. I wanted to create one formula for all cases – even the range grows to 10 or more columns. I modified your formula with integrated IFs, but for some strange reason formula failed to give results in some cases for 3 or 4 columns (for now the cases is with maximum 4 columns). After that I modified again formula and put one more IF – if there is 2 or more columns.
The final formula looks like this:
=INDEX(AllRanges;3+SMALL(IF(ISBLANK(INDEX(SelectedRange;;1)); IF(ISBLANK(INDEX(SelectedRange;;2));IF(COLUMNS(SelectedRange)>2;IF(ISBLANK(INDEX(SelectedRange;;3));IF(ISBLANK(INDEX(SelectedRange;;4));"";ROW(INDEX(SelectedRange;;4))-MIN(ROW(INDEX(SelectedRange;;4)))+1);ROW(INDEX(SelectedRange;;3))-MIN(ROW(INDEX(SelectedRange;;3)))+1);"");ROW(INDEX(SelectedRange;;2))-MIN(ROW(INDEX(SelectedRange;;2)))+1); ROW(INDEX(SelectedRange;;1))-MIN(ROW(INDEX(SelectedRange;;1)))+1); ROW(1:1));3)
I have 2 worksheet – in Worksheet1 is database and Worksheet2 is for calculations.
Where AllRanges is
=OFFSET(Worksheet1!$A$1;0;0;COUNTA(Worksheet1!$C:$C)+2;COUNTA(Worksheet1!$1:$1))
SelectedRange is
=OFFSET(Worksheet1!$A$1;3; Worksheet2!$G$2-1;COUNTA(Worksheet1!$C:$C)-1; Worksheet2!$F$2- Worksheet2!$G$2+1)
On Worksheet2 on cell C4 there is cell for choosing. From this choose SelectedRange is defined.
Again on Worksheet2 on cells F2 and G2 there are array formulas to calculate last and first columns for the range which was choosen
=MAX(IF(Worksheet1!3:3=$C$4;COLUMN(Worksheet1!3:3)))
=MIN(IF(Worksheet1!3:3=$C$4;COLUMN(Worksheet1!3:3)))
I hope that I was able to describe situation
April 9th, 2012 at 9:11 am
Ups, I saw that the formula is not clear for reading
=INDEX(AllRanges;3+SMALL(IF(ISBLANK(INDEX(SelectedRange;;1)); IF(ISBLANK(INDEX(SelectedRange;;2));IF(COLUMNS(SelectedRange)>2;
IF(ISBLANK(INDEX(SelectedRange;;3));IF(ISBLANK(INDEX(SelectedRange;;4));"";
ROW(INDEX(SelectedRange;;4))-MIN(ROW(INDEX(SelectedRange;;4)))+1);
ROW(INDEX(SelectedRange;;3))-MIN(ROW(INDEX(SelectedRange;;3)))+1);"");
ROW(INDEX(SelectedRange;;2))-MIN(ROW(INDEX(SelectedRange;;2)))+1);
ROW(INDEX(SelectedRange;;1))-MIN(ROW(INDEX(SelectedRange;;1)))+1); ROW(1:1));3)
April 13th, 2012 at 7:27 pm
hi, thanx 4 your valuable formulas
i have a query, if u help i will be very thankful....
i have a workbook which is having 2 worksheet
i sheet1 i have a data range C1:H100
i just want a formula with this criteria
if i type "OK" in any column rang from J1:J100
the same row which cell contains "OK" match with other 6 rows range
and return the data in sheet2 with ignoring the blank calls
the data should be return in first row
and it should follow the same criteria when in put "OK" in any other column eg:
Sheet1 sheet2
C D E F G H J C D E F G
1 4 5 2 11 "OK" 1 2 4 5 11
April 13th, 2012 at 7:28 pm
please help me for this
i will be very thankful to u all
April 14th, 2012 at 3:08 pm
This is an amazing piece of code.
April 16th, 2012 at 3:01 pm
BatTodor,
I modified the formula in this post:
Unique distinct values from multiple columns using array formula
This formula works with blanks.
April 16th, 2012 at 3:05 pm
SUNNY,
the same row which cell contains "OK" match with other 6 rows range
Can you explain in greater detail?
April 16th, 2012 at 3:06 pm
Matt Villion,
thanks!
April 17th, 2012 at 9:07 pm
Hi Oscar,
In my example the digits on first columns are the rows numbers, not entered digits.
Thank you very much for your reply, but the formula which you send me isn't give result in my case...
It's my mistake - sorry for my poor English
The task looks like this "Remove blank rows from multi-column array". I want to know which rows contains data (I marked with "X") on one or more cells (columns).
April 18th, 2012 at 11:24 am
Hi Oscar,
thanx for giving me ur valuable time
after one day if mind exercise i came to conclusion and this formula works for me but it slowdown my excel file processing.
if possible can you help for the above mention problem
{=IFERROR(IF(V4="OK",INDEX(K4:O4, SMALL(IF(ISBLANK(K4:O4), "", COLUMN(K4:O4)-MIN(COLUMN(K4:O4))+1), COLUMN(A3))), ""),"")}
thanx again
April 18th, 2012 at 12:59 pm
BatTodor,
Check out this formula:
http://www.get-digital-help.com/wp-content/uploads/2007/09/Remove-blanks-from-a-cell-range.xlsx
Unfortunately it is a complicated formula, I wish I could make it smaller.
April 18th, 2012 at 2:06 pm
hi oscar,
is there any formula that can replace a rang of cells value into text
eg:
A B C D E W
1 3 4 6 8
2 4 5 7 9 OK
1 2 6 8 3
now i want to replace the value 1 with (oscar)
is it possible with formula.
one more question is that:
is that any formula which can lookup the cell rang W2:W321 & if any cell contains "OK" then it lookup the cell rang in the same row i.e. A:E
and then count the value just like in example.
thanks in advance
April 19th, 2012 at 2:09 pm
Hi Oscar,
In fact for my current task which I try to solve is enough to know the numbers of non-empty rows and this part of your formula gives needed information =SMALL(IF(FREQUENCY(IF(List"";MATCH(ROW(List);ROW(List));"");MATCH(ROW(List);ROW(List)))>0;MATCH(ROW(List);ROW(List));"");ROW(A2)). In any way it will be big challenge for me to understand the formula 
And now it comes to cleared the picture, but still is hard for me to create my own array formulas.
THANK YOU VERY MUCH for solution!
In general for me is interesting to know the way for creating array formulas – how to trace results, how to choose when use array formula "row-by-row" instead "for-all-area" (I'm not sure that I wrote it right), etc. I read your blog often, but the logic for array formulas is very big challenge for me.
In the past it was like a magic – now I see that you started to explain very detailed the reason of using parts of formulas
In any way again want to thank you Oscar!
April 23rd, 2012 at 12:41 pm
SUNNY,
You can´t replace a value in another cell using a formula. A formula can only return a value in the cell it is entered.
and then count the value
Can you explain in greater detail?
May 8th, 2012 at 8:37 am
The best one:
G14:G19 is the array with blanks and non-blanks. Put it in the first target cell in the column of results. With cursor in formula bar, press Control+Shift+Enter. Now drag it along the entire range.
May 11th, 2012 at 3:22 pm
johnson,
thanks for sharing!
May 12th, 2012 at 11:47 am
Thank you Oscar,
Your approach is great and works fine. I used several hours to find such a solution.
Keep on sharing the knowledge.
Idrissa
May 14th, 2012 at 2:41 pm
Idrissa,
thanks for commenting!