4

I am making a simple cash flow and would like to set up a column for outflows where it automatically makes any number typed in that column a negative. How would I do this?

2
  • 1
    ABS could help. Something like =-ABS(A1)) Jul 14, 2014 at 15:16
  • @Stephen Bailey The solution I gave, worked for you? let me know. Jul 16, 2014 at 7:59

5 Answers 5

10

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

2
  • What version of Excel is this? or do you have a custom theme? Jul 14, 2014 at 15:59
  • @Nathan Taylor Excel 2013. OS Windows 7 but with MAC-AQUA theme and visual styles installed. Jul 14, 2014 at 16:59
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
0

If you just wanted to make sure only negative numbers get typed in you could do the following:

  1. Select the cell
  2. Go to the Data tab and select the Data Validation button.
  3. Select Data Validation again as the format.
  4. For value, choose "Decimal" since you are working with a money format.
  5. For data, choose "less than"
  6. For Maximum, choose "1" or even ".01".

When you try to enter a positive number this will stop processing and pop up an error alert where you are prompted to enter a negative number or zero.

0

I'm doing a similar thing and I just made 3 columns. 1st column is for income, 2nd for expense, 3rd column is =column1-column2.

If the 1st column is empty, then the value on the 3rd is automatically a negative value.

enter image description here

0

I formatted the cells.

  1. Highlight cells you want negative (i did entire columns)
  2. right click
  3. Format Cells
  4. I selected Numbers and chose the negative formatting i wanted
  5. then select Custom
  6. and where it shows 0.00_ ;[Red]-0.00 (is how i wanted it to look) add a - before the first 0.00 -0.00_ ;[Red]-0.00

anything i typed in those columns turned out negative.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .