scrolling speed excel with filters

Ah - you want Microsoft to fix a problem but you won't let them see it and you won't let them know whether or not their suggestions helped. I'm afraid that sort of stance will only guarantee that the problem will never be solved. Microsoft engineers are smart, but they are not clairvoyant.

If you look at the date the question was asked you will discover the problem was reported on November 3, 2011.

XinXIn, the Excel product manager at the time, acknowledged the problem on November 6, 2011.

XinXin requested that people affected send him example workbooks. It appears that Burhan Nazir was able to provide XinXIn with an example workbook, and Burhan reoplied in this Q&A with XinXIn's results on February 11, 2012. Apparently in Burhan Nazir's case, converting the data range to an Excel table fixed the problem.

Since early 2011 much has changed. Product updates for Office and Mac OS X have been released. XinXin moved into a new position and is no longer head of Excel development. Sending XinXin your workbooks now won't help.

There are many causation variables that remain to be explored, and example workbooks are required to explore them.  Example workbooks should not contain personal information or private data. Place your example files in a public directory on SkyDrive or your favorite file sharing service, and then put a link to those files in a reply to this message.

Keep in mind this is a forum of Excel users, not Microsoft employees. The MVPs don't work for Microsoft, but we do have a way to get example files to the Excel team.

Before providing an example workbook, please be sure that the file is in a current file format and that it exhibits the problem in Excel version 14.3.5 on a fully up-to-date edition of Mac OS X. By providing example files for other users to experiment with all of us users can see what happens on a variety of different hardware configurations on each others real-world Macs.

So what we need now is some experimentation from anyone affected by this problem.

No, what we really need now is for Microsoft to acknowledge and fix the long standing problem, not experimentation by customers who are losing their patience.

The reality is that most of the people that are experiencing this problem are business users that have gigantic spreadsheets that they cannot share with MS, cannot be re-factored, and do not have the time or patience to mess around with saving in different formats, etc, even if these things somehow worked (which they don't)

The biggest red flag is that it works fine under Windows (surprise surprise!!) .. but also works fine under IBM Symphony on the Mac, which is an Open Office variant.  I'm guessing that if it works fine under Windows, then no one at MS really gives a rat's you know what about the problem.

Thank you, Ceasr, for sharing XinXin's answer. Unfortunately, XinXin is no longer the Excel product manager at Microsoft. The good news is he has let his successor know about this problem.

To elaborate a bit on XinXin's suggestions.

1. To turn a normal text range or querytable into the new Table feature in Excel 2011, use the Table tab of the Ribbon.
2. The default file format for Excel has a fancy name; Office Open XML (.xlsx or use .xlsm if a Macro exists in the workbook). The good thing about XML is that it is plain text and geeky types can play with the HTML and XML code if they want to. The bad thing about XML is that it is plain text and is not computer code, so all the text has to be interpreted, which is very slow. Excel 2011 has a way to save files in a binary code format which can be much faster. Use File > Save As and choose format Excel Binary Workbook (.xlsb) to use this format. Excel .xlsb binary format supports workbooks with and without macros, so any workbook can be saved in this format.

3. I don't know what XinXin means by "refactoring." To me this implies that getting the data out in text format and importing it into a new workbook might help things. I might try saving the data range as .csv and then in a new workbook import the data.

So what we need now is some experimentation from anyone affected by this problem. Do any of XinXin's suggestion improve performance in your problem workbooks? Which of XinXin's suggestions give you the best bang for your buck?

I've done just that and got the following response.  Looks like a known issue.  None of the suggested work arounds work bar refractoring but not always practical.  If this issue is known, can I have an update on when this will be fixed?  It's a crippling bug.

From XinXin:

Thanks a lot for sending me your file. I looked at your file – yes, scrolling is not smooth on this workbook. There is a known issue in XL2011 – the more rows/data you filter out, the more sluggish you will observe during scrolling. There are a couple of ways to work around the issue:

1.       Use table instead of normal range – I resaved your file (attached) in a table.

2.       Use binary file format (such as xlsb, xls, rather than xlsx).

3.       Try refactor/restructure the file, so that you don’t have a huge data table.

It has been what? 1 1/2 - 2 years and the issue still persists? When will we get a fix?

I know of no MAC / OS combination that doesn't exhibit this problem. Snow Leopard/Lion, MacBook Airs, Pros, Minis, etc. Not only is the scrolling useless when in filter mode, but I can't imagine who thought the idea of real-time filtering would be a good one. It makes no sense whatsoever... at the very least it should be a setting you enable/disable for those crazy enough to want it. 

Got spreadsheet with 10000 rows... got 25 categories in one column. Would like to enable 12 o those.... here we go

Filter - click on category 1.... UI freezes (ever heard of non-blocking UI?).... wait.... wait..... wait... it is probably not stuck, right?.... wait.... wait.... maybe I didn't click it... try again.... wait... wait... oh sh#&... I did... and now it is cleared again... start over... repeat 12 more times.

When is Microsoft going to acknowledge these issues and fix them?