How to make a column negative in Excel

You can watch a video tutorial here.

Excel is widely used for calculations because of its convenient spreadsheet format and ease of performing arithmetic operations. You may need to convert a column of numbers from positive to negative. While you can go to each number and insert the minus sign in front of it, this can be time-consuming if you have a long list of numbers.

Option 1 – Using a formula

Step 1 – Create the formula

  • Select the destination cell for the negative number
  • Type the formula using cell references
    • = Positive * -1 
  • Press Enter

Step 2 – Copy the formula

  • Using the fill handle from the first cell, drag the formula to the remaining cells

OR

  1. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
  2. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)

Step 3 – Check the result

  • The positive numbers are converted to negative

Option 2 – Use Paste Special

Step 1 – Copy the value

  • Copy the positive values into a neighboring column
  • Enter -1 (minus one) in a cell outside the column
  • Copy the -1 by pressing Ctrl+C or by right-clicking and selecting Copy from the context menu
  • Select the positive values that were copied 

Step 2 – Open the Paste Special window 

  • Open the Paste Special window by right-clicking and selecting Paste Special from the context menu

OR

Go to Home > Clipboard > Paste > Paste Special

OR

Press Alt+E+S

Step 3 – Paste the value

  • In the Paste Special window, select Multiply
  • Click OK

Step 4 – Check the result

  • All values in the column are multiplied by -1 which turns them into negative numbers

Leave a Comment