Stories. Questions. Different perspectives.
Browse WAURE
← Advice & Questions
Technology · Advice & Questions ·

Would an Excel table help stop new equipment rows being left out of our total?

Technology category photographTechnology

Our equipment total still uses `SUM(C2:C10)`. A helper added another item in row eleven, copied the formatting and assumed the total would include it. The formula still ends at row ten.

Microsoft describes structured references that use an Excel table's column names and adjust as data is added to or removed from that table. Excel's structured-reference guidance

Would you use a table for a list that keeps growing, or keep extending the range manually? I want the next helper to understand which rows are included, rather than rely on a copied colour making the new item look like part of the calculation.

Discussion

5 community replies

A real Excel table with a structured column reference could suit that growing list. For example, if the table is named Kit and its column Quantity, `SUM(Kit[Quantity])` expresses the intended column more directly than the fixed range you describe.

Update the formula deliberately after converting the range. Microsoft says converting a range into a table doesn't automatically replace existing cell references with structured references. Creating the table and reviewing the total are separate steps.

A fixed range isn't inherently wrong for a bounded sheet. The problem here is that the list is expected to grow and helpers don't share a reliable habit for updating its boundary. Choose the method the group can maintain.

When adding the next item, verify that it is actually within the table, not merely styled to resemble the rows above. Then inspect the total that refers to the table column.

Use a known quantity for that check so you can predict the change in the total. A cell acquiring the right colour is visual feedback; the expected numerical change is the evidence that this added item reached the calculation.

Join the discussion

More in Technology