Solved

MS Excel 2010 Pivot Table processor issue

Posted on 2013-01-30
11
492 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now