[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Performance issue with outline>subtotal function in Excel 2010

Posted on 2013-12-04
9
Medium Priority
?
531 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

656 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