A finance PivotTable refreshed, but the new rows are missing

Refreshing a PivotTable does not establish that its source includes every transaction on the worksheet. A fixed range can end above the newest records, while the refresh successfully recalculates the smaller population. Start with one missing transaction that should be included under the report's stated purpose. Record its identifier, date, amount and location in the source export.

Confirm the expected inclusion before changing the workbook. An invoice outside the reporting period or an entry belonging to another entity may be correctly absent. Have the report owner explain the intended period, posting status and population. The investigation should establish whether an eligible record is missing, rather than expand the report until its total resembles a separate number.

Preserve the issued workbook and use a controlled copy for diagnosis. Record whether the workbook is reading worksheet cells, an Excel table, a query result or the Data Model. Those sources have different maintenance paths. A repair suitable for a simple worksheet range should not be applied blindly to a model with relationships or an external connection.

For a worksheet-based PivotTable, inspect the configured source through Excel's supported source-data controls. Microsoft documents changing the source to an Excel table or cell range and describes limitations for Data Model-based pivots. Read the actual range or table name. A familiar worksheet name alone tells you little about which rows are included.

Suppose a fictional receivables export originally contained headers in row 1 and 100 invoices in rows 2 through 101. The pivot points to A1:F101. A later export contains another twenty invoices in rows 102 through 121. Refreshing the same fixed source can still summarize only the first hundred invoices. Check the last included record and the first excluded record to establish that boundary.

If the source is an Excel table, inspect the table's extent as well as its name. Data pasted below a blank separating row may be outside the intended object. Microsoft explains that rows added to the table are included when the pivot refreshes. The important condition is that the records belong to the table, not merely that they appear somewhere below it.

Check report filters, slicers and selected field items after inspecting the source. A transaction can exist in the pivot's underlying population while its category is excluded from the current presentation. Record those exclusions instead of clearing every filter and declaring the resulting larger total correct. Some filters express a required business boundary.

Trace the selected missing transaction through its grouping fields. A new department name, blank category or changed date can place it somewhere unexpected. Inspect the source value, then the corresponding pivot item. This avoids changing a source range when the real issue is a mapping or an explicitly restricted view.

Also inspect any transformation between the export and the pivot source. A query can remove rows before loading an otherwise complete table. In that case, widening the pivot range will not retrieve the removed records. Compare the export, loaded source and pivot at separate checkpoints so the repair addresses the first point where the eligible transaction disappears.

Use a count and monetary control from the eligible source population. In the illustration, assume the original hundred invoices total $80,000 and the twenty added invoices total $12,500. A complete unfiltered pivot for that population should account for 120 invoices and $92,500. The $12,500 difference is explained by identified records, rather than a plug entered into the summary.

Choose a count appropriate to the source grain. A file with several lines per invoice needs a distinct invoice check or a separate line count; counting every row as an invoice would introduce another error. Retain the grain definition with the reconciliation. Compare amounts in the same currency and on the same gross, net or outstanding-balance basis.

Inspect several added records after the source change, including the final row and a record in a new category. Then check the existing records still appear once. Selecting a larger area can accidentally include a pasted subtotal, duplicate extract or note below the data. An increased total is useful only when the included population is correct.

An appropriately maintained Excel table can make recurring additions easier to handle than a fixed address. Define how the replacement export enters that table, how old rows are removed when necessary and when the pivot is refreshed. Avoid a process that appends a complete new export to last month's complete export without accounting for the repeated records.

Give the preparer an explicit completion check. It can compare the table's row count and final source identifier with the approved export, followed by the pivot reconciliation. Do not rely on a particular row number forever; transaction volume changes. The evidence should describe the population, not merely confirm that somebody clicked a refresh command.

Check other summaries that use the same source. Some may share a source object, while others point to a separate range copied months earlier. Identify the affected reports and update only the relevant configuration. A working pivot on one worksheet does not establish that all charts and summaries in the package read the same complete data.

Have the report owner review the corrected output and determine whether an issued report needs replacement. Explain which records were omitted, the effect on the reported measures and the corrected version. Preserve the original file so the company can answer a later question about the figures previously distributed.

Record the old source boundary, repaired configuration and checks performed without including unnecessary transaction detail in a general issue log. Update the recurring preparation instructions at the point where new records enter the workbook. The finished repair should make the next period's source population observable, so a successful refresh can be evaluated against the records it was meant to summarize.