Separate Positive and Negative Bank Amounts in WPS Spreadsheets
Question details
Separate signed bank-statement amounts into positive and negative columns with formulas.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The transaction amounts are in one column and need to be split into separate columns for positive and negative values.
- Observed behavior
- Positive and negative amounts currently appear together in the same amount column.
Confirm which column contains the transaction amounts and where the first data row begins. The examples below assume amounts are in column C and row 1 contains headings.
Use IF formulas to keep positive and negative amounts in separate columns
Use this method when you want two columns that retain the original signs of the transaction amounts.
The positive column returns positive values and leaves negative rows blank. The negative column returns negative values and leaves positive rows blank, which is useful for bank-statement analysis and summaries.
In WPS Spreadsheets, insert or use two empty columns next to the amount column, such as columns D and E. Add headings such as Positive Amount and Negative Amount in row 1.
Select cell D2 and enter =IF(C2<0,"",C2), then press Enter. This keeps zero and positive values from C2 and returns a blank for negative values.
Select cell E2 and enter =IF(C2<0,C2,""), then press Enter. This keeps negative values from C2 and returns a blank for zero and positive values.
Select D2:E2, then drag the fill handle down through the transaction rows, or select the destination range and use Home > Fill > Down or Ctrl+D.
Select the two result columns and use Home > Number Format to choose Currency or Accounting so the separated amounts match the statement format.
Convert negative amounts to positive expense values
Use this option when expense amounts should display as positive numbers in the separate column.
Split Bank Statement Amounts with WPS Spreadsheets Formulas
WPS Spreadsheets lets you use familiar IF formulas to separate income and expenses into dedicated columns, preserve signed values, or reverse expense signs for reports. Fill the formulas down quickly and format the results as currency.
- 1. Open the transaction worksheet: Open your Excel-compatible workbook in WPS Spreadsheets and identify the column containing signed amounts.
- 2. Add positive and negative columns: Insert two adjacent columns and label them for the values you want to report.
- 3. Enter the IF formulas: Use =IF(C2<0,"",C2) for positive values and =IF(C2<0,C2,"") for negative values, adjusting C2 to match your amount cell.
- 4. Fill and format the results: Drag the fill handle down the rows, then choose Home > Number Format > Currency or Accounting.
Frequently Asked Questions
How do I put only positive numbers in a separate column?
If the source amount is in C2, enter =IF(C2>0,C2,"") in the destination cell. Fill the formula down to return positive values and blanks for zero or negative amounts.
How do I show negative bank transactions as positive expense amounts?
Use =IF(C2<0,-C2,"") in the expense column. The -C2 portion reverses a negative source amount, so -25 displays as 25.
Can I keep zero amounts out of both columns?
Yes. Use =IF(C2>0,C2,"") for positive values and =IF(C2<0,C2,"") for negative values. Because neither test includes zero, zero-value rows remain blank in both columns.
How can I copy the separated amounts without keeping formulas?
Select the formula results, copy them, then use Paste Special > Values in WPS Spreadsheets. This replaces the formulas with their current calculated values.
Will these formulas work with an Excel workbook in WPS Spreadsheets?
Yes. Standard IF formulas are compatible with Excel .xlsx and .xls files. You can open the workbook in WPS Spreadsheets, apply the formulas, and save it in the same format.
