Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Excel 2007 best practices when linking to an external workbook

Posted on 2011-09-15
Medium Priority
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
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
LVL 13

Accepted Solution

nike_golf earned 1200 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 1200 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."
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 13

Assisted Solution

nike_golf earned 1200 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 400 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 400 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

610 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