Excel: Convert a Column of Positive Dollar Amounts to Negative Numbers
Question details
Convert a column of whole-dollar amounts from positive numbers to negative numbers without editing cells individually.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user needs to reverse the sign of every amount in a selected column while keeping the values as numbers.
- Observed behavior
- The amounts are currently positive and need to display and calculate as negative values.
Make sure the selected cells contain numeric values rather than text. Do not include headers, totals, or cells that should remain positive in the selection.
Use Paste Special Multiply to Reverse the Sign of an Excel Amount Column
Use this method to change all selected positive amounts to negative values while preserving their numeric formatting and formulas' calculation behavior.
Multiplying the selected values by -1 reverses each number's sign in one operation. This is faster and safer than typing a minus sign into every cell.
Click an unused blank cell, type -1, and press Enter.
Select the cell containing -1 and press Ctrl+C, or choose Home > Copy.
Select only the positive dollar amounts you want to convert. Exclude the column heading and any values that should not change.
On the Home tab, open the Paste drop-down and select Paste Special. You can also right-click the selection and choose Paste Special.
In the Paste Special dialog box, select Multiply under Operation, then click OK. Delete or clear the helper cell containing -1 when finished.
Reverse Positive Amounts to Negative Values with WPS Spreadsheets
WPS Spreadsheets lets you use Paste Special with the Multiply operation to change an entire selected amount range from positive to negative values in a few clicks.
- 1. Create the multiplier: In a blank worksheet cell, enter -1 and press Enter.
- 2. Copy the negative value: Select the -1 cell and press Ctrl+C or choose Home > Copy.
- 3. Select the positive amounts: Highlight the dollar amount cells that need to become negative.
- 4. Apply the Multiply operation: Go to Home > Paste > Paste Special, select Multiply in the Operation area, and click OK.
- 5. Remove the helper value: Select the cell used for -1 and press Delete after the conversion is complete.
Frequently Asked Questions
Will Paste Special Multiply change my currency format?
No. Multiplying by -1 changes the underlying values but normally preserves the existing currency or accounting number format in the selected cells.
Can I convert negative amounts back to positive amounts using the same method?
Yes. Enter -1 in a blank cell, copy it, select the negative values, then choose Home > Paste Special > Multiply. Each negative value becomes positive.
Why does Paste Special Multiply not work on some cells?
The cells may contain numbers stored as text, blanks, or errors. Convert text values to numbers first, then copy -1 and use Paste Special > Multiply again.
Can I use this method on cells with formulas?
Use caution with formula cells. Paste Special > Multiply can replace formulas with their calculated results. For formulas, edit the formula to multiply it by -1 instead, such as =-A1.
