Hello,

Since this is the first time I'm in this forum let me introduce myself, my name is Srinath and I'm working with an MNC.

I'm new to 'Shared Workbook' concept in excel file and while using the same have come across some performance issue.

I'm using an excel file which contains VB Macros in it and while opening it gives me a message whether to update the links or not, the size of this excel file is 8.5MB

Now, I goto-->Tools-->Shared Workbook, select the checkbox 'Allow changes by more than one user...' option and click on OK, it converts the file into a shared file, I save the same by clicking on 'Save' and close it.

Now when I re-open it, since there are macro's as said before it asks whether the links need to be updated or not, I select 'Don't update' then the excel file hangs (it is still running at the background and Task manager shows 55 - 90% CPU usage) and only after about 5 min I'm able to use this file. The same issue is seen even when I click on update, it shows 'Calculating Cells = 100%' and then only after about 5 min I can work with this file.

If I removed the 'Shared Workbook' and perform the same steps within couple of seconds I'm able to work with the same excel file.

Could you pl. let me know why this is seen.

Thank You in Advance.

Regards,

Srinath.

Hello Ashish,

Thanks for looking into this.

I think the excel which I'm using does not contain data tables.

Excel contains Data (Numeric values, descriptions, ID's), Pivot tables, Charts....In total it has 7 worksheets and one of the worksheet has values in 1408 Rows & 100 Columns, these rows & columns have links to other excel files and while opening the excel file if I click on Update then 25% of these columns & rows get updated with the values from the linked excel fiel, else it will not.

Based on the values entered in this worksheet, Pivot tables, Charts & other analysis worksheets (they have excel formulas like COUNTIF) get updated.

Hope this will help you.

Thanks & Regards,

Srinath.

Hi Srinath,

Let us categorize the problem and then discuss:
You have a file of 8.5MB with 7 worksheets, of which
a) 1 worksheet contains links to other workbooks ( 1408 x 25 cells, to be specific ) [1408 rows x 25% of 100 columns]
b) Other 6 worksheets have Pivot Tables, Charts, and Excel formulas (like countif)

Your problem is
1. When you open it in "Shared" mode
a) and choose "Update Links", or
b) choose "Don't Update Links"
It takes 5 minutes or more to open

But
2. When you open it in "Non-shared" mode, irrespective of choosing "Update" or "Don't Update", it opens within a couple of seconds.

Now, the time taking factor may become crucial in the following cases:

1. Number of links in the file, in this case 1408 x 25 cells or 35200 cells
2. Location of "Sources of Links", i.e., if they are accessed through network, then time taken depends on the speed of network
3. If the sources contain formula and/or more links, then they are re-calculated on opening this file
4. If the sources are open by other users, update will take time until those files are saved/autosaved

Again, note that whether shared or not, re-calculation is not done only for "Pivot table", which needs manual update on change of source data. But, all formula and links are recalculated depending on the above constraints. Farther, if your file contains array formula, time taken shall be more. And remember, even if you choose "Don't Update", Excel goes through the whole procedure of "Update" case-by-case, only making a "no-no-no..." combination instead of "yes-yes-yes..." ( I guess I am able to convey the message in my poor English! ).

But, why the same recalculation takes lesser time if opened in "Non-shared" mode? Frankly, I am also wondering on that. Let us do some checking, please choose the "Manual Recalculate" option (described below) and try to open the file as you did with 2 cases along with 2 sub-cases ( Shared-Update to NonShared-Don'tUpdate ) and see if it improves the performance of time.

How to set "Manual Recalculate"?
1. Tools > Options > Calculation
2. Manual (check the radio button)
3. You can also change the parameters under "Workbook options" available there and see the effect on timings one by one.

Last but not the least, please check the available free disk space and Disk Fragmentation status as well as size of cache memory.

Please inform if this worked.