Solved

Performance issue with outline>subtotal function in Excel 2010

Posted on 2013-12-04
9
436 Views
Last Modified: 2013-12-17
I have an employee in my office who uses the data functions in excel to prepare reports for upper management.  Specifically, she sorts data using the Data>Outline>Subtotal function in excel.  For some reason, when she initiates the process, the application processes the data ALARMINGLY slow.  The problem is unique to her machine, as another employee tried it with the same file, and it zipped right through.  Also of interest, when I launched her Excel in safe-mode, the processing also occurs much quicker on her machine.  I have checked for add-ins, and it says that none are active.  Anyone have any ideas what could be causing the slow down besides add-ins?
0
Comment
Question by:Trenton Knew
  • 3
  • 3
  • 2
9 Comments
 
LVL 9

Author Comment

by:Trenton Knew
ID: 39699598
Increased points to 500
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39706327
I don't have any good ideas but I would look at the calculation settings in File | Options | Formulas

I would also check to see if there is a macro workbook being loaded.  Perhaps there is a macro that duplicates a function name being used in the worksheet being processed.

I would also look at whether the workbook has links to other workbooks, especially those on a network.

Good Luck
0
 
LVL 10

Expert Comment

by:broro183
ID: 39710927
I think Jim's suggestions are good ideas, I'll try from another angle:
- What view is being used eg is it displayed using "pagebreak preview"?
- Is the zoom level at 100%?
- This is unlikely but I'll ask anyway. Are all machines using the same version of printer driver?
Can they be updated?
- Do they have the same default printer?
- Are all machines on the same version of Office & do they have the same Service Packs applied?

Rob
0
 
LVL 9

Author Comment

by:Trenton Knew
ID: 39712755
Follow up, We found that removing conditional formatting rules from the spreadsheet SIGNIFICANTLY boosted performance with inserting subtotals.  Anyone know a way to make the spreadsheet not apply formats until the operation completes?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 15

Assisted Solution

by:JimFive
JimFive earned 250 total points
ID: 39712943
You could try setting Calculate to Manual, but I'm not sure if that applies to conditional formatting.
0
 
LVL 10

Accepted Solution

by:
broro183 earned 250 total points
ID: 39713499
hi,

I don't think the calculation mode will stop CF from calculating either.

I did some Google searching & found some links that suggest using a "switch" as the first condition.

This post of Chip Pearson's gives a brief example:

Select the cells whose conditional formatting (CF) you wish to enable or disable. Then, decide on a cell whose value is to control this. Suppose that cell is B1. Open the CF dialog, add a new rule, select the "Formula Is" option and enter =NOT($B$1) as the condition. Move this rule to the top of the list of CF conditions. Check the "Stop If True" check box for this item.  Don't add any formatting. Now, if you enter TRUE in B1, conditional formatting will be applied as normal. If you enter FALSE in B1, no formatting is applied.
 
Note that this works only in Excel 2007 and later. It does not apply to Excel 2003 and earlier version.
Source of quote

Debra's article & comments give some similar examples.

hth
Rob
0
 
LVL 9

Author Closing Comment

by:Trenton Knew
ID: 39725669
I appreciate you two helping with this.  The person I was trying to help was a little too on the impatient side for me to try the formats on/off tbing.  she was able to get her work done, so, I'm splitting the points for effort.  Thanks again.
0
 
LVL 10

Expert Comment

by:broro183
ID: 39725845
Thank you for the points :-)

If you do get a chance to try any of our suggestions in the future please let us know if they help

Rob
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

706 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

15 Experts available now in Live!

Get 1:1 Help Now