Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

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?
0
Scott-McRaeGroup
Asked:
Scott-McRaeGroup
  • 5
  • 4
1 Solution
 
redmondbCommented:
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.
0
 
Scott-McRaeGroupAuthor Commented:
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).
0
 
redmondbCommented:
No file, Scott-McRaeGroup!
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott-McRaeGroupAuthor Commented:
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
0
 
redmondbCommented:
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.
0
 
Scott-McRaeGroupAuthor Commented:
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!
0
 
redmondbCommented:
Scott-McRaeGroup,

OK, that pushed one processor to 100% and locked out Excel for me as well. From a quick look around, I haven't come across this elsewhere, so I think we're into workaround mode. I see a couple of options...
(A) Add a column to the data with a formula which sets the Group.
...or...
(B) Your best option appear to be to use "Auto Group"...
 - Select the first entry in the "Mileage" column.
 - On the Ribbon's menu bar, select "Options".
 - On the "Options" tab, select "Group Selection".
 - A dialogue similar to the one I've shown above appears. Make the changed I've shown.

they may want the first first grouping to be 100,000, then in increments of 50,000
...which you can do by a simple change to "Ending at:" in my example - either specify an amount >= the largest value or just tick the box beside it. With "Auto" the first and last groups can be any size, but all of the groups between them must be specified.

Regards,
Brian.
0
 
Scott-McRaeGroupAuthor Commented:
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
0
 
redmondbCommented:
Thanks, Joni. Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now