A rolling P&L dashboard gives you an always-current view of your last 12 months — no manual date adjustments, no copy-paste errors. Here’s how to build one from scratch.
What You’ll Need
- Excel 2016 or later (Microsoft 365 recommended)
- A source data table with columns:
Date,Category,Amount - About 90 minutes of focused time
Step 1 — Structure Your Source Data as a Table
Convert your raw data to an Excel Table (Ctrl + T). Name it tblData. This ensures formulas update automatically as you add new rows.
Your table should look like this:
| Date | Category | Amount |
|---|---|---|
| 2024-01-31 | Revenue | 45000 |
| 2024-01-31 | Salaries | -18000 |
Step 2 — Calculate the Rolling Window
Use EDATE to define the 12-month window dynamically. In a helper cell:
=EDATE(TODAY(), -12)
This gives you the start of your rolling window. Name this cell RollingStart.
Step 3 — SUMIFS for Each Category
For each P&L line, use SUMIFS to pull only figures within the rolling window:
=SUMIFS(tblData[Amount], tblData[Category], "Revenue", tblData[Date], ">="&RollingStart, tblData[Date], "<="&TODAY())
Step 4 — Build the Dashboard Layout
Use a clean grid layout:
- Column A: Category labels (Poppins Bold if you’re matching brand style)
- Column B: Rolling 12-month total
- Column C: Sparkline showing monthly trend
Step 5 — Add Conditional Formatting
Highlight negative values in red, positive in teal. Use custom number formats to show (£18,000) for negatives rather than -£18,000 — this is standard financial presentation.
The Result
A dashboard that updates every time you open the file, always showing your most recent 12 months. No maintenance required.
Have questions or want a template file? Get in touch at alex@analyticaccountant.com