Compile Monthly Sales Data in an Excel Year-to-Date Table
Question details
Organize monthly sales and commission records into a year-to-date database that is easy to filter, sort, and summarize.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to combine sales contracts from multiple months and salespeople for year-to-date reporting.
- Observed behavior
- A single structured dataset should support monthly and year-to-date views without manually combining separate worksheets.
Identify the fields shared by your monthly records, including sold date, salesperson, contract type, contract amount, and commission amount. Make sure dates are stored as dates and amounts as numbers, and decide how to identify duplicate contracts.
Create one Excel table for all sales records
Use a single table as the source for monthly and year-to-date reports.
On a worksheet, enter headers such as Contract ID, Sold Date, Customer, Description, Contract Type, Contract Amount, Commission Amount, and Salesperson.
Enter or paste one contract per row beneath the headers. Keep all months and contract types in the same list, without blank rows or separate sections.
Select the data, choose Insert > Table, confirm that your table has headers, and select OK. Add new contracts in rows immediately below the table.
Use the header filter arrows to select a salesperson, contract type, or date range. For totals, select a cell in the table and choose Insert > PivotTable; place Salesperson or Contract Type in Rows and Contract Amount or Commission Amount in Values.
Add Sold Date to the PivotTable filters and select the dates from the start of the year through the reporting date. Right-click the PivotTable and choose Refresh after adding or changing records.

Append existing monthly tables with Power Query
Use this approach when historical records already exist on separate worksheets.
Build a year-to-date sales report in WPS Spreadsheet
Use WPS Spreadsheet to keep monthly sales in one list, filter contracts by date or salesperson, and summarize amounts with a PivotTable.
- 1. Open or create a workbook: Open WPS Spreadsheet and choose Open to select an existing Excel workbook, or create a blank workbook.
- 2. Build the sales list: Enter column headers for Sold Date, Customer, Contract Type, Contract Amount, Commission Amount, and Salesperson. Paste one contract per row beneath them.
- 3. Filter the records: Select the header row and data, then choose Data > Filter. Use the header arrows to select a salesperson, contract type, or reporting date range.
- 4. Create a summary: Select the sales list and choose Insert > PivotTable. Add Salesperson or Contract Type to Rows, and add Contract Amount or Commission Amount to Values.
- 5. Save the workbook: Choose File > Save As and select Excel Workbook (.xlsx) if you need to share the report with Excel users.

Frequently Asked Questions
Should I create a separate worksheet for each month?
For reporting, one table with a Sold Date column is usually easier to maintain. Use the date filter or a PivotTable to create monthly views from that table.
How do I show sales for one salesperson?
In the source table, open the Salesperson header filter and select the person. Alternatively, add Salesperson to the Rows or Filters area of a PivotTable.
How can I calculate year-to-date totals?
Create a PivotTable from the sales table, put Contract Amount or Commission Amount in Values, and filter Sold Date from January 1 through the reporting date.
Do I need VBA to combine monthly sales sheets?
No. You can keep new records in one table or use Data > Get Data > Combine Queries > Append to combine existing monthly table queries with Power Query.
Why are new records missing from my PivotTable?
Right-click the PivotTable and choose Refresh. If records still do not appear, check that they were added to the source table or that the PivotTable's data source includes them.




