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
Solved

MS Excel 2010 Pivot Table processor issue

Posted on 2013-01-30
11
579 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
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

856 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