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 A is the list with random blank cells. Column B is the list without the blank cells.
Array formula in B1:B9:
=INDEX($A$1:$A$10,SMALL(IF(ISTEXT($A$1:$A$10), ROW($A$1:$A$10),""),ROW(1:10))) + CTRL + SHIFT + ENTER
ISTEXT($A$1:$A$10) checks if the values in the array are text
ROW($A$1:$A$10) returns the row number
SMALL(IF(ISTEXT($A$1:$A$10), ROW($A$1:$A$10),""),ROW(1:10)) returns the k-th smallest value in a data set. In this case 2, 4, 5, 7 and 8.
Alternative array formula in B1:
=INDEX($A$1:$A$8, SMALL(IF(ISBLANK($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.
Download excel sample file for this tutorial:
remove-blank-cells.xls
remove-blank-cells-without-num-errors.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
ISTEXT(value)
Checks whether a value is text, and returns TRUE or FALSE
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!
Related posts:
- Filter text values existing in range 1 but not in range 2 using array formula in excel
- Extract and sort text cells from a range containing both numerical and text values
- Delete blanks and errors in a list
- Filter common text values in range 1 and in range 2 using array formula in excel
- Extract duplicate text values from a range containing both numerical and text values in excel
- Filter unique text values from a range containing both numerical and text values in excel
- Create a unique distinct text list from a range containing both numerical and text values in excel
- A more userfriendly version of: Automatically filter unique row records from multiple columns
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.