Eighteen Rows In, Twelve Rows Out
Purchasing maintains tblOrders on sheet Orders with columns OrderID, Date, Region, Rep, Cost, Amount, and a calculated column Margin whose formula is =[@Amount]-[@Cost]. The table has a Totals row. A new analyst pastes 30 rows of CSV data starting at A2; the block contains one fully blank row, and Excel appends only the first 18 rows to the table - the remaining rows land below the Totals row and are ignored by =SUMIFS(tblOrders[Margin],tblOrders[Region],"West"), which now understates West margin. Elsewhere in the table one Margin cell holds a typed 0 instead of a formula, and a block of rows carries yellow fill that hides the style banding. A manager reviews only the Grand Total.
1. Explain why 18 of the 30 pasted rows joined the table, and give the repair sequence plus a check that the SUMIFS is now complete.
Read the worked answer
Why. Table auto-expansion is mechanical, not intelligent:
- The paste must be contiguous with the table body, in the same columns.
- The Totals row is a hard boundary - anything pasted below it is outside the table.
- A fully blank row inside the pasted block terminates the expansion, so rows after the blank land outside (and if they fell below the Totals row, they are simply ordinary cells).
Repair.
- Delete the 12 stray rows sitting below the Totals row (delete the rows, don't just clear them).
- Remove the blank row from the source block before pasting again, or paste in two contiguous chunks.
- Paste starting at the cell immediately below the last data row (above the Totals row).
- Confirm the table's extent: click inside it and read the range on the Table Design tab, or use Table Design > Resize Table if needed. Every data row must be inside that range.
Check. =COUNTA(tblOrders[OrderID]) should equal 18 + 12 + the original row count, and the Totals row Grand Total should reconcile to a manual sum of Amount minus Cost. If SUMIFS still disagrees, the mismatch is in the table boundary, not the criteria.
2. One Margin cell contains a typed 0 rather than the formula. Why does this matter beyond cosmetics, and how do you restore consistency without hunting row by row?
Read the worked answer
Why it matters. SUMIFS and the Totals row sum values, not intentions. A hand-typed 0 is a legitimate-looking number, so the West total and the Grand Total are both wrong by whatever the real margin should have been. Excel flags it with a small green triangle and the message "Inconsistent calculated column formula", and new rows added to the table will still inherit the correct formula - so the defect never spreads, it just hides.
Repair.
- Select the offending cell and read the tooltip; the flag confirms it is inconsistent.
- Retype
=[@Amount]-[@Cost]and press Enter. The flag clears. - If several cells are affected, type the formula in one cell, then Table Design (or the warning options) > Overwrite all cells in this column with this formula.
- Cross-check: Totals row Margin should equal Totals row Amount minus Totals row Cost. If it does not, another cell in the column is overridden.
Prevention. Set the column's number format and formula before distributing the file, and tell users to add rows rather than type over the column. The inconsistent-formula check is only visible if Error Checking is left on.
3. Yellow fill on several rows now covers the banding, and the analyst asks whether to just convert the table to a range and re-apply the style. What is the correct answer?
Read the worked answer
Why the yellow wins. Direct cell formatting always outranks table style formatting. Banded rows are a style applied by the table; a manual fill is a format on the cell, so it sits on top. Toggling Banded Rows on the Table Design tab will not remove it.
Correct fix.
- Select the affected rows and use Home > Clear > Clear Formats (or the Fill Color > No Fill). The banding reappears underneath.
- If those rows were highlighted for a reason, do it with conditional formatting instead, so it coexists with the banding and updates when the data changes.
Do not convert to a range. Convert to Range keeps the current formatting but destroys the structure: tblOrders[Margin] and tblOrders[Region] become #NAME? or are rewritten as fixed A1 references, [@Amount]-[@Cost] loses its meaning, the Totals row becomes a frozen figure, auto-expansion stops, and every future pasted row must be added to ranges by hand. Recommend clearing the fill instead.