Excel Tables 11 – Auto Update Dependent Formulas and References

MOST IMPORTANT BENEFIT of Excel Tables. Spend 10 minutes. Read it.


The risk

Formulas refer to data. Similarly Validation lists, Pivot Tables, Named Ranges also refer to data ranges. If a reference is for ONE cell, the formula is updated when the value changes.

But if the reference is to a range of more than one values, the problem starts. When more data is added, the formula becomes outdated. If you forget to update the formula, the result shown is wrong. It is not mathematically wrong – it is operationally wrong.

Due to this lot of wrong decision can be taken (and ARE taken) leading to monetary and other types of business losses.

To avoid this we need tables.

Let us see this in action. I have a range of values and a formula next to it. I have entered the same formula in two cells so that we can see the result and the formula.


Now if I add one more item, the formula will NOT change by itself. Some human being has to remember to update it. Hopefully someone will! IF not – this is what happens.


The range has not changed. But the data has increased. The output is still 14. If nobody notices the problem, everyone will assume that 14 is the correct result and proceed to use it for further decision making. All of these decisions will obviously be wrong.

That green mark is a warning

That is why Excel shows a green mark – that is a warning. Nobody knows this. Please start noticing it and correcting it. I have written a series of articles on this.  Please refer to these articles:
Auditors, Risk Managers, Everyone: Did you know? These green marks are WARNINGS!!
Green Marks Part 2: Formulas showing wrong results!

Obviously, we have to find and correct all such green marks. But that can be done ONCE.

Even if the formula is updated, next time more data is added, it will require manual update of all formulas. That is what we want to avoid. That is why Tables were created.

Pre-requisites for using Excel Tables

The Excel workbook must not contain any pending errors (green marks). All must have been resolved already. At least the Formula Omits Adjacent Cells type of errors have to be handled.

Create a table and then add formulas

Let us take the same example and see the difference. This is the same formula. Only difference is that there is a table added.


After adding another value, TABLE automatically updated the formulas.
Simple and Effective!


Reduced Effort and Increased Accuracy

This update is not just applicable to formulas which depend on the data range. It also works with PIVOT TABLES, Charts, Validation Lists, Named Ranges and all kinds of dependents.

It is extremely powerful and useful. Start using it.

Benefit for programmers

While writing VBA code, we often need to find out the data range. This is typically done by going one row (or column) at a time in a loop looking for an empty cell – which usually (not always) indicates that the data range is finished. This is guesswork and is extremely error prone. Named ranges also are error prone.

The answer is use Excel Tables even while accessing data programmatically. The object is called ListObject.

Listobject.range will give the data range without iterating in a loop. Of course all other capabilities of Tables are also available programmatically.

Relook at your existing code and check if you can  simplify things (and increase accuracy) by incorporating ListObject functionality.

This object is available since 2003 version.

What Next

We will see some additional benefits of Excel Tables and end this series in the next article.

Related Articles

This article is a part of the series: Knowledge Pack – Excel Tables


2 Responses

Queries | Comments | Suggestions | Wish list