Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Excel 2010 Pivot Table processor issue

Posted on 2013-01-30
11
Medium Priority
?
653 Views
Last Modified: 2013-09-25
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
Comment
Question by:Scott-McRaeGroup
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38835935
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
 

Author Comment

by:Scott-McRaeGroup
ID: 38837423
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38837439
No file, Scott-McRaeGroup!
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:Scott-McRaeGroup
ID: 38845215
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38845396
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
 

Author Comment

by:Scott-McRaeGroup
ID: 38855128
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
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38855682
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
 

Author Comment

by:Scott-McRaeGroup
ID: 38855768
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38855902
Thanks, Joni. Glad to help!
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question