Excel 2007 best practices when linking to an external workbook

Posted on 2011-09-15
Last Modified: 2012-06-21
I have a workbook in Excel 2007 in which 8 sheets have up to 1800 formulas linking to an external workbook. Since each sheet has the same formulas in the same cells, I could easily set it up so that one sheet links to the external workbook and let the other 7 sheets link to that sheet.

Would that somehow be more of a "best practices" way to do it? Or does it matter either way?

Question by:gabrielPennyback
LVL 13

Accepted Solution

nike_golf earned 300 total points
ID: 36546896
This link for Optimizing Excel should help you understand the nuances of Excel 2007.


Author Comment

ID: 36547019
Thanks for posting, nike golf. Great link, which I will refer to a lot I think. Do you happen to know if it addresses my particular question?

LVL 13

Assisted Solution

nike_golf earned 300 total points
ID: 36547024
A quote from the link posted

"Links Between Workbooks

Avoid inter-workbook links wherever possible: they are slow, easily broken, and not always easy to find and fix.

Using fewer larger workbooks is usually (but not always) better than using many smaller workbooks. Some exceptions to this might be when you have a lot of front-end calculations that are so rarely recalculated that it makes sense to put them in a separate workbook, or when you do not have enough RAM.

Try to use simple direct cell references that work on closed workbooks. By doing this, you can avoid recalculating all your linked workbooks whenever you recalculate any workbook. Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.

If you cannot avoid using linked workbooks, try to have them all open rather than closed, and open the workbooks that are linked to before you open the workbooks that are linked from.
Links Between Worksheets

Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.

In Excel 97 and Excel 2000, worksheets and workbooks are calculated in alphabetical name sequence with individual calculation chains. With these versions, it is very important to name the worksheets in a sequence that matches the flow of calculations between worksheets."

"Large Number of Links to Other Workbooks

If possible, open the workbooks that you are linking to before you open the workbook that contains the links. Often it is faster to open a workbook than to read the links from a closed workbook."
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

LVL 13

Assisted Solution

nike_golf earned 300 total points
ID: 36547041
I've found a large number of external links just kills the performance in Excel 2007 much worse than Excel 2003 a big step backwards in my opinion. That said, I would avoid external links if at all possible and incorporate them into a single workbook.

LVL 17

Assisted Solution

andrewssd3 earned 100 total points
ID: 36547939
I agree with all the above, and would advise you to take care with external links - they are slower to update in 2007 and will cripple performance if you need to change the link source when you have more than a few hundred cells.  

Sometimes you must use extrnal links, and the most efficient way I have found is to use an array-entered reference.  Excel views this as just one reference even if it incorporates many cells, and it will be much faster to change the link source.  Obviously you can only do it with a contiguous block of cells:  select the whole block of cells in the target worksheet where you want the data to go, and type = to start the formula.  Then Switch to the source worksheet and highlight a the block of cells you wnat to link to - it must have the same dimensions as the target block.  Then press ctrl-shift-enter to array-enter the formula, and you're done.  The other advantage to this is that you can't inadvertently change one of the cells in the middle of the array.

Assisted Solution

jan24 earned 100 total points
ID: 36549460
An alternative to linking is to have the source workbook save data to a CSV or XML file, either on clicking a button, or automatically on save.  The destination workbook can then contain a macro that reads that CSV or XML and puts the values into a predetermined range.  That can improve performance and give you more control over the link, though it does obviously involve writing some macros.

Author Closing Comment

ID: 36552692
Thanks, all of you. I posted a followup question here about how to use XML. Please take a look.

Featured Post

Highfive Gives IT Their Time Back

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!

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

707 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

12 Experts available now in Live!

Get 1:1 Help Now