Business Analytics Made Easy

Struggling with mountains of data in your spreadsheets?

Spreadsheets larger than about 60 MB (xlsx; about 200 MB for xls) tend to have sluggish interactive response. Size is not the only factor – for example, the Excel database functions (dsum and friends) tend to make a model slower than its size would otherwise suggest. Slow response is particularly troublesome during data input.

The simplest way to improve interactive response of large spreadsheet models during data input is to turn off automatic computation during data entry, then manually re-compute when you need the full spreadsheet model updated (using the F9 key). This makes sense: generally, you don’t need an updated version of your model every time you enter one new number. You can turn off automatic recalculation in Excel 2010 by clicking on tab Formulas in the ribbon (at the top of the Excel window), then on Calculation options.

Using manual computations does not work so well if you want to update some content in the data input tables during data entry. We have found that a useful solution is to locate the data entry tables on separate worksheet(s), and to place the input worksheets in a separate workbook. You can open the data entry workbook by itself for data input, and enter data with very fast response times. The input workbook updates external links to named regions in the main workbook using cached values that it remembers from the last time it was able to communicate with the model in the main workbook.

If you are starting with one workbook, you can separate the input tables into a separate workbook.

• First, design the workbook so that the data input tables are on worksheets that contain virtually no other computations that rely on the main model.
• Move the input worksheets to a new workbook and save both workbooks in the same directory.

Excel will automatically tell each workbook how to communicate with the other one. If you need to update the links that enable the workbooks to find each other, you can do this using the Edit Links… dialog found under Data in the ribbon (the Data tab at the top of the Excel window in Excel 2010).

Excel has two kinds of links: local links in the same workbook, and external links that can link to other workbooks.

• Local links are defined by references to cells or named regions in the same workbook. External links are defined by references that include the full path name (such as ‘c:/directory/filename.xlsx[sheet1]‘!region_name).
• External links can be easily updated to point to a new file location using the “Edit Links…” dialog .
• If your workbooks have local links, then manually convert them to external links by editing the references to the external addresses in each formula (with search-and-replace and copy operations).

If the input workbook and the main workbook are allowed to communicate and then are closed, then each workbook caches values for external named regions. When you open the input workbook by itself, you get fast response during data input (typically sub-second response), and the workbook can update its external links with cached values.

If you start out with two separate workbooks, you can get them to read named regions from each other using the same Edit Links… dialog.

You don’t have to choose between fast response time during data input and updating information on data input worksheets.