How to Format Debit Amounts as Negative Values in WPS Spreadsheets
Question details
The user needs to convert Debit transaction amounts into negative values so that a pivot table can correctly subtract debits from credits instead of adding all positive amounts together.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A bank statement lists transactions as Credit or Debit, but the amount column contains only positive values. The user is building or updating a pivot table and needs debits to reduce the total.
- Observed behavior
- Pivot table totals are inaccurate because credits and debits are both added together as positive numbers.
Open your workbook in WPS Spreadsheets and identify the columns that contain the transaction type (for example, Credit or Debit) and the amount. Make sure you can add a helper column next to your data before building or updating the pivot table.
Create a helper column that makes Debit amounts negative
Use this method when you want to keep the original amount column unchanged and feed correctly signed values into the pivot table.
The helper column leaves the original transaction data intact, so you can audit the original statement later. You can hide the helper column after the pivot table is set up if needed.
In WPS Spreadsheets, select the column to the right of your amount column, right-click the column header, and choose Insert to add a new column. Name it Signed Amount or similar.
In the new column's first data row, enter =IF(B2="Credit",E2,IF(B2="Debit",-E2,"What?? "&E2)) and press Enter. Adjust B2 and E2 to match your transaction type and amount columns.
Drag the fill handle from the first formula cell down through all transaction rows to apply the signed amount formula to every record.
Select your data range including the new Signed Amount column, go to the Insert tab > PivotTable, or update the existing pivot table data source to include the helper column. Then use the Signed Amount field in Values so debits subtract from credits.
Convert existing Debit amounts in place with Paste Special
Use this method if you do not need to keep the original positive amount column and want the source data itself to show Debit amounts as negative numbers.
Format Debit Amounts as Negative Values in WPS Spreadsheets
Use a helper column formula in WPS Spreadsheets to turn Debit transaction amounts into negative values, so your pivot table totals show the correct net balance.
- 1. Open your bank statement workbook: Launch WPS Office and open the Excel-compatible .xlsx or .xls file that contains the Credit/Debit transaction columns.
- 2. Insert a helper column: Right-click the column header to the right of the amount column and choose Insert to add a new Signed Amount column.
- 3. Apply the signed amount formula: Enter =IF(B2="Credit",E2,IF(B2="Debit",-E2,"What?? "&E2)) in the first data row, replacing B2 and E2 with your actual cell references, then fill down through all rows.
- 4. Update the pivot table data source: Go to Insert > PivotTable or PivotTable Tools, include the helper column in the data range, and use Signed Amount in Values to get corrected totals.
Frequently Asked Questions
Can I make Debit values negative without adding a helper column?
Yes. Filter the transaction type column to show only Debit rows, copy -1, select the amount cells, and use Paste Special > Multiply. This changes the original data, so a helper column is safer if you need an audit trail.
Why are my pivot table totals inflated when my data has Credits and Debits?
WPS Spreadsheets sums all positive numbers in the amount field. Unless Debit amounts are stored as negative numbers or converted with a helper column, credits and debits are added together instead of offsetting each other.
What formula should I use if my type column is C and amount column is D?
Use =IF(C2="Credit",D2,IF(C2="Debit",-D2,"What?? "&D2)) and adjust C2 and D2 to match the first row of your data.
Can I use this helper column in an existing pivot table?
Yes. Open the existing pivot table, go to PivotTable Tools or the Analyze/Options tab, change the data source to include the new helper column, and replace the original amount field with the Signed Amount field in Values.
