How to Combine Two Excel Worksheets for One PivotTable in WPS Office
Question details
The user needs to combine billed and payment data from separate worksheets so a single PivotTable can show unpaid or rejected amounts.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has billed data and payment data in two separate worksheets and wants to analyze unpaid or rejected amounts in one PivotTable.
- Observed behavior
- The separate worksheets currently prevent one PivotTable from using both data sources together.
Check that both worksheets use the same column headers, especially for customer or invoice, amount, and status fields, and format amounts consistently before combining them.
Combine the Worksheets into One Table and Build the PivotTable
Use this method when you need a reliable, editable combined source table before creating a PivotTable for unpaid or rejected amounts.
WPS Spreadsheets creates PivotTables from one contiguous source range. Combining the worksheets into a single table with consistent headers ensures all billed and payment records can be analyzed together.
If you already have separate status values, add or standardize a Status column with values such as Billed, Paid, Unpaid, or Rejected.
Open the workbook in WPS Spreadsheets, click the plus icon next to the sheet tabs to add a new worksheet, and give it a name such as Combined.
Go to the first source worksheet, select the header row and all data rows, then press Ctrl+C to copy them.
Click the new worksheet, select cell A1, and press Ctrl+V to paste the first table.
Go to the second source worksheet, copy its data rows without the header row, then paste them directly below the first table in the combined sheet.
In the combined sheet, add a Status column if needed and fill it with values such as Billed, Paid, Unpaid, or Rejected.
Select any cell in the combined table, go to the Insert tab, and click PivotTable.
Drag the customer or invoice field to Rows, drag the amount fields to Values, and drag the Status field to Filters or Columns to identify unpaid or rejected records.
Combine Two Excel Worksheets into One PivotTable with WPS Office
Use WPS Spreadsheets to consolidate your billed and payment data into one structured table, create a PivotTable, and filter by Status to quickly display unpaid or rejected amounts.
- 1. Open your workbook: Launch WPS Office, open the workbook that contains the billed and payment worksheets.
- 2. Create a combined table: Add a new worksheet, paste both data sets into one table, and use consistent headers plus a Status column.
- 3. Insert a PivotTable: Select a cell in the combined table, click the Insert tab, and choose PivotTable.
- 4. Configure fields: Place customer or invoice in Rows, amount fields in Values, and Status in Filters or Columns.
- 5. Filter the result: Use the Status filter to show unpaid or rejected records and analyze the outstanding amounts.
Frequently Asked Questions
Can I create a PivotTable from two separate worksheets without combining them first?
In WPS Spreadsheets, a PivotTable works best from one structured source range. Combine the worksheets into a single table first, then select the table and click Insert > PivotTable.
What fields should I add to show unpaid or rejected amounts?
Add or standardize a Status column with values such as Billed, Paid, Unpaid, or Rejected. In the PivotTable, place Status in Filters or Columns, customer or invoice in Rows, and amount fields in Values.
Does the column order need to match when combining worksheets?
Yes. Keep the headers identical and in the same order so the pasted rows align correctly. Mismatched headers can split fields or create blank values in the PivotTable.
Can I use this method for large datasets?
Yes. For large datasets, format the combined range as a table and make sure there are no blank rows or columns before inserting the PivotTable.
Is WPS Office compatible with Excel PivotTable files?
Yes. WPS Spreadsheets opens and saves .xlsx workbooks and preserves PivotTable structure with Microsoft Excel compatibility.
