Excel 2010 Finally Fixes SpecialCells 8192 Limit

If you filter data out of large data sets, you may have run into an issue where you can hit 8192 non-contiguous ranges, which Excel couldn't handle. While this issue has been in Excel for many versions, I can honestly say that I haven't run into it in years, as I've always sorted my data first, to avoid this issue.

To replicate the issue, try this in any version of Excel prior to 2010:

Enter the following data in a blank sheet:

  • A1:    Row
  • B1:    Index
  • A2:    =ROW(B2)
  • B2:    =IF(A1=1,2,1)

Now copy row 2 down about 20,000 rows…

Next, select row 1 and Filter your data on Column B to only show 1's

Now, select from cell A3 to the end of your data in column B and try to copy it… You'll get a message like this one…

Well guess what… that's no longer necessary! In Excel 2010, the Excel team has removed this limit. And while I haven't tested this, the new limit is apparently imposed by the memory in your computer, not a hard coded limit.

It's great to see that such a long standing bug has finally been resolved. 🙂

3 thoughts on “Excel 2010 Finally Fixes SpecialCells 8192 Limit

  1. Ken, In Excel 2010, if you try and fill a Range from A1..XFD1048576 ie: and entire sheet, with even a simple number, say 1, Excel warns you that it may take a long time and then tells you "Excel cannot complete the task with the available resources. Choose less data or close other applications."
    So excel is obviously using memory and I don't have enough and some restrictions still remain!
    I know it is 17 Billion cells +/- a few.
    Maybe an excuse to upgrade my PC?

Leave a Reply

Your email address will not be published. Required fields are marked *