Forum Discussion
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
SergeiBaklan mathetes .. Thanks for your help and i used ISERROR function to get it fixed.
- Thank you for the update. I only tried to illustrate that FILTER() has no formal limit on rows number.
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.
- Thanks, I did and it worked. I found that if the array have value #N/A, then filter function is throwing error.
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.
