How to automatically make all entered numbers in a row negative using Excel?

Solution 1:

Here are the three possibilities:
1. Multiplying with -1
2. Formatting to minus(-) sign
3. ABS() Function

Method 1: Multiplying with -1
1. Enter the numbers as usual, after finishing all entries, do the following:
2. Go to any other empty cell, and type -1 and copy it, now select the entire column you want to make negative.
3. Right-click on the selection and select Paste Special..
4. Choose All and Multiply and click OK, as shown in the picture below: Now all the selected cells will be negative. Now delete the cell value-1 you copied from.

enter image description here

Method 2: Formatting to minus(-) sign (This method only show up as negative in the cells to our eyes, but in formular bar the minus sign won't show up and also will be treated as positive)

1. Select the entire column you want it to be negative, by clicking on the column header.
2. MAC users Hold down Command key and click in any cell(with selection), WINDOWS users right-click, then click Format cells in the context menu.
3. Click Number tab, click Custom Option, on right-hand side, under Type text box select General, and in the Type text box, enter a minus sign like this: -General and click OK.
enter image description here

Method 3: USING ABS
1. Allocate a separate column for positive(this is where you enter the positive values), and allocate another column for negative values(this column/cell only have the ABS formula as follows)
2. IF E1 must have negative value, then choose any column/cell of choice, let's say D1
3. In E1 type =-ABS(D1)
4. Now, whatever value you type in cell D1, the same value will be negative in cell E1
5. Copy the formula you typed once to all cell ranges for example from E1 to E50
6. After finishing all, you can hide the D column which contains D1 positive value.

Note: Positive values will be from D1 to D50 and Negative values will be from E1 to E50 as the above example is concerned

Solution 2:

In my opinion, you would want to automatically convert the cell contents to a negative value and not just show the formatting differently.

I would use VBA to accomplish this. Place this code on the sheet in question

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then 'Column A
        If Len(Target.Value) > 0 Then
          If IsNumeric(Target.Value) Then
             Target.Value = -1 * Abs(Target.Value)
          End If
        End If
    End If
    Target.Parent.Calculate
End Sub

How to automatically make all entered numbers in a row negative using Excel?

Solution 1:

Solution 2:

Related

Recent Posts