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?
5 Answers
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.
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.
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
-
-
@Nathan Taylor Excel 2013. OS Windows 7 but with MAC-AQUA theme and visual styles installed. Jul 14, 2014 at 16:59
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
If you just wanted to make sure only negative numbers get typed in you could do the following:
- Select the cell
- Go to the Data tab and select the Data Validation button.
- Select Data Validation again as the format.
- For value, choose "Decimal" since you are working with a money format.
- For data, choose "less than"
- 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.
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.
I formatted the cells.
- Highlight cells you want negative (i did entire columns)
- right click
- Format Cells
- I selected Numbers and chose the negative formatting i wanted
- then select Custom
- 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.
=-ABS(A1))