How to Separate Positive and Negative Amounts into Debit and Credit Columns in WPS Office Spreadsheets
Question details
The user needs to split a single amount column from a CSV or Excel table into separate Debit and Credit columns, where negative values appear as Debit and positive values appear as Credit in WPS Office Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a CSV or Excel table with one amount field and wants a clean worksheet or PivotTable layout that separates negative amounts into Debit and positive amounts into Credit.
- Observed behavior
- The desired result shows negative amounts in a Debit column and positive amounts in a Credit column, including when summarizing data in a PivotTable.
Confirm that the amount column contains numeric values rather than text. If CSV values are stored as text, use Text to Columns in the Data tab or the VALUE function to convert them before creating the Debit and Credit fields.
Add Debit and Credit helper columns before building the PivotTable
Use this method when you need a PivotTable summary. Creating helper columns in the source data is more reliable than adding calculated fields directly inside the PivotTable.
WPS Office Spreadsheets can split amounts with simple row-level formulas, but PivotTable calculated fields work on aggregated values. If you try to create Debit and Credit directly as calculated fields, the IF condition may apply to totals instead of individual rows and produce incorrect results.
Open the CSV or Excel file in WPS Office Spreadsheets. If the amounts are stored as text, select the amount column, click Data > Text to Columns, choose the delimiter, set the format to General, and click Finish.
Add two blank columns next to the amount column. Name the first helper column Debit and the second helper column Credit.
In the first data cell under the Debit column, type =IF(A2<0,-A2,0), replacing A2 with the actual amount cell reference.
In the first data cell under the Credit column, type =IF(A2>0,A2,0), replacing A2 with the actual amount cell reference.
Select both formula cells and drag the fill handle down through all rows that contain amounts.
Select the data range including the Debit and Credit helper columns. Click Insert > PivotTable, choose where to place the PivotTable, and click OK.
In the PivotTable Fields pane, drag Debit and Credit into the Values area. Use Value Field Settings to set both to Sum if they do not default to Sum.
Use worksheet formulas to split amounts without a PivotTable
Use this method when you only need a table view with separate Debit and Credit columns and do not plan to summarize the data in a PivotTable.
Create Debit and Credit Columns in WPS Office Spreadsheets
Use WPS Office Spreadsheets to split a single amount column into separate Debit and Credit helper columns with IF formulas, then add those fields to a PivotTable for a clear and correct summary.
- 1. Open the workbook: Launch WPS Office Spreadsheets and open the CSV or Excel file that contains the single Amount column.
- 2. Add helper columns: Insert Debit and Credit columns beside the amount column. Enter =IF(A2<0,-A2,0) for Debit and =IF(A2>0,A2,0) for Credit, replacing A2 with the actual amount cell reference.
- 3. Create the PivotTable: Select the data range including the helper columns, click Insert > PivotTable, choose the destination, and click OK.
- 4. Summarize Debit and Credit: Drag Debit and Credit into the Values area and set the value field to Sum to see the negative and positive amounts separated.
Frequently Asked Questions
Can I create separate Debit and Credit columns directly as calculated fields in a WPS PivotTable?
It is possible, but not recommended for this task. PivotTable calculated fields operate on aggregated values, so the IF condition may be applied to totals instead of individual rows. Add Debit and Credit helper columns in the source data before creating the PivotTable, then drag those fields into the Values area.
How do I convert CSV amount values from text to numbers in WPS Office Spreadsheets?
Select the amount column, click Data > Text to Columns, choose Delimited, select the correct delimiter, set the column data format to General, and click Finish. You can also use the VALUE function to convert text amounts individually.
Which formulas split negative and positive amounts into Debit and Credit columns?
Debit uses =IF(A2<0,-A2,0) to show negative amounts as positive debit values. Credit uses =IF(A2>0,A2,0) to show positive amounts as credit values. Replace A2 with the cell that contains the original amount.
Will the Debit and Credit helper columns update when new rows are added to the source data?
If your formulas are copied down through the data range and the PivotTable source includes the new rows, they will update after you refresh the PivotTable. You can also convert the range to a WPS table or expand the PivotTable data source to include new rows.
Does this method work with both Excel and CSV files?
Yes. WPS Office Spreadsheets opens and saves Microsoft Excel .xlsx, .xls, and .csv files, so you can use the same Debit and Credit helper column method on all supported formats.
