WPS Spreadsheet: Separate Income and Expenses Without Double-Counting Transfers
Question details
The user needs a pivot table that separates positive income from negative expenses while excluding transfers from both totals.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A transaction list contains income, expenses, and transfers recorded with positive and negative amounts. The user wants a transfer-aware cash-flow report.
- Observed behavior
- Transfers can be counted as both income and expenses when the pivot table groups only by amount sign, inflating both totals.
Confirm that the source data includes consistent Category and Amount columns, and that both sides of each transfer are labeled Transfer. Make sure income amounts are positive and expense amounts are negative before creating the classification field.
Classify transfers before creating the pivot table
Use a helper column to label transfers as Exclude and classify all other transactions by amount sign.
The category check must come before the sign check. This ensures both positive and negative sides of a transfer are excluded instead of being assigned to Income or Expense.
In the first empty column beside the transaction table, enter a heading such as Cash Flow Type.
In the first data cell of the new column, enter =IF([@Category]="Transfer","Exclude",IF([@Amount]>=0,"Income","Expense")). If the data is not formatted as a table, replace [@Category] and [@Amount] with the appropriate cell references, such as =IF(C2="Transfer","Exclude",IF(B2>=0,"Income","Expense"))).
Press Enter, then copy or drag the formula through every transaction row so each record receives Income, Expense, or Exclude.
Select the complete data range, including the new Cash Flow Type column, then choose Insert > PivotTable and select the desired destination.
In the PivotTable Fields pane, drag Cash Flow Type to Rows and Amount to Values, confirm the value calculation is Sum, and filter out Exclude. The remaining rows show separate income and expense totals without transfers.
Use a standard cell-reference helper formula for non-table data
Use this approach when the transaction range is a normal cell range rather than an Excel-style table.
Build a Transfer-Aware Income and Expense Pivot Table in WPS Spreadsheet
WPS Spreadsheet lets you add a sign-based classification column, exclude transfers, and summarize the remaining transactions with a PivotTable. Its familiar spreadsheet tools support a straightforward migration from existing Excel workbooks.
- 1. Prepare the transaction data: Open WPS Spreadsheet and verify the Category and Amount columns in the source worksheet.
- 2. Add the helper classification: Create a Cash Flow Type column and enter =IF([@Category]="Transfer","Exclude",IF([@Amount]>=0,"Income","Expense")), or use ordinary cell references for a non-table range.
- 3. Create the PivotTable: Select the full data range, including Cash Flow Type, then choose Insert > PivotTable.
- 4. Summarize and filter results: Place Cash Flow Type in Rows, Amount in Values, set the value calculation to Sum, and remove Exclude from the filter.
Frequently Asked Questions
Why are transfers appearing as both income and expenses in my pivot table?
A sign-only grouping treats positive transfer entries as income and negative transfer entries as expenses. Add a category-first helper formula that assigns Transfer records to Exclude before checking whether the amount is positive or negative.
How can I exclude transfers with both positive and negative amounts?
Use =IF([@Category]="Transfer","Exclude",IF([@Amount]>=0,"Income","Expense")) in a helper column. Both transfer entries receive Exclude regardless of their signs, and you can filter Exclude out of the PivotTable.
Where do I create a PivotTable in WPS Spreadsheet?
Select the source range, including the classification column, then choose Insert > PivotTable. In the field list, place Cash Flow Type in Rows and Amount in Values, using Sum for the value calculation.
What if my data is not formatted as a table?
Use ordinary cell references instead of structured references. For example, if Amount is in B2 and Category is in C2, enter =IF(C2="Transfer","Exclude",IF(B2>=0,"Income","Expense")) and fill it down.
How do I update the report after adding new transactions?
Extend the PivotTable source range to include the new rows, or use a table-based source that expands automatically. Then select the PivotTable and use its Refresh command so the classification and totals update.
