Excel: Tables Calculate Slowly With Dependent Cells

SUMMARY

For the quickest recalculation of tables in Excel, make sure that the row and column inputs of the table are not used in any formulas not associated with the table formula.

When Excel calculates a table, it essentially places the information from the row or column into the indicated cell, then calculates the table formula to insert that value into the table cell. Excel then inserts the next row or column input and repeats the cycle. For each input from the data table, Excel must recalculate all cells that are dependent on the input cell, either directly or indirectly. Excel uses this method because the table formula may refer indirectly to the input cell; thus Excel must make sure it correctly calculates all related cells.

This enables Excel's table feature to be very powerful, and allows you to do such things as combine tables with databases to generate concise, intuitive reports. However, this feature also slows down recalculation if you have several formulas referring to the input cell. In the case of databases, if you have a database function such as DCOUNT or DSUM that uses the same criteria as your database, that DCOUNT or DSUM statement must be recalculated for each entry of the table.

Therefore, for optimum calculation, use separate criteria for database functions in the table and other database functions on the worksheet. The database functions accept any defined name for either the database or the criteria. Thus, you can set up two criteria, naming one, for example, "tcriteria" (for table criteria), and the other simply "criteria". You then can refer to table criteria in a database function [such as DCOUNT(Database,,tcriteria)]. If you then use the second criteria for any other database functions, they will not have to recalculate for each entry in the table.