Summary  
This chapter demonstrates how to automate the creation of a dynamic pivot table and a formula-driven summary block that filters a master dataset to the three most recently completed months—excluding the current partial month—and computes aggregated metrics like sums and averages. It covers setting up hierarchical row structures, using date functions to select periods, and ensuring the report updates automatically as new data is added.  

General domain of usage  
Financial income reporting



In this chapter, you turn your Master Ledger into a clean, professional report.

The goal is to create something you can share with a bank, landlord, or client without exposing raw data.










Start by creating a new sheet and building a PivotTable from the Master Ledger.

Structure it to show:

* Month;
* Tax category;
* Description;
* Sum of amounts.

Filter the data to include only the three most recently completed months, excluding the current one.

Build the PivotTable Report


Check how the data is displayed.

PivotTables may sort months alphabetically instead of chronologically. If needed, adjust the order manually to keep the report readable.

This is a normal limitation when working with text-based dates.

Handle PivotTable Limitations



Next, build a summary section next to the PivotTable.

Use formulas to:

* Detect the last three completed months;
* Calculate total income for each month;
* Compute the three-month average.

Make sure this block pulls data directly from the Master Ledger so it updates automatically.


Add A Dynamic Summary Block

Discover how to use Excel with Copilot to solve real business problems. Explore practical workflows that turn messy data into structured, reliable systems. Learn how to build models, automate calculations, and generate insights without memorizing formulas. Understand how to guide Copilot, validate its output, and keep full control over your logic.

Explore the core setup and habits for working with Copilot in Excel. You configure your environment, learn how to write clear prompts, and apply safe workflows before using Copilot on real data. You focus on testing, verifying results, and protecting your files. These skills help you avoid mistakes and use Copilot with confidence further.

Focus on turning messy, inconsistent datasets into clean and usable data. You standardize headers, split combined fields, fix mixed formats, and remove duplicates using Copilot. Consider real-world scenarios and apply structured workflows to clean data safely.

Discover using Copilot to work with clean data and turn it into insights. You extract values, connect multiple datasets, handle errors, and build dynamic summaries. Learn to describe tasks in plain language while Copilot generates and applies the formulas.

Learn how to present data in a clear and interactive way. Create PivotTables, add slicers, build charts, and apply conditional formatting to highlight key insights. Combine all elements into a structured dashboard and use Copilot to support analysis, formatting, and presentation. 

Build a forecasting model using assumptions, pricing scenarios, and 12-month projections. Test different outcomes, guide Copilot through complex logic, and turn your analysis into a clear, decision-ready recommendation.

Turn your work into a reusable, product-like spreadsheet. Clean raw data, combine multiple sources into a single system, build automated reports, and package everything into a polished template with instructions that anyone can use.

Excel Income Report with Copilot