Last week Keith asked how he can ignore blanks in a range referenced by a Data Validation list.

I think you’ll agree the list below on the right with the blanks removed looks a lot nicer.

## Extract a List Excluding Blank Cells

To get the ‘no blanks’ look we first need to create a new list that excludes the blanks.

Here’s our original list containing blanks starting in cell A2 through to A9:

And in column C we’ll create our new list that excludes the blanks.

Stop looking at the formula bar, I don’t want to put you off 🙂

## Formula to Extract a List Excluding Blanks

The formula in cell C2 is:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""),ROW(A1))),"")

And in English it reads:

Look at the range A2:A10 and return the first value if it is text (i.e. not blank and not a number). If this formula returns an error just enter nothing (as denoted by the "").

*This is an array formula so it needs to be entered by pressing CTRL+SHIFT+ENTER.*

## SMALL’s Big Role

Let’s expand on the SMALL(IF(ISTEXT part of the formula first:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF({TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE},{1;2;3;4;5;6;7;8;9},""),{1})),"")

The SMALL function syntax is:

=SMALL(array,k)

We’ve used the IF function to return the array argument. More on that in a moment.

The k argument is the position in the array we want to find. i.e. 1 is the smallest, 2 the next smallest and so on.

You’ll notice in our formula we used ROW(A1) to dynamically return the k argument. i.e. ROW(A1) simply evaluates to 1 and returns the first value in the list that isn’t blank.

We use ROW(A1) so that as we copy the formula down column C the ROW function reference will go up in increments of 1 (because the reference to A1 is relative, not absolute).

So, in cell C3 the ROW function part of the formula will be ROW(A2) which evaluates to 2 and will return the second value in the list that isn’t blank, and so on.

## IF Function

The IF part of the formula first identifies which cells in the range contain text by using the ISTEXT function to test the cells in the range A2:A10.

If the cell does contain text it returns a TRUE, and if not it returns a FALSE, which you can see in orange below:

In the list above the first TRUE is referring to cell A2, the next TRUE refers to A3, and the first FALSE refers to A4 and so on.

Whilst this ISTEXT function is evaluated inside the formula, it might be easier to visualise how it works if we insert the formula in column B like this:

That list of TRUE’s and FALSE’s in column B is the same as the list in the IF formula.

Now, because we actually need a list of cell numbers that contain text (for the SMALL function’s ‘array’ argument), as opposed to TRUE/FALSE values returned by the ISTEXT function, we use ROW($A$1:$A$9) to return an array of numbers 1 through 9 like this:

{1;2;3;4;5;6;7;8;9}

*Note: we need 9 numbers because there are 9 cells in the range A2:A10.*

Next the IF function finishes evaluating and returns an array of numbers that represent the cell numbers in the range A2:A10 that contain text like this:

`=IFERROR(INDEX($A$2:$A$10,SMALL({1;2;"";4;"";6;7;8;""},ROW(A1))),"")`

Again, we can see how Excel does this if we put the different components of the formula in separate columns in our workbook:

That is; rows 1,2,4,6,7 and 8 in the range A2:A10 contain text.

Next the SMALL function finishes evaluating and goes from this:

`=IFERROR(INDEX($A$2:$A$10,SMALL({1;2;"";4;"";6;7;8;""},{1})),"")`

To this:

`=IFERROR(INDEX($A$2:$A$10, 1),"")`

## INDEX’s Turn

Now finally INDEX knows which value to return from the range A2:A10, which is the first value.

Remember the syntax for INDEX is:

INDEX(reference, row_num, [column_num], [area_num])

=IFERROR(INDEX($A$2:$A$10, 1),"")

*Note: We’re only using the first two arguments for INDEX. The column_num and area_num are optional arguments as denoted by the square brackets.*

## IFERROR - The Fall Guy

IFERROR picks up the pieces if there is an error in the result and simply returns a blank.

This is important because the next thing you need to do is copy this formula down column C to at least row 10 (because later on you might enter values in cells A4, A6 and A10).

However, because there are currently only 6 values in column A you would end up with errors in cells C8:C10 if you don’t use IFERROR.

## Download the Workbook

Inside the Workbook is an option that ignores blanks instead of the example above which relies on finding text.

This is useful if your data isn’t text, or is a combination of text and numbers.

*Note: the workbook is a .xlsx file. Please ensure your browser doesn't change the file extension to a .zip when you download it.*

Next week we’ll look at how we can use this list in a Data Validation list that ignores the blank cells at the end. i.e. cells C8:C10 in our example above.

Bernie Lee says

This formula worlks great, i am now trying to get it to work across columns where the values are horizontal as opposed to vertical in your example, I have tried everything with the formula but i cannot get it to work.

e.g. Values appear like this

Chamomile, Lavender, “blankcell”, Liquorice, “blankcell”, ChaiGinger, Oolong

Catalin Bombea says

Hi Bernie,

Please take a look at this comment from this page, there is a link to a OneDrive file with multiple examples: horizontal source data, vertical source data, horizontal output, vertical output. These examples should cover any situation.

Cheers,

Catalin

Duane says

Hi how do you make the same formula work on a different tab on the same file?

Mynda Treacy says

Hi Duane,

You can use the same formula, but prefix the cell references with the sheet name inside apostrophes and an exclamation mark on the end e.g.:

Mynda

Stephan, England, Hull says

VERTICAL COLUMNS in a spreadsheet, how to copy excluding non Blank Columns, what Formula would you use?

I know how to do this HORIZONTAL: ROW by ROW with INDEX MATCH, COUNTIF & IF, but VERTICAL?

Catalin Bombea says

Hi Stephan,

Excel has also the COLUMN() function, not only ROW().

You can try, in this version, you have to copy the formula to the right, not down, it will produce an horizontal list with no blanks, as you can see, the initial list is also horizontal:

If you want the result to be in an vertical list, but the initial range is in a horizontal layout, you can try this version (the only change is in the last part of the formula, ROW function is replacing the COLUMN function, this is what makes the difference. If you want to understand why, you have to read again the article, the answer is there 🙂 ):

Catalin

Stephan, England, Hull says

Hi Excel 2003 doesn’t work with, or perhaps some of the Cell Refs are not Absolutes?

My method for VERTICAL > HORIZONTAL is:

TRANSPOSE row 1ST Column fields to use as Headers:

=TRANSPOSE($A2:$L8) array formula Ctrl, Shift & Enter

COPY/CELL LINK: Row > Column Headings

INDEX MATCH from TRANSPOSE ROW HEADER & 1COLUMN:

=IF(AF1<0,"",INDEX($A$2:$L$8,MATCH(AF$1,$J$2:$J$8,0),MATCH($AE2,$A$1:$L$1,0)))

Catalin Bombea says

Hi Stephan,

IFERROR function is not available in excel 2003. You have to use another function to avoid errors. See this file from our OneDrive folder, you have 2 versions in sheet 2 that will work in excel 2003 too.

Catalin

S.Narasimhan says

Dear Hi,

I would like to use the above formula for numbers and column blanks.

The formula does not work if ROW is changed and ISTEXT is Changed to ISNUMBER. They work only for row blanks.

Please guide.

Regards

Catalin Bombea says

Hi,

Can you please upload a sample file with your problem on our Help Desk System? (create a new ticket) It will be easier to understand your problem and help you.

Catalin

Steve H says

Hi S.Narasimhan,

did you get an answer? I have the same issue trying to use numbers – it must be dynamic as my original array changes and it has to automatically regenerate a new list without blanks.

Catalin Bombea says

Hi Steve,

Please take a look at this comment: Horizontal List

There can be many variations:

– the initial list can be in a horizontal range or in a vertical range;

– the returned list can also be in a horizontal range or in a vertical Range,

– the original list is produced by formulas and the “blanks” returned by those formulas are not exactly blanks, other formulas will not see the “” string as a blank cell

– the list should display only numbers, or only text, or both.

You have a sample file with multiple examples on our OneDrive folder.

If this is not what you need, you can try uploading a sample file with your problem on our new forum.

Cheers,

Catalin

Ed Sykora says

Hello,

For small ranges, I find it easier to remove blanks by highlighting the range, pressing F5 to bring up the goto dialogue box, clicking special and selecting blanks, then click ok and right click and select delete and choose to shift cells or delete the row or column.

Best regards,

Ed

Mynda Treacy says

Hi Ed,

Good idea, although this formula is for use when you don’t want to have to repeat that process each time the list gets updated and more blanks potentially appear. With the formula you write it once and it’s done.

Mynda

KTak says

I created a worksheet that consists of a column of drop-down lists for a user to select from. For this example, I will use “YES” & “NO” as the choices, and the column is B1:B12.

Then, I have a separate column to enter specific text when a specific selection is selected from the drop-down list.

For example, =IF(B1:B12=”YES”,”Testing”, IF(B1:12=”NO”,”Coaching”, IF(B1:B12=”N/A”,””,””))).

So, I am receiving the appropriate text from the various selections, but I want to eliminate the blank spaces when a user selects “N?A” from the drop down list, and just have the text in the cell(s) below, moved up so it is a continuous list.

Later, I want to highlight the boxes that have “Testing” in them, but I will tackle one issue at a time.

Thank you in advance for your assistance.

Catalin Bombea says

Hi,

Can you please upload a sample file with your problem on our Help Desk? (create a new ticket). It will be a lot easier to provide a personalized answer rather than a general solution.

Cheers,

Catalin

KTak says

Thank you very much, Catalin. I have submitted the requested sample file in a new ticket.

Joey says

Hello, thank you for the tutorial!

I was hoping you might be able to help me with a problem. I want to take data from Table1 and automatically populate it into Table2. However, before this data can be moved to Table2, it has to meet certain requirements. For example, Table1 could have three rows. Supplier 1, Supplier 2, and Supplier 3. The following column lists as red, *blank cell*, red. I only want Supplier 1 and Supplier 3 to transfer to Table 2 as they do not have a blank in the following column.

I’ve tried using:

=IF(‘Raw Machining Data’!$A3=””,””,INDEX(‘Raw Machining Data’!A3, MATCH(‘Raw Machining Data’!$F3, ‘Raw Machining Data’!$F3,0)))

and also

=IFERROR(INDEX(‘Raw Machining Data’!A3,MATCH(‘Raw Machining Data’!$F3, ‘Raw Machining Data’!$F3,0),SMALL(IF(ISTEXT(‘Raw Machining Data’!A3),ROW(‘Raw Machining Data’!$A$1),””),ROW(‘Raw Machining Data’!A1))),””)

Neither work. The first returns #N/A values for blanks and the second just returns a blank. I want it so that it doesn’t even list a blank cell. Does this all make sense lol?

Thank you for your time!

Mynda Treacy says

Hi Joey,

I’d use a PivotTable to extract the data and filter out the blanks. This tutorial might point you in the right direction:

http://www.myonlinetraininghub.com/excel-pivot-tables-to-extract-data

Another option is Power Query if you are familiar with it.

Kind regards,

Mynda

cardnexus says

Hi,

How do you make row(a1) in the array dynamic? When i select the range and type in the formula in the first cell and hit ctrl + alt + enter, the row reference is fixed on a1 through out the entire array.

Please help.

Thanks,

Mynda Treacy says

Hi Cardnexus,

You enter the formula in the first cell in your range and then copy it down as opposed to a multi-cell array formula where you select all cells that you want to contain your formula and enter it all in one go.

Mynda

Diane says

I have been looking for an easy solution to this issue for awhile now and this is great!

The only problem I seem to be having is that for me, in column A I am using the CONCATENATE formula to combine First and Last names. When I use this the formula used in this article in Column C doesn’t seem to work. I even tried to copy and paste only the values, but that still doesn’t seem to work. Can you help?

Catalin Bombea says

Hi Diane,

Please upload a sample workbook with your formula, so we can see what’s wrong, there is no clue in your description.

Use our Help Desk.

Cheers,

Catalin

Dritan says

Awesome solution. I was looking for such a solution in Excel and I couldn’t make it, but finally you provide a great solution.

Thank you again.

Mynda Treacy says

Thanks, Dritan. Glad I could help 🙂

Dritan says

Hi Mynda,

It works perfect within the same sheet (I just remove blank cells from a range), but when I use to put the result in different sheet doesn’t work? Can you help me why it may not work?

Thank you,

Dritan

Mynda Treacy says

Hi Dritan,

It should work on any sheet in the file. Can you please send me your workbook via the Help Desk so I can take a look?

Thanks,

Mynda

Justin says

I have to sort data as you describe above. However, the data starts at N7 and continues through N208. I can’t seem to get the formula above to work. Can you please help?

Thanks

Catalin Bombea says

Hi Justin,

Can you upload a sample workbook with your attempts, so i can see what is wrong?

You can use the Help Desk: http://www.myonlinetraininghub.com/helpdesk/

Thank you,

Catalin

emman says

Hi Mynda!

I need your help on this ‘how to select all non blanks in a activesheet? its not for particular range its for entire sheet. expecting ur help ASAP.

Bruce says

Thank you for your help with this example and explanation. I have been working on this problem for a long time now and this solved it. Very Helpful!

Mynda Treacy says

That’s great to hear, Bruce. Glad we could help.

Kind regards,

Mynda.

Berne says

Hi Mynda,

I am having the same issue as some of the others on here with the blank cell actually being a “” result from a IF Function. I can’t seem to rewrite the list without the blanks. It needs to be a live document too. Have you got any more ideas?

thanks,

Berne.

Mynda Treacy says

Hi Berne,

The formula above is testing for text i.e. IF(ISTEXT(…, if you can change your IF function so that it returns a number instead of “” e.g. a zero, or 1, then the array formula will ignore them and give you the desired result.

If you don’t want to see the 0 or 1 results in amongst your other text you could use conditional formatting to hide them by formatting them to match the cell colour. e.g. if your cells are white, format all 0 values white.

I hope that option works for you.

Kind regards,

Mynda.

eugene says

Hi Mynda,

thank you for this very handy tutorial.

After reading I tried the above suggestion of changing the IF function so that it returns a number instead of “” however the array formula doesn’t ignore the cell and posts the number in the list.

Any help would be fantastic.

All the very best,

e

Catalin Bombea says

Hi Eugene,

If the source range contains zero length strings (“”) returned by a formula, then this version that is evaluating the length of the text instead of identifying blanks is more flexible:

replace:

with:

Here is how the formula should look like:

Catalin

Kevin Ashley says

Is it possible to accomplish the same thing with a 2D range/table/array?

I want to build a vertical one column table on one worksheet comprised of only the occupied cells from 2D range/table/array.

Mynda Treacy says

Hi Kevin,

I think it would be simpler to use a PivotTable with multiple consolidation ranges to generate this list. If you have Excel 2007 onwards finding the multiple consolidation ranges option is a bit tricky. You first need to put the icon for the PivotTable Wizard in your Quick Access Toolbar (QAT):

Right click QAT > Customize > Choose from Commands Not in the Ribbon > add the PivotTable and PivotChart Wizard icon to the QAT.

Now you can click the icon and select: Multiple consolidation ranges > click next.

Select ‘Create a single page field for me. Click next > add your two ranges to the ‘All ranges’ area (Note: you need your ranges to be at least two columns wide even though your data is only in one column. You can ignore the second column).

Click next and you should have your list. You can use the filters in the PivotTable to exclude the blanks.

I hope that helps. If you get stuck send me your file via the help desk with clear instructions on what you want and where and I’ll take a look.

Kind regards,

Mynda.

Kevin Ashley says

That didn’t seem to work and maybe because my blanks are actually [“”] resulting from a formula that is in every cell of my source table/2D-range/array.

Kevin Ashley says

I have provided my working file to the Help Desk under same subject as this blog.

I want the occupied cells found in the table located on the “Crosstab view” worksheet to fill the first column of the “Schedule” worksheet as I’ve demonstrated by simply copying the values from first several occupied rows of the “Crosstab view” to the first column of the “Schedule”.

Kevin Ashley says

I admit that I have no experience with Pivottables. So, maybe, I didn’t quite get your procedure correctly.

Kevin Ashley says

I think I may have figured it out after some experimentation. I found I could achieve my result be specifying a series of overlapping double columns in the Pivottable wizard (eg, specifying columns E & F for the first range, F & G for the second range, and so on until I cover the entire table on the “Crosstab view” worksheet). I think I also need to avoid specifying blank columns. Correct? Or, at least, avoid having the first column a double column specification be blank. Will the pivottable automatically grow if the ‘blank’ cells become occupied in the “Crosstab view” OR shrink if occupied cells become blank? Or will I need to rebuild the pivottable each time the contents of the “Crosstab view” change?

Is it possible to edit the pivottable to add or delete specified ranges from the “Crosstab view” (eg, if blank column becomes occupied)?

Mynda Treacy says

Hi Kevin,

Now that I have seen your data and the amount of it, I’d say your best option is some VBA. Here is a link where you can get an addin that will do this for you.

I hope that helps.

Kind regards,

Mynda.

Kevin Ashley says

I can’t seem to located the addin he references on that page, either on the internet or listed among the addins in the corresponding Excel Options menu. Can you point me at where/how to get the “Filter unique distinct values” addin?

Mynda Treacy says

Hi Kevin,

It’s not strictly an add-in, it’s a UDF (user defined formula) that Oscar has written. I also can’t see where you can download it from his page. You might like to leave a comment on the page asking him where you can get the file.

Kind regards,

Mynda.

Michael says

Mynda,

I looked in dozens of websites for an answer to this vexing problem but yours was the one that was the easiest to understand/use.

Two quick questions:

1) Can you use dynamic arrays =IFERROR(INDEX(SpecialList,SMALL(IF(ISTEXT(SpecialList),ROW(SpecialList)),ROW(A1))),””)?

It returns a circular reference if placed in all three array locations.

2) Conceptual question really, why does it return 0 if you were to insert a column above the data. Aka, in your example, your data on columns A/C and row 2. If you were to insert a new row on row 1, it turns the cells into 0.

Thank you!

Mynda Treacy says

Hi Michael,

Thanks for your kind words 🙂

I’m not sure what you mean by ‘placed in all three array locations’. You should be able to use dynamic arrays but I’m not sure where you’re placing the formula.

If you insert a row above the data the formula dynamically updates the ROW component of the formula to also move down a row and therefore returns the wrong result.

I hope that helps. If you want to send me your workbook with the dynamic array circular errors I can take a look to see what is going on.

Kind regards,

Mynda.

Roxann Walker says

how do i get this to work with text and numbers returned from a formula instead of text? The formulat may return a null or NA vlaue so would need to skip over these.

Mynda Treacy says

Hi Roxann,

It sounds like you have a few variables we need to deal with. If you send me your file via the help desk we’ll take a look.

Kind regards,

Mynda.

bigbuck12 says

What tips or tricks do you have for cells that are blank as a result of another formula. What I have found is that cells with formula results that display a blank are ingnored with the example provided. I’m looking for something simular to remove those cells as a result from my formula. Dealing with Dates… “Oh what fun!” My attempt to remove both weekends and holidays in a colume for each month has been a phased approach and now I am left with a dynamic table with blanks where my formula has removed both weekends and a defined list of holidays.

Carlo Estopia says

Hi bigbuck12,

Please send your file via HELP DESK and let’s see what we can do about it.

Cheers.

CarloE

Tony Dee Vee says

I don’t suppose there was an answer to the above query regarding blank cells from a formula. I can’t believe I have actually gotten to a point where I’m using the above very helpful stuff…. but I just need to make the non-blank blank cells become blank! BLEEP.

Cheers

Mynda Treacy says

Hi Tony,

That’s a tricky one, which is why it’s taken me so long to reply to you, sorry!

My workaround is to have your formula return something other than a blank. e.g. a character that is not going to be found anywhere else in your list. e.g. an = sign or + sign.

You can then use Conditional Formatting to highlight this unique character. Then use Go To Special (CTRL+G > Special) and select all cells containing conditional formatting and delete the contents.

Note: you could try filtering the list on the special character and then deleting them but sometimes this deletes data in the hidden rows. I’ve never found it to work consistently, so dabble with care!

Phew, not ideal I know. I’ll keep thinking about this one and if I come up with a better solution I’ll let you know.

Kind regards,

Mynda.

Attique Tahir says

Dear Dear Mynda,

I came to know about this very usefull formula through your email. Really it is very very helpful and great formula in data validation.

I downloaded your sample work book, to do some practis I copied a range from A1:K9 and pasted on A12:K23 and changed formula from

=IFERROR(INDEX($A$2:$A$9, SMALL(IF(ISBLANK($A$2:$A$9),””, ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1), ROW(A5))),””)

TO E13

=IFERROR(INDEX($A$13:$A$20, SMALL(IF(ISBLANK($A$13:$A$20),””, ROW($A$13:$A$20)-MIN(ROW($A$13:$A$20))+1),ROW(A12))),””)

and used shift+ctrl+enter to enter the formula. and copied fromula down to E23 but result is blank although a13 to a23 has data with blank rows

I compared formula again and again but I could not find any error

Further more, I copied A1:K9 to L1:V9 and copied changed address in formula in non-blank formula as

=IFERROR(INDEX($L$2:$L$9, SMALL(IF(ISBLANK($L$2:$L$9),””, ROW($L$2:$L$9)-MIN(ROW($L$2:$L$9))+1),ROW(L1))),””)

and it worked fine.

Please help me to trace the problem

Attique

Carlo Estopia says

Hi Attique,

Please start with the ROW part with

A1:=IFERROR(INDEX($A$2:$A$9, SMALL(IF(ISBLANK($A$2:$A$9),””, ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1),

)),””)

=IFERROR(INDEX($A$13:$A$20, SMALL(IF(ISBLANK($A$13:$A$20),””, ROW($A$13:$A$20)-MIN(ROW($A$13:$A$20))+1),

)),””)

Note that this is a part of the SMALL function’s argument ‘k’. SMALL, by the way has two arguments

the arrayin which it will choose from, andka number to signify rank starting fromthe smallest. Going back, ROW(A1) function will return 1. Once drag to the next row, A1 turns A2 which

will return 2 etc..

More on SMALL FUNCTION

Cheers.

CarloE

Attique Tahir says

Dear CarloE,

Thank you for your prompt reply, you mentioned exactly what I did wrong, I considered it a relative address that should be changed according to new location, but that was wrong. Thank you so much for your help

Attique

Carlo Estopia says

Hi Attique,

Okie Dokie. You’re welcome. It’s Mynda’s credit by the way, I learned it from her.

Thanks for reminding with the term ‘relative address’ 😉

Cheers.

CarloE

Ajoy Banerji says

I have a simple problem in Excel, what would you say about this?

There are two separate reports as two separate Excel file. The old one is on a server and the new one is pulled once each week from a system where data is updated all the time. Column G is added to the new report as a Insert Column function.

However, 100s of new rows are added each week. So the old report will not be having those new rows actually.

The logic necessary to populate G column on the new report is – “If the respective cells for column A, E, F and H in last report and new report ARE THE VERY SAME, what ever was there in the old report in the Column G in that row should get populated in the new report Column G.

Otherwise it should be populated with blank.”

This means all newly added rows should be populated with blank in the G Column of the new report.

I am looking for some Macro or Merge or any other type of Formula that can be used in this case but VLOOKUP will not work in this case. Any suggestions or guidance would be highly appreciated. I also must say that it is impossible to do this manually as there are more than 6000 rows in the reports.

Carlo Estopia says

Hi Ajoy,

You may also mail me via HELP DESK

I have created here a simple function named MatchOldNew.

If you’re new to VBA, you may encounter security questions. Well, just agree to that; Otherwise, you won’t avail of VBA.

Also, if VBA has not yet been set, Go to Excel Options, Trust Center, Trust Center Settings, Enable Macro and ActiveX.

Here’s the deal:

1) ALT + F11 (this will bring you to the VBE Window)

2) While in the VBE Window, Select INSERT menu, Add Module(Note:NOT Class Module)

3) Copy and paste this function to your new Module

4 Add a Commandbutton and paste this Code:

how to add a CommandButton

Please Note that you can Change

AjoyNew and AjoyOldwhich are my assumptions to your Sheet Names.Also you can Change the

start Rows for RN and ROto your actual report. It may not be necessarily beboth One(1). For example, Old may start at 2 and New may start at 3.

Cheers.

Carlo

Pradeep says

Hi Mynda,

Thanks for this, i always scare whenever i see an array formula.

you explained it very well here. Thanks once again

Mynda Treacy says

Thank you, Pradeep 🙂