How to Combine Monthly Excel PivotTables for Annual Analysis in WPS Spreadsheets
Question details
The user wants to turn twelve monthly Excel datasets into one structured WPS Spreadsheets table and create a single PivotTable for annual reporting instead of manually merging twelve copied PivotTables.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains monthly reports in separate Excel files or sheets and needs an annual view with month, quarter, or year groupings for reporting and charts.
- Observed behavior
- Twelve separate monthly PivotTables exist, and the user may try to merge copied PivotTables manually, which cannot be centrally grouped, filtered, or charted.
Confirm that all monthly source workbooks use the same column headers, consistent data types, and a usable Date or Month column. Add a Date column to any source table that stores only a month name so the PivotTable can group correctly.
Append monthly source data and build one annual PivotTable
Use this solution when you have twelve monthly Excel datasets and want a single PivotTable that supports month, quarter, and year grouping.
Do not copy and combine PivotTables. A PivotTable works correctly only when it reads a source range. Combining the underlying monthly tables into one master table lets WPS Spreadsheets treat all months as one dataset and group dates with a single field list.
In WPS Spreadsheets, open a new workbook and create a sheet named Monthly Data. Add the exact header row you will use across all monthly records, such as Date, Region, Product, Sales, and Units.
Open each monthly Excel file, confirm its columns match the master headers, then copy the data rows and paste them below the previous rows in the master sheet. Keep one row per record and do not insert blank columns between fields.
Click any cell inside the combined range, go to the Insert tab, and click PivotTable. In the Create PivotTable dialog, choose New Worksheet and click OK.
In the PivotTable Field List, drag Date to Rows, drag Category or Region to Columns or Filters as needed, and drag numeric fields such as Sales to Values.
Right-click any date row label in the PivotTable, select Group, check Months, Quarters, and Years, then click OK. The PivotTable now expands from year to quarter to month.
Build an annual WPS Spreadsheets PivotTable from monthly source data
WPS Office Spreadsheets lets you append twelve monthly source ranges, insert one PivotTable, and group dates by month, quarter, or year for a single annual report. You get a familiar Excel-like ribbon, .xlsx file compatibility, and no manual merging of copied PivotTables.
- 1. Prepare the master data: In WPS Spreadsheets, open a new workbook and copy all twelve monthly tables into one sheet using identical column names and date values.
- 2. Insert a PivotTable: Select the combined range, click Insert > PivotTable, choose New Worksheet, and click OK.
- 3. Add reporting fields: Drag Date to Rows, add category or region fields to Columns or Filters, and drag numeric fields to Values.
- 4. Group dates: Right-click a date row label, choose Group, select Months, Quarters, and Years, and click OK.
- 5. Refresh and visualize: After updating the master data, click Data > Refresh All, and use Insert > Chart with the PivotTable selected to build annual charts.
Frequently Asked Questions
Can I combine monthly Excel source files into WPS Spreadsheets without retyping data?
Yes. Open each workbook in WPS Spreadsheets, confirm the headers match, and copy the rows into one master sheet. For structured CSV files, use the Data tab import options and append the imported sheets to the master table.
How do I group a date field by month, quarter, and year in a WPS PivotTable?
Right-click any date label inside the PivotTable, choose Group, check Months, Quarters, and Years, and click OK. WPS Spreadsheets then treats the date field as a grouped field that can expand or collapse from year to month.
Do my monthly sheets need identical columns before I combine them?
Yes. Use identical headers so the PivotTable reads each field correctly. The column order can differ, but each field must use the same data type—especially dates—for correct grouping and calculations.
Why is a single source PivotTable better than merging twelve PivotTables manually?
A single PivotTable reads one source range, so filters, grouping, totals, and charts update from one field list. Copying multiple PivotTables cannot be grouped together and often creates mismatched totals and formatting.
Will my Excel PivotTable work in WPS Spreadsheets?
WPS Spreadsheets can open .xlsx workbooks and existing PivotTables. For an annual analysis from multiple monthly files, create one PivotTable from the combined source data rather than reusing separate PivotTable copies.
