Build a PivotTable and reconcile the result

Understand the method

A PivotTable is a native Excel summary that groups records and aggregates fields. An Excel Table is the rectangular source with headers and filters. They are different features. In this lesson you will create the PivotTable yourself in your installed Excel edition. Neither downloaded workbook contains a prebuilt native PivotTable. The solution provides a formula summary for independently checking your result.

Start from a verified source

Complete the Prepared formulas first and confirm the Orders table includes A5:H17. Each row is one verified order. Missing identifiers, duplicate order rows or inconsistent value types would undermine the summary even if every PivotTable setting were correct. Use the verified data, not the original raw extract with the duplicated M004.

Create the summary

1. Select a cell in Orders and choose Insert, PivotTable. Use the full Orders table as the source and place the result on a new sheet. Rename the sheet My pivot.
2. Put month_start in Rows. Keep the actual month and year. If Excel automatically groups the field, inspect the grouping rather than allowing February from multiple years to combine.
3. Put channel in Columns.
4. Put order_value_eur in Values and confirm its aggregation is Sum.
5. Put status in Filters and select completed. Record that choice beside the result.
6. Format the values as EUR and compare every month/channel cell with the formula summary.

Menu and field-list layouts vary between Excel editions. If you cannot create a PivotTable in your current app, complete the formula summary and read the worked configuration; the native practice task remains unfinished until you can create and inspect it in Excel.

Inspect Sum versus Count

When a value field contains text, Excel may choose Count rather than Sum. Read the field caption and settings rather than assuming the chart or table is showing money. Add order_id to Values as Count if you want completed-order counts. Counting IDs represents order count here only because every prepared ID occurs once. You do not need a distinct-count feature for this verified table.

For AOV, divide summed completed sales by counted completed orders using matching populations. Averaging order_value_eur also gives AOV here when the data is numeric and the completed filter is applied, but the explicit numerator and denominator are easier to audit and safer when the data grain changes.

Build an independent reconciliation

In Analysis B14 enter:

=SUMIFS('Prepared'!$E$6:$E$17,'Prepared'!$D$6:$D$17,"completed",'Prepared'!$F$6:$F$17,B$13,'Prepared'!$C$6:$C$17,$A14)

Fill down to B15 and across to C15. B$13 moves across periods while $A14 moves down channels. B16 is =SUM(B14:B15) and B17 is =B16-B6; copy these to column C. The two differences should be zero. This diagnostic must not feed the underlying business calculations. Agreement between two summaries does not independently prove that the source file is complete, so keep the quality checks too.

Refresh deliberately

After changing source data, refresh the native PivotTable using the refresh command in your edition. A formula summary and a PivotTable can otherwise show different moments of the source. For new rows, verify the source table expands, the PivotTable source includes them, and any fixed sample formulas are extended. This module does not assume automatic PivotTable refresh or an external connection.