New Contributor

# Excel Filter Function- Restricted to 5000 rows

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

6 Replies

# Re: Excel Filter Function- Restricted to 5000 rows

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.

# Re: Excel Filter Function- Restricted to 5000 rows

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.

# Re: Excel Filter Function- Restricted to 5000 rows

@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.

# Re: Excel Filter Function- Restricted to 5000 rows

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

# Re: Excel Filter Function- Restricted to 5000 rows

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

# Re: Excel Filter Function- Restricted to 5000 rows

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