?
Solved

Performance issue with outline>subtotal function in Excel 2010

Posted on 2013-12-04
9
Medium Priority
?
517 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

765 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