Data Import & Export

Compile Monthly Sales Data in an Excel Year-to-Date Table

Camila MilosovichCamila Milosovich Nov 18, 2026 870 views

Question details

Organize monthly sales and commission records into a year-to-date database that is easy to filter, sort, and summarize.

Compile Monthly Sales Data in an Excel Year-to-Date Table
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.
Before you start

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.

Solution 1Recommended

Create one Excel table for all sales records

Use a single table as the source for monthly and year-to-date reports.

1
Set up the columns

On a worksheet, enter headers such as Contract ID, Sold Date, Customer, Description, Contract Type, Contract Amount, Commission Amount, and Salesperson.

2
Add the records

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.

3
Create a table

Select the data, choose Insert > Table, confirm that your table has headers, and select OK. Add new contracts in rows immediately below the table.

4
Filter and summarize

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.

5
Set the reporting period

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.

Create one Excel table for all sales records
Keep the source data consistent: Use consistent column names and check Contract ID values for duplicates before summarizing. Store contract types in a column rather than in separate worksheet sections.
Organize sales in WPS Spreadsheet

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. 1. Open or create a workbook: Open WPS Spreadsheet and choose Open to select an existing Excel workbook, or create a blank workbook.
  2. 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. 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. 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. 5. Save the workbook: Choose File > Save As and select Excel Workbook (.xlsx) if you need to share the report with Excel users.
Filter sales records by month, salesperson, or contract typeSummarize contract and commission amounts in a PivotTableOpen and save Microsoft Excel formats, including XLSXWork with a familiar spreadsheet interface
microsoft office alternative - wps office

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.