Expanding expense table: calculated column, PivotTable refresh, filtering
A workbook tracks 2,400 project expenses in a table named ExpenseTbl on a sheet named Data. Columns are ProjectID, Phase, Vendor, InvoiceDate, Hours, Rate, and a calculated column Cost with =[@Hours]*[@Rate]. The table has a Totals row and a slicer for Phase. Each month a colleague appends 150–300 rows by pasting from a PDF extract, sometimes pasting above the header or including a duplicate header row. The Cost formula fills only some new rows. A second sheet uses SUMIFS with structured references, and a PivotTable is built on the table. Your manager wants the table to keep expanding automatically, the calculated column to be consistent, and the PivotTable to include new rows after refresh. She also wants to filter out Phase = 'Closed' without affecting totals for other phases.
1. Why is the Cost calculated column inconsistent after the monthly paste, and how do you fix it without breaking the table?
Read the worked answer
Calculated columns fill automatically only when the table recognizes the new rows and the formula is consistent. Pasting above the header can push the header down or create a second header row, so the table no longer sees the pasted rows as data. Fix the structure first: undo the paste, remove any duplicate header row, and paste below the last data row inside the table so it autoexpands. Then check File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type and enable 'Automatically fill formulas in tables.' Re-enter the formula once in the Cost column: =[@Hours]*[@Rate]. If some cells already contain a different formula or a hard-coded value, select the whole column and use Table Design > Fill or copy the formula down. Do not convert the table to a range; that would break the structured references and the automatic expansion the SUMIFS sheet and PivotTable rely on.
2. How do you ensure the PivotTable includes the newly appended rows after refresh?
Read the worked answer
Because the PivotTable's source is the table ExpenseTbl, it will pick up appended rows after Refresh, provided the rows were added inside the table and the table expanded. Set the PivotTable to refresh on open if the workbook is shared: PivotTable Analyze > Options > Data > Refresh data when opening the file. If the PivotTable was actually created from a fixed range, change the source to the table name. For the monthly process, add a step to Refresh All (Ctrl+Alt+F5) and check the row count against the source. If the new rows lack Phase values, they will fall into a blank category; clean them before refresh. A PivotTable does not automatically include new columns, only new rows, so tell the colleague to append rows, not insert columns.
3. How should you filter out Phase = 'Closed' while preserving totals for the other phases?
Read the worked answer
Use the table's filter or the Phase slicer to hide Closed rows; do not delete them, because the history is needed and deleting would break the PivotTable and any audit. The Totals row uses SUBTOTAL, which ignores rows hidden by a filter, so the visible total will exclude Closed rows automatically. SUMIFS formulas on the second sheet are independent of the filter and will still include Closed unless you add a criterion such as Phase<>'Closed'; decide which number the manager needs and label it clearly. If the requirement is 'totals for open phases only while still showing all rows,' use SUMIFS with the criterion, not a filter. If the requirement is a printable view, filter and print the visible rows. Clear the filter afterward so the next user does not mistake a filtered table for missing data.