asked on

MS Excel 2010 Pivot Table processor issue

I have user with a MS Excel 2010 spreadsheet with 70,000 rows (that I have even whiddled down to 10,000 rows for testing purposes). When he tries to run a pivot table on one of the fields (grouping the data in that field by ranges), the first grouping works well. The next grouping uses almost 100% of the processing power of the PC (we also noticed it only uses one of the two processors). We built a Xen Desktop and tried in this environment (with LARGE processing power) and it still ate up ALL the processor power. Where should we look for possible issues?

redmondb

Hi, Scott-McRaeGroup.

Obviously, the ideal would be for you to post the actual file here, but I presume that the data is sensitive. As a second best, please create a copy of the file, with a few dummy records and a pivot as in the original, specifying exactly what you were doing when the CPU usage soared.

You mentioned that only a single core was used. Please check Excel's Advanced Options (Alt-F-T, click on the "Advanced" tab and scroll down a couple of times) - is multi-threaded calculation enabled and, if so, how many processors? If that's set up for multiple threads then it's possible that there's an Event macro interfering. What's the file-type (xlsx/xlsm, etc)?`If it's xlsm, xlsb or xls then please save the file as an xlsx (to definitively strip out macros), recyle Excel, open the file and see has anything changed.

Enough for the moment!

Thanks,
Brian.

Scott-McRaeGroup

ASKER

Thanks so much for your suggestions. The file was a .csv file, so I saved it as an .xlsx file. I also verified multi-threading was turned on (or the two processors I have on my machine). I have attached the file, but it doesn't have the pivot table in it because I can't get it to create the table. Basically, they are trying to pivot on the mileage field with the following groupings: 0-99,999; 100,000-149,999; 150,000-199,999; 200,000+. The first grouping works well (and fast), but as soon as I try to get the second grouping, it never processes (I have to close out of Excel).

redmondb

No file, Scott-McRaeGroup!

Scott-McRaeGroup

ASKER

Sorry - I could not get it to load and have been away from my desk much of the last two days... Attached is the file. Thanks so much for your help!
UACDBCP001-01012013---test---201.CSV

redmondb

Thanks, Scott-McRaeGroup.

I'm sure that I.ve done it differently to you, but it didn't cause me any problem. I've attached the file (including Pivot) and the the Grouping dialogue.

What steps did you go through before Excel became unresponsive?

Edit: Force of habit, I changed some of the Pivot's settings before doing the grouping. However, I've just rebuilt the file from the csv without changing any Pivot settings and the result was the same as the attached.

Thanks,
Brian.UACDBCP001-01012013---test---201.xlsx

Scott-McRaeGroup

ASKER

I am manually highlighting the fields to include in the grouping, right clicking once they are highlighted and then clicking on "group." Each group is done that way. How did you get the above screen (where you Auto Group)? I'm not sure that will work because the user doesn't necessarily have consistent groupings (ie, they may want the first first grouping to be 100,000, then in increments of 50,000, etc), but I can't seem to find that automated grouping option.

Thanks again for all your help so far!

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott-McRaeGroup

ASKER

Awesome! That worked. Thanks so much! I'll get with the end user and see if he can make this work in his complete report.

Thanks again - everything you provided was EXTREMELY helpful! I knew there was probably another way to do this that wouldn't be so processor-intensive!

Joni

redmondb

Thanks, Joni. Glad to help!