Excel pivot table P&L

Structurally, a management P&L or KPI view is a set of line items (revenue, COGS, opex categories, or KPIs) aggregated by period. That's a pivot, with Rows = line item, Columns = month or quarter, Values = Sum of amount. Build it once over the transaction/GL extract and it refreshes forever. There's no grid of SUMIFS to maintain, and adding a new month is automatic once the source is a Table.

How do you build a P&L in a pivot table?

To assemble one, point a pivot at a GL-style extract (Date, Account, Category, Amount). Drag Category (Revenue, COGS, Marketing, Payroll, …) into Rows, group Date into Quarters and put it in Columns, and put Amount in Values (Sum). You now have every category by quarter. Two more moves turn it from a plain table into analysis:

• Common-size it. Add Amount to Values a second time and set Show Values As ▸ % of Column Total. Each period's column now reads every line as a percentage of that period's revenue. That's the common-size P&L an analyst uses to spot a creeping cost ratio that absolute rupees would hide.
• Make it drillable. Add a slicer on Region or Product (Report-connected if you have several pivots), plus a PivotChart (Insert ▸ PivotChart, Alt, N, SZ), so the story is visual and re-draws as the slicer moves.

What are the limits of calculated fields?

Calculated fields come with one limit you need to know. For a ratio line like gross-margin %, you may reach for PivotTable Analyze ▸ Fields, Items & Sets ▸ Calculated Field. Learn its rule before you trust it. A calculated field operates on the aggregated totals. It doesn't work row by row. Margin% = Profit / Revenue is fine, because it divides the summed profit by the summed revenue, which is the correct aggregate margin. But Revenue = Price × Qty as a calculated field computes SUM(Price) × SUM(Qty). That isn't Σ(Price × Qty), and it'll be badly wrong.

The safe habit is to use calculated fields only for ratios of already-additive measures. Compute any row-level product, like extended price, in the source data, then sum that in the pivot. When in doubt, remember that a pivot excels at adding up additive facts, and push multiplication upstream.

A pivot P&L isn't a formatted statutory statement, and out of the box it won't indent and subtotal like a finance template. As a live, sliceable, common-size actuals view, though, it's unbeatable. The monthly reporting you'll do on the analyst track rests on it.