Excel 301 Question

A popular solution is to close and reopen the workbook, perhaps even after restarting your machine if reopening the workbook didn't do the job.

Thank you for your input. The find/replace is the answer for the short term and is saving us a lot of grief.

Great, Sara. But I'm still concerned that you had this problem in the first place!

Another thought with regard to your very large file: I always try to put data into one workbook and the reports/analyses in other workbooks. That allows for any number of reports & analyses to reference the same data. It's something you might want to think about.

Warning 1: Never, NEVER reference data in an external workbook by cell address. This is because if you insert a row or column in the data workbook while the report workbooks are closed, the reports will then reference incorrect ranges. Instead, reference all external workbooks by range name.

Warning 2: Only reference *data workbooks* externally. If you reference other reports, you could create some really nasty problems with data integrity.

To illustrate, I once consulted at the headquarters of a company you've heard of, where I found a legally important workbook that violated this rule. It linked to other workbooks that linked to other workbooks, and so on. I found more than 50 inter-linked workbooks, including circular calculations that were eight workbooks deep. I also found links to workbooks on the computers of people who hadn't worked in the company for more than two years.

But if you link to data workbooks correctly, you can keep your workbooks small because you don't have to cram every report or analysis into the one workbook that contains your data.

Charley

Charley, What do you mean by "referencing 'data workbooks' externally"?

You might want to upgrade the amount of RAM your computer has. If you have several windows open and other programs running while you work on the spreadsheet, you could run into issues. I, too work with large spreadsheets. I recently upgraded my system to 16GB and don't have any issues.

Joe,

An "external reference" is where in workbook D you reference an area in workbook A. I **only** do this when workbook A contains a table of data that I can use in workbook D. (That is, I *never* link to another report.) With this approach, I also could reference the data in workbook A from workbooks E, F, G, and so on.

I also could maintain other kinds of data in workbooks B and C, which I also could reference in workbooks D, E, F, G, and so on.

The primary ways to return data from the data workbooks are...

1. Using PivotTables.
2. Using worksheet functions like SUMIFS, SUMPRODUCT, and INDEX-MATCH.
3. Using array formulas.

This is a very powerful design, because it allows you to have any number of workbooks for reporting and analysis that contain **no** data. Instead, they have links to data in the data workbooks. With this approach, you update a report merely by changing its report date in one cell...assuming the new period's data has been added to the data workbook(s).

If you want to deliver an interactive workbook to someone, you would have two choices. First, if your data workbooks are available on the network, you could just deliver a report workbook and let it grab the data it needs.

Second, if the report won't have access to your data workbook(s), you could combine the data and the report and then save it under a new name for distribution. One of the problems with this approach, however, is that you lose control of your data. (That is, over time, you wind up with many versions of the truth.)

Does that make sense?

Charley

If you have a large amount of formulas in your file where the values stay largely unchanged you could try converting the cells to values. I did this in a planning and reporting excel spreadsheet and it cut the size of the file dramatically, increased the speed of the file, and fixed a lot of the problems associated with an excel file that was too much for my computer hardware. If you periodically need to update the values, save a row of formulas, cut and paste in to update all of the rows, then convert back to values.

This goes without saying, but just in case: be sure to protect all of the formulae in your spreadsheet and password protect the sheet itself. I've had clients who have wanted to use "hard" number entries for various purposes (to be expeditious, or to do instantaneous "what ifs" etc.) so they unlocked cell protections. Invariably, they call a week or two later when the "real" report is needed and their workbook isn't working. Always keep blank backup copies when you have finished (or commissioned) a complex workbook!