Solved

Performance issue with outline>subtotal function in Excel 2010

Posted on 2013-12-04
9
500 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
[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
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

690 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