Cleaning Up Excel Worksheets: Eliminating Blank Cells, Rows, and Errors
In this blog post I will demonstrate methods on how to find, select, and deleting blank cells and errors.
Table of Contents
1. Remove blank cells - formula
Column B is the list with random blank cells. Column D is the list without the blank cells.
Excel 365 formula in cell D3:
The FILTER function spills values automatically to cells below, the #NUM error shown in the image above is not returned if you use the FILTER function.
The following array formula in cell D3 is for versions before Excel 365:
This older formula returns a #NUM error when there are no more values to display. Section 1.3 below provides a solution to this problem.
1.1 How to enter an array formula
Excel 365 users can skip these steps, a dynamic array formula in Excel 365 is entered as a regular formula by pressing the Enter key.
- 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.
1.2 How to copy array formula - older versions
Excel 365 users can ignore this.
- Copy (Ctrl + c)Â cell D3
- Paste (Ctrl + v)Â array formula on cell range D3:D8
1.3 Remove #num errors (excel 2007) - older versions
Excel 365 users can ignore this.
When you run out of values to show, the array formula above returns #NUM! errors. You can avoid this if you use the IFERROR function, however use it with great caution. It not only finds #NUM! errors but all errors. So if you formula or cells contain an error you won't see it, the IFERROR function removes that error too.
2. Remove blank cells [keyboard shortcut F5]
The image above shows random cell values in column B, follow these simple steps to remove blank cells in column B.
- Select range B2:B12.
- Press F5 and a dialog box appears.
- Press with left mouse button on "Special..." button.
- Press with left mouse button on radio button "Blanks".
- Press with left mouse button on OK button. The image below shows that the cell range selection changed, now only blank cells are selected.
- Press with right mouse button on on one of the selected blank cells and a context menu appears, select "Delete..".
- Another dialog box appears, press with left mouse button on "Shift cells up".
"Shift cells up" will delete selected blank cells and move non empty cells up. This step will mess up your dataset if you have values arranged as records.
"Entire row" will delete row 3, 6, 8 and 11 in image above. If you have data on these rows they will be deleted as well. - Press with left mouse button on OK button.
The image above shows that blank cells are now deleted.
3. Remove blank rows - formula
This example demonstrates how to filter out blank rows meaning both cells on the same row must be empty.
Excel 365 dynamic array formula in cell E1:
The FILTER function returns values automatically to cells below, this is called spilling by Microsoft.
Array formula in cell E2 is for earlier versions:
3.1 How to create an array formula
Excel 365 users can ignore these steps, a dynamic array formula in Excel 365 is entered as a regular formula by pressing the Enter key.
- Select cell E2
- Paste formula
- Press and hold Ctrl + Shift
- Press Enter
3.2 How to copy array formula
Excel 365 users can skip these steps.
- Select cell E2
- Copy cell (Ctrl +c)
- Select cell range E2:E10
- Paste
4. Delete blanks and errors in a list
The formula deletes blank cells and cells with errors. It doesn't matter if the cells contain numbers or text, they all will be presented in a new column.
Excel 365 formula in cell D3:
Array formula in cell D3:
4.1 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.
4.2 How to copy array formula
- Copy (Ctrl + c)Â cell D3
- Paste (Ctrl + v)Â array formula on cell range D3:D11
4.3 Explaining formula in cell D3
Step 1 - Identify blank cells
The ISBLANK function returns TRUE if cell is blank (empty) and FALSE if not.
ISBLANK($B$3:$B$20)
becomes
ISBLANK({2; 4; 0; 3; "AA"; 0; "CC"; 0; 5; #REF!; 0; #DIV/0!; 0; "ZZ"; "DD"; 0; 7; #NUM!})
and returns
{FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}
Step 2 - Identify errors
The ISERROR function returns TRUE if cell contains an error and FALSE if not.
ISERROR($B$3:$B$20)
becomes
ISERROR({2; 4; 0; 3; "AA"; 0; "CC"; 0; 5; #REF!; 0; #DIV/0!; 0; "ZZ"; "DD"; 0; 7; #NUM!})
and returns
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}
Step 3 - Add arrays
If at least one of the boolean values is TRUE then the result must be TRUE, addition is what we need to use.
| Boolean | Boolean | Multiply | Add |
| FALSE | FALSE | 0Â (zero) | 0 (zero) |
| FALSE | TRUE | 0Â (zero) | 1 |
| TRUE | TRUE | 1 | 2 |
ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20)
becomes
{FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE} +Â {FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE}
returns {0; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 1; 0; 0; 1; 0; 1}.
Step 4 - Convert array to row numbers
The IF function lets you use a logical expression to determine which value (argument) to return.
IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20)))
becomes
IF({0; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 1; 1; 0; 0; 1; 0; 1}, "", {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
and returns
{1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}
Step 5 - Get k-th smallest row number
To be able to return a single value from the array we need to use the SMALL function to extract a single row number. The second argument in the SMALL function uses the ROWS function with an expanding cell reference to extract a new value in each cell.
SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20))), ROWS($A$1:A1))
becomes
SMALL({1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}, ROWS($A$1:A1))
becomes
SMALL({1;2;"";4;5;"";7;"";9;"";"";"";"";14;15;"";17;""}, 1)
and returns 1.
Step 6 - Return value based on row number
The INDEX function returns a value from a cell range based on a row and column number, our cell range is a single column so we need to only specify a row number in order to get the correct value.
INDEX($B$3:$B$20, SMALL(IF(ISBLANK($B$3:$B$20)+ISERROR($B$3:$B$20), "", MATCH(ROW($B$3:$B$20),ROW($B$3:$B$20))), ROWS($A$1:A1)))
becomes
INDEX($B$3:$B$20, 1)
becomes
INDEX({2;4;0;3;"AA";0;"CC";0;5;#REF!;0;#DIV/0!;0;"ZZ";"DD";0;7;#NUM!}, 1)
and returns 2 in cell D3.
4.4 Get excel *.xls
5. How to find errors in a worksheet
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error.
Instructions:
- Go to "Home" tab
- Press with left mouse button on "Find & Select"
- Press with left mouse button on "Go to Special..."
- Press with left mouse button on "Formulas"
- Enable "Errors"
- Press with left mouse button on ok!
If any formula errors exist, they are now selected. The picture below demonstrates error cells being selected.
6. How to find blank cells
In this article, I am going to show you two ways on how to find blank cells. Both techniques are quick and easy.
The first one allows you to find each blank one by one. The picture above shows you the first example, I want to find blanks in column D.
- Select the first value in the column you want to search.
- Press and hold CTRL key.
- Press down arrow key once.
 + 
This will move the selection right above the first blank cell in the same column, see picture below.
Press down arrow key twice still holding the CTRL key will move the selection to the next blank cell in your worksheet.
The second way lets you create a list of all blanks in your worksheet.
- Press with mouse on the first value in the column, then press CTRL + SHIFT + END keys to select all values including blanks in the column.
I have selected cell range D2:D355, see picture above. - Press CTRL + H to open the "Find and Replace" dialog box.
- Don't enter anything, simply press with left mouse button on the "Find All" button.
The dialog box now shows all blank cells with their location, see picture below.
Press with mouse on a row in the dialog box to instantly move to and select that blank cell.
Tip! Use the "Find and Replace" dialog box to fill blank cells with a value. The example below replaces all blank cells with S.
7. How to quickly select blank cells
In this smaller example, column D (Category) has empty cells, shown in the picture above. If your column contains thousands of cells manually selecting those cells, one by one will be tedious and time-consuming.
Luckily there is a wonderful trick that will save us lots of time, the following steps demonstrate how to select blank cells in a cell range:
- Select cell range D3:D15.
- Press function key F5 on your keyboard.
- Press with mouse on button "Special...".
- Select "Blanks"
- Press with left mouse button on OK button
The picture above shows all blank cells selected in cell range D3:D15.
Read the following article on how to enter a formula or data in all selected cells:
Recommended articles
VBA Macro
The following macro will be handy if you often find yourself often selecting blank cells in a specific cell range.
Sub Macro1() Selection.SpecialCells(xlCellTypeBlanks).Select End Sub
Where to copy code?
- Copy above macro
- Go to VBA Editor (Alt+F11)
- Press with left mouse button on "Insert" on the top menu
- Press with left mouse button on "Module" to insert a module to your workbook
- Paste code into the code window
- Exit VBA Editor and return to Excel (Alt+Q)
Save your workbook
To be able to use the macro next time you open your workbook you need to save the workbook as a macro-enabled workbook.
- Press with left mouse button on "File" on the menu, or if you have an earlier version of Excel, press with left mouse button on the office button.
- Press with left mouse button on "Save As"
- Press with left mouse button on file extension drop-down list
- Change the file extension to "Excel Macro-Enabled Workbook (*.xlsm)".
Tip! Link the macro to a button on the "Quick Access Toolbar"Â to have it freely available when needed.
Blank cells category
Excel has great built-in features. The following one lets you search an entire worksheet for formulas that return an error. […]
Table of Contents Delete blanks and errors in a list How to find errors in a worksheet 1. Delete blanks […]
In this article, I am going to show you two ways on how to find blank cells. Both techniques are […]
Excel categories
116 Responses to “Cleaning Up Excel Worksheets: Eliminating Blank Cells, Rows, and Errors”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I would like to use exactly this function, but I cannot get your example to work. Why do you have semicolons in the formula?
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.
I do not know why but wordpress changes "" (double quotation marks) to
[...] 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 [...]
[...] 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 [...]
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?
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.
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!
(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.)
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. Press OK button with left mouse button!
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.
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!
Instead of the column to a row, do a row to a column.
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.
That is great thanks!! it works perfect!!! Now is it possible to do that exact formula to ignore cells that have the virtual blank ""?
not ignore i mean remove im sorry
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.
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
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.
thanks that works great!!!!!
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! ?
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.
Oscar, I just changed your semicolon (near the end of the formula) to a comma, and it worked PERFECTLY. Thank you for your help!
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!
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
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!
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.
I really dont know what is wrong please help thanks
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 Press with left mouse button on and hold and drag the cell down to the last cell you need.
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?
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
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.
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!
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.
Marcol,
Yes you are right. ISERROR($B$2:$B$200) removes formulas that return an error.
[...] how it can be done using formulas. One of them that explained how it can done can be found a https://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 [...]
Oscar,
Thank you sooo much~!The formula removes cells that seem to be blank but contains a space character solve all my problems~!
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!!!
[...] 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 [...]
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?
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.
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.
Hi Marcol,
With helper column is easy :) 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.
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.
@ BatTodor
I'm not sure that I'm following you.
See if you can get 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.
Hi Markol,
Thank you for solution! :)
I will try to find solution without helper column :)
BatTodor ,
I am not sure I understand.
What is x in your table?
Can you provide the formula you are working with?
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 :)
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)
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
please help me for this
i will be very thankful to u all
This is an amazing piece of code.
8-)
BatTodor,
I modified the formula in this post:
Unique distinct values from multiple columns using array formula
This formula works with blanks.
SUNNY,
the same row which cell contains "OK" match with other 6 rows range
Can you explain in greater detail?
Matt Villion,
thanks!
Hi Oscar,
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 :( In my example the digits on first columns are the rows numbers, not entered digits.
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).
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
BatTodor,
Check out this formula:
https://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.
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
Hi Oscar,
THANK YOU VERY MUCH for solution! :) 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 :)
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 :) And now it comes to cleared the picture, but still is hard for me to create my own array formulas.
In any way again want to thank you Oscar!
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?
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.
johnson,
thanks for sharing!
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
Idrissa,
thanks for commenting!
Hello,
I am having the same issue as above with a little twist. Have 4 active wsorksheets in a workbook. Each worksheet has formulas that pull data from the another worksheet. What I need to do is combine one formula with another formula that will pull over the data as well as delete any blank rows. The formula that I have which is pulling over data is: Column A: =IF(Modifications!C4="Open / Active","A",IF(Modifications!C4="Completed","D","")). Modifications being one of the active worksheets. Column B: =IF(Modifications!A4="","",Modifications!A4). Currently, its pulling the data correctly but there are blank rows, I need those deleted and can't figure out how to combine the formulas. Please help!!
BTW, I am comparing a current worklist with a new worklist that will let me know what I need to upload as an Add or Delete.
Lisa S,
See attached file:
Remove-blank-rows.xlsx
Thank you for the great work Oscar!
I found your remove blank rows array formula to be especially helpful. Is it also possible to add another condition onto that? For instance, using your example, would you be able to remove blanks and list only the rows of people over the age of 30?
Thank you for your help!
Yen,
Thanks!
Is it also possible to add another condition onto that? For instance, using your example, would you be able to remove blanks and list only the rows of people over the age of 30?
Yes, it is possible!
Array formula in cell E2:
Thank you Oscar! This formula is exactly what I've been searching for. I just wanted to thank you for the excellent resource you are providing!
Thank you for commenting!
Oscar, thank you so much! I had a chance to test your solution and it works beautifully.
I had no idea about the option to use * in an IF function to accommodate multiple conditions. This will definitely make my life a bit easier :)
I'm going crazy trying to figure this one out...
Sorry to bring you back to the olddays of yore, but I'm still using Excel 2003... (don't laugh-it was free!)
I'm, as you can guess, trying to remove blank lines. I haven'ttried allof the formulas on the ->https://www.get-digital-help.com/2007/09/16/excel-remove-blank-cells/ =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))) =IFERROR(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))), "") <-)
There are three problems with these formula (actually, with me):
(1) I don't know how to modify these formulas to my range (A59 to I129)
(2) This formula creates a #NUM error in the resulting array if the cell in the source array is blank. I don't know how to modify the formula to show blank cells instead of an error. (Yes, I want to show blank cells, not #NUM error indicators.)
(3) I don't know how to modify the second formula to Excel 2003.
Can someone help?
Thanks
hendis
hendis,
This formula should work in excel 2003:
=IF(ISERROR(INDEX($B$2:$C$9, SMALL(IF(FREQUENCY(IF($B$2:$C$9<>"", MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)))>0, MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), ROW(A1)), COLUMN(A1))), "", INDEX($B$2:$C$9, SMALL(IF(FREQUENCY(IF($B$2:$C$9<>"", MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)))>0, MATCH(ROW($B$2:$C$9), ROW($B$2:$C$9)), ""), ROW(A1)), COLUMN(A1)))
Using your cell references:
=IF(ISERROR(INDEX($A$59:$I$129, SMALL(IF(FREQUENCY(IF($A$59:$I$129<>"", MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)))>0, MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), ROW(A1)), COLUMN(A1))), "", INDEX($A$59:$I$129, SMALL(IF(FREQUENCY(IF($A$59:$I$129<>"", MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)))>0, MATCH(ROW($A$59:$I$129), ROW($A$59:$I$129)), ""), ROW(A1)), COLUMN(A1)))
See attached file:
Remove-blank-rows-from-a-cell-range-formula-excel2003.xls
Oscar, thank you for the comment, formula and file.
(One look at the formula you put together tells me that there is no way in the world that I would - or could - have figured this thing out.)
Unfortunately, when I copy and paste your formula into my spreadsheet, I get an error. When I open the Excel 2003 file you included, the spreadsheet shows a VALUE error, but nothing else -- no formula, no text, no nothing.
What am I doing wrong?
hendis
hendis,
My formula uses more levels of nesting than are allowed.
Try this formula:
See attached file:
Remove-blank-rows-from-a-cell-range-formula-excel2003_2.xls
Hi Oscar,
I am trying to use your array formula
=INDEX($C3:$AO3,SMALL(IF(ISBLANK($C3:$AO3),"",COLUMN($C3:$AO3)-MIN(COLUMN($C3:$AO3))+1),COLUMN(A1)))
on values collected using the CONCATENATE function, some of which are zeros. Most of the cells in my row arrays don't return a value but contain the concatenate formula and therefore aren't removed by the above.
Can ISBLANK or another part of the formula be easily adapted to remove cells that don't return a value (showing those that return a zero) but do contain a formula.
Look forward to hearing from you,
Matt
Matt,
Try this array formula:
=INDEX($C3:$AO3, SMALL(IF(ISERROR($C3:$AO3), "", IF($C3:$AO3="", "", MATCH(COLUMN($C3:$AO3), COLUMN($C3:$AO3)))), COLUMN(A1)))
That works perfectly, really appreciate the help!
Hi Oscar,
I've successfully implemented one of your fabulous formulas, but am now stuck on a variation. I am trying to pull names (found in 'Pre-Separation Sign-Up'!C25:C225) of people who have completed at least one section of a course, but not all of it. Incomplete sections are marked "Incomplete" in rows W:BT. Complete sections are left blank.
I think I need to use COUNTBLANK across a dynamic range as part of the selection criteria. I've made a failed attempt inside the second IF statement below. What I want is to count the number of blanks (both real and formula produced) from W to BT (a subset of the larger A:BT range being used in the rest of the formula). If any of these are not blank, I want to import the data from that row. I know the rest of the formula works well, but I just can't quite get the syntax for the COUNTBLANK condition to cooperate.
=IFERROR(
INDEX('Pre-Separation Sign-Up'!$A$25:$BT$225&"",
SMALL(
IF(
FREQUENCY(
IF(('Pre-Separation Sign-Up'!$A$25:$BT$225"")*(COUNTBLANK('Pre-Separation Sign-Up'!$W$25:INDEX('Pre-Separation Sign-Up'!$W$25:$BT$225, MATCH(2,1/'Pre-Separation Sign-Up'!$W$25:$BT$225"")))0,
MATCH(
ROW('Pre-Separation Sign-Up'!$A$25:$BT$225),
ROW('Pre-Separation Sign-Up'!$A$25:$BT$225)), ""),
ROW('Pre-Separation Sign-Up'!$C1)),
COLUMN('Pre-Separation Sign-Up'!$C1)),
"")
Thank you in advance for your help!
-Katie
Oops, here's the bit I'm working on:
(COUNTBLANK('Pre-Separation Sign-Up'!$W$25:INDEX('Pre-Separation Sign-Up'!$W$25:$BT$225, MATCH(2,1/('Pre-Separation Sign-Up'!$W$25:$BT$225""))))<50)
Not quite sure what happened, but my first copy/paste just didn't come through correctly at all! Sorry for the multiple messages!
=IFERROR(
INDEX('Pre-Separation Sign-Up'!$A$25:$BT$225&"",
SMALL(
IF(
FREQUENCY(
IF(('Pre-Separation Sign-Up'!$A$25:$BT$225"")*(COUNTBLANK('Pre-Separation Sign-Up'!$W$25:INDEX('Pre-Separation Sign-Up'!$W$25:$BT$225, MATCH(2,1/('Pre-Separation Sign-Up'!$W$25:$BT$225""))))0,
MATCH(
ROW('Pre-Separation Sign-Up'!$A$25:$BT$225),
ROW('Pre-Separation Sign-Up'!$A$25:$BT$225)), ""),
ROW('Pre-Separation Sign-Up'!$C1)),
COLUMN('Pre-Separation Sign-Up'!$C1)),
"")
Katie G,
See attached file:
Katie-G.xlsx
Array formula in C11:
My Excel hero! Thank you! This worked beautifully!
Ok, so I'm having trouble developing an inventory spreadsheet using Microsoft Excel 2013. I have all of the inventory scanned in, but i am trying to set up a system that will delete a serial number from sheet 1 (warehouse inventory) when i scan the same serial number on subsequent sheets (2,3,4,5,6etc..) no matter what cell the serial number is in. Sheet 1 is the warehouse inventory and all of the others are inventory on different installation technician's trucks and I need to be able to assign them equipment with ease,as well as have the remaining blank cells in sheet1 be deleted automatically....I can't figure it out!!! Time to turn to smarter people PLEASE help!
I am trying to use the formula =IF(ISERROR(INDEX($X$13:$FN$13, MATCH(0, IF(ISBLANK($X$13:$FN$13), 1, COUNTIF($K$5:K8, $X$13:$FN$13)), 0))),"",INDEX($X$13:$FN$13, MATCH(0, IF(ISBLANK($X$13:$FN$13), 1, COUNTIF($K$5:K8, $X$13:$FN$13)), 0))) but I want to allow repeatition please help
I have been trying to find a formula to remove "blank" cells from list A and put then into a consolidated list B. The contents of list A is text and comes from a formula. the consolidated list B works some of the time, but when I change 1 of the inputs that generates list A list B doesn't work.
List A's formula is:
=INDEX($K$5:$P$250,-INT(-ROWS(Z$6:Z6)/$K$1),MOD(ROWS(Z$6:Z6)-1,$K$1)+1) where K5-P250 are the columns of the matrix of text. The matrix can vary in width from 4-6, which II manually input in cell K1 to = the number of columns in the matrix and the consolidated list starts in cell Z6. All works until I use a 4 column matrix and update cell K1 to a 4 or smaller number..
In Z6 I have tried:
=IFERROR(INDEX(Z$6:Z$1000,SMALL(IF($Z$6:$Z$1000"",ROW($Z$6:$Z$1000)-ROW($Z$6)+1),ROWS(AI$6:AI205))),"") Control+Shift+Enter again works great until I update K1 to a 4,3, or a 2 and all of Oscar's variations haven't helped.either.
Thanks for the help in advance.
Doug
Hi Oscar,
Just wanted to thank you for the absolutely incredible stuff you have posted here. At least three times, I have been able to apply your formulas to a project and make it awesome. Just fantastic.
K.
KW,
thank you for your kind words!
In your article "How to extract a unique distinct list from a column in excel" (https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/) the formula (if you continue to copy it down past the last entry), will leave a "0" in the first cell after (i.e. b17) your last entry (i.e. Almagro, Nicolas in cell B16). Using the "IFERROR" function doesn't solve it (it does for the ensuing cells). Neither, that I can tell, do any of the options on this page. How would you change the formula [=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"")] to get rid of the "0" that will appear in cell B17.
Aaron,
I don´t get a "0". See this picture:
I'm using Excel 2013 in case that matters. See picture below. I get a "0" and then the "#N/A" after that. When I add IFERROR the "#N/A" becomes blank, but the "0" still exists.
https://s17.postimg.org/5flnfck8v/Page_1_Index_Example.jpg
Aaron,
How would you change the formula [=IFERROR(INDEX(List,MATCH(0,COUNTIF($B$1:B1,List),0)),"")] to get rid of the "0" that will appear in cell B17?
The named range List is larger than necessary. for example A2:A21 or your range contains a blank cell.
Yes, the list is larger than necessary on purpose. You should, theoretically, be able to autofill data using Index and Vlookup. Having a list larger than necessary allows room to accomodate users with different amounts of data. I ended up eliminating the "0" issue using an IF statement. I was just thinking there was another way. I'm using Excel 2013...maybe that is contributing to it... In any event, my fix worked even if it wasn't elegant. I will upload the file I did so you can see what I was doing and what I did to fix it (you don't need to do anything with it, it's just for your reference if your interested). I appreciate your help. Have a good one.
I am trying to make a workbook with two sheets. One sheet will have data including the prices of various items. Second sheet will have formatted invoice which will only receive selected data from the Sheet one. This selection in sheet one will be done by type Y or N in the end of every record. Those with N should NOT be in the invoice sheet and all with Y should be in invoice sheet without any blank records in it.
I have tried a lot your formula but it doesnt work. Struggling a lot to break it in small segments to work. Can you help me in this regard.
Also when I try =IF(LIST"",1,2) it results #Value!
Riz,
My email is [email protected]. If you want, I can send you a file I did that mirrors what you want to do close enough that you should be able to work out a solution for your situation.
Aaron
aaronb123...outlook.com. Fill in the @ at the ...
I have sent you an email Aaron.
I would like to use your
=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)))
formula against an "Excel Table" (ie using Table Nomenclature), in such a way, that as the table grows in number of rows, the resulting array adjusts accordingly.
Can you advise?
Many thanks for your contribution and suppport.
Kind regards.
DMurray3,
see attached file:
DMurray3.xlsx
Many thanks Oscar... Your recommendation works like a charm...
Kind regards.
Oscar,
I am working with this formula to copy a long list of inventory from one tab to another. I want all the blanks to be skipped. This list will be updated 2-3 times a week. I am looking to only do this formula once and as inventory changes in the other tab, this "Master" list can change as well. Any help or solution?
Thank you.
[…] Excel: Remove blank cells | Get Digital Help – Microsoft … – Problem: Remove blank cells from a list of values? How to create a list with non empty cells? I want to create a new list without blanks. Answer: In this… […]
Tried a few different help forums trying to remove blank lines in a table and the formulas at the top work perfectly for me so thank you. I was just wondering if there's a way to return a different column of data?
i.e. when checking column A for blanks, I want to it return B3 even though it's blank just because A3 has data.
[…] Excel: Remove blank cells […]
hi all
anyone who can give advise of what to do when my file stops responding? i noticed it starts to not respond if i place the formula being discussed here. i have a very large file, about 1000 to 5000 KB. formula worked when tried in the first cell, but the file starts to not respond when i drag the formula down the other cells in a column..
Hi Oscar,
When I tried to copy your example for the Array Formula in cell D3, my new list just repeats "SM" all the way down the list, from D3 to D10. I can't figure out why this is the case. Can you help?
Never mind! I figured it out. If I do an array formula, ROW(A1) doesn't seem to iterate as we read down column B. But that does work if I just drag the formula down to fill the destination column; the iterating ROW(A1) works to select the k-th smallest value from the array.
However, for an array formula, I replaced ROW(A1) with ROW(A:A) so it iterates as we read down the column.
I'm pretty positive I'm not explaining myself very clearly... :-) but it makes sense to me! Thanks!
Hi Oscar,
I am trying to delete blank cells but by shifting the cells left in the row. I don't want to delete the blank cells up as all my data is in rows. Do you a formula that can work? Let me know Thanks,
Thanks a lot Oscar. It helped me. its working awesome.
Hi,
I just can`t seem to get the formula working. I`m following the instructions, but the formula doesn`t remove empty cells. It merely Mirrors the table With alle empty cells. So now i have two tables With empty cells.
Can`t figure out what i`m doing wrong. I`m sure it`s something small and rediculous and i`m going insane trying to figure this out.
Here is my Version of Your formula:
=INDEX($D6$:$D$93; SMALL(IF(ISBLANK($D6$:$D$93); ""; ROW($D6$:$D$93)-MIN(ROW($D6$:$D$93))+1); ROW(A1)))
My blank cells contain the following formula
=IFERROR(IF(enrolled!F5="x";enrolled!C5;"");"")
I need those cells counted as blank and removed to get a proper list of enrolled pupils
Hi. do you have a tutorial on how to remove duplicates?
Elsie Serrano,
Sure, read this:
https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/
If I do an array formula, ROW(A1) doesn't seem to iterate as we read down column B. But that does work if I just drag the formula down to fill the destination column; the iterating ROW(A1) works to select the k-th smallest value from the array.
Hello,
I am trying to remove blank rows from a list that I have in excel.
the list has 200 rows and 7 columns. Part of list ex. shown below:
Orriginal List:
8 8 8 8 8 8 8
5 2
3 5
3
2 3 1
What I want it to look like:
8 8 8 8 8 8 8
5 2
3 5
3
2 3 1
I tried using the formula you posted:
{=IFERROR(INDEX(List,SMALL(IF(FREQUENCY(IF(List"",MATCH(ROW(List),ROW(List)),""),MATCH(ROW(List),ROW(List)))>0,MATCH(ROW(List),ROW(List)),""),ROW(B1)),COLUMN(B1)),"")}
but it isnt working... Even when I take your excel example and paste it on my sheet it doesn't work...
Any suggestions?
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.