Excel Filter Function- Restricted to 5000 rows

[mshakeeb]

Hello,

I am trying to use the new filter function on a table which contains 20K Lines. It looks this function is restricted to 5000 Line item. When I provide the range with in 5K rows , I am getting the results and if its more than 5K , the result is "#N/A" . Any  solution available? Thanks

[#2]

@mshakeeb 

It seems only remotely possible that the FILTER function is limited to 5000 rows. Possible, but extremely unlikely.

Usually when we humans get a #N/A error message, it's we who have erred, not Excel. Usually. OR there's something in the data to which we're applying the function in question.

I'd check your syntax, all of the references, and then the data itself. There could be something in row 15,678 that causes the #N/A.

Do something like doubling (via copy and paste) the 5000 rows that DO work and see if your FILTER handles the resulting 10,000 rows.

[#3]

@mshakeeb 

You may perform small experiment.

New workbook, 1 in A1, in A2 enter =A1+1, Ctrl+Shift+Down, Ctrl+D. Now you have 1 048 576 rows in data.

Go to B1 and enter =FILTER(A:A,A:A>100000).  It returns 948 576 rows of data.

[#4]

@mathetes  Thanks, that's right there is #N/A in cell after 5K.  Ideally i want to filter the cells having #N/A . But if the array contains #N/A then filter function is throwing error #N/A. 

[#5]

Thanks, I did and it worked. I found that if the array have value #N/A, then filter function is throwing error.

[#6]

@Sergei Baklan @mathetes .. Thanks for your help and i used ISERROR function to get it fixed. 

[#7]

Thank you for the update. I only tried to illustrate that FILTER() has no formal limit on rows number.