Changing the Sign of a Cell

Posted on by Dick Kusleika

You probably already know the trick for changing the signs on a bunch of cells. But if not, here it is:

  1. Type -1 into an unused cell on your worksheet
  2. Copy that cell
  3. Select the cells whose sign you want to change
  4. Paste Special – Multiply

I do this quite a bit. Recently when I had to enter a pretty long list of numbers, most of which were negative, I decided to enter them with their sign reversed (for ease of entry) and employ this technique. Then I thought that it was just too much work. So I wrote a macro. It doesn’t do exactly what the Copy – Paste Special – Multiply trick does, but arguably does it better.

Private Const msFORMADD As String = ")*-1"
Private Const msFORMST As String = "=("

Sub ChangeSign()

Dim rCell As Range

gclsAppEvents.AddLog "^+n", "ChangeSign"

If TypeName(Selection) = "Range" Then
For Each rCell In Selection.Cells
If CellCanChangeSign(rCell) Then
If rCell.HasFormula Then
If CellFormulaHasSignChange(rCell) Then
rCell.Formula = RemoveFormulaSignChange(rCell.Formula)
Else
rCell.Formula = Replace(rCell.Formula, "=", msFORMST, 1, 1) & msFORMADD
End If
ElseIf IsNumeric(rCell.Value) Then
rCell.Value = -rCell.Value
End If
End If
Next rCell
End If

End Sub

Function CellCanChangeSign(rCell As Range) As Boolean

CellCanChangeSign = rCell.Address = rCell.MergeArea.Cells(1).Address And Not IsEmpty(rCell.Value)

End Function

Function CellFormulaHasSignChange(rCell As Range) As Boolean

CellFormulaHasSignChange = Left$(rCell.Formula, Len(msFORMST)) = msFORMST _
And _
Right$(rCell.Formula, Len(msFORMADD)) = msFORMADD

End Function

Function RemoveFormulaSignChange(ByVal sFormula As String) As String

Dim sReturn As String

sReturn = Left$(sFormula, Len(sFormula) - Len(msFORMADD)) 'remove last characters
sReturn = Replace$(sReturn, msFORMST, "=", 1, 1) 'remove first paren

RemoveFormulaSignChange = sReturn

End Function

is the entry point procedure and is called with Ctrl+Shift+n (more on what AddLog is in a later post). After it determines that there is a range selected (and not a shape, for example), it calls

to make sure it’s OK to move forward. In

I try to deal with merged cells. I don’t use merged cells a ton, so I can never remember how to avoid problems in VBA when dealing with them. I decided that if the cell was the first cell in the merge area, that was good enough. If the cell isn’t merged, its MergeArea is equal to itself, so this would still return True. Also, I don’t want to do anything to empty cells so I make sure that’s not the case.

Once I verify that the cell is good to go, I treat cells with formulas different than cells with values. When you use Paste Special – Multiply, Excel takes whatever formula you have and changes the formula to multiply by -1. For example, =SUM(F6:F14) becomes =(SUM(F6:F14))*-1. I’m not sure why it doesn’t negate the formula with -(formula), but it doesn’t. I made mine match what Excel does.

One problem with changing the signs of formulas is that if you do it twice, then =SUM(F6:F14) becomes =((SUM(F6:F14))*-1)*-1. Of course what else could it do. It’s a multiplying operation, not a toggle. I tried to make mine a toggle at least where I could. If you monkey with the formula between operations, you may get multiple multiplications, but if you just run the code twice it will remove what it did. I’ve tested this nearly a half dozen times, so I’m sure it’s rock solid.

checks that

is at the start of the formula and

is at the end. If they are then 

RemoveFormulaSignChange


 removes them and sets the formula back to its original.


For cells with numbers and not formula, the Value is simply negated.

13 thoughts on “Changing the Sign of a Cell”

  1. or ?

  2. snb: your approach doesn’t work on formulas like this:
    = 1 + 1

    Also, for anyone wondering what those mysterious numbers in SpecialCells are:
    1 = xlNumbers
    2 = xlCellTypeConstants
    -4123 = xlCellTypeFormulas

  3. Dick: There’s a great procedure to employ at a bank as part of an April Fools trick. Get it to fire whenever the user does a cut and paste, along with the following custom number format:
    “-“##0;##0;0

  4. If you are willing to give up your “toggle” and just have the code do exactly what Excel does…


    Sub ChangeSign()
    With Cells(Rows.Count, "A").End(xlUp).Offset(1)
    .Value = -1
    .Copy
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
    SkipBlanks:=False, Transpose:=False
    .Clear
    End With
    End Sub

  5. @Jeff

    I always assumed everybody has the object browser (F2) in the VBEditor available.

    Sub M_snb()
    For Each ar In Columns(1).SpecialCells(-4123, 1)
    ar.Formula = Replace(ar.Formula, "=", "=-(") & ")"
    Next
    End Sub

  7. The Sub M_snb does not work for functions that return an error. Also, Ctrl+Shift+Enter functions are converted to Enter functions
    perhaps:

    Sub ChangeSign()
    For Each ar In Columns(1).Cells
    If ar.HasArray Then
    ar.FormulaArray = Replace(ar.FormulaArray, "=", "=-(") & ")"
    ElseIf ar.HasFormula Then
    ar.Formula = Replace(ar.Formula, "=", "=-(") & ")"
    End If
    Next
    End Sub

  8. I’ve used something like this for a few years. There is an evil edge case where a number has a trailing minus sign. Isnumeric will return true which may or may not be the behavior expected depending on the source data.

  9. Beware of ListColumns with formulas. I just applied this to a column in a ListObject (with 20k rows) that had a formula. See if you can guess what happened. :)

    If you can’t get guess, type this into Excel

    Name Number
    Tom =ROW()
    Dick =ROW()
    Harry =ROW()
    Joe =ROW()

    Ctrl+T to make it a table. Select the Number column and run this procedure.

  10. Function CellCanChangeSign(rCell As Range, rSel As Range) As Boolean

    Dim bReturn As Boolean

    bReturn = rCell.Address = rCell.MergeArea.Cells(1).Address
    bReturn = bReturn And Not IsEmpty(rCell.Value)
    bReturn = bReturn And Not (Not rCell.ListObject Is Nothing And rCell.Row > rSel.Cells(1).Row And rCell.HasFormula)

    CellCanChangeSign = bReturn

    End Function

    That seems to fix the ListObject problem. How about that last row for true code readability. It says if the cell is part of a table, and it's in the first row of the selection and it has a formula, then change it. That should ensure that only one cell per formula-containing-ListColumn gets changed.

  12. When I ran it, it changed the first cell, which changed all the other cells in the column as Tables do. Then it changed the second cell (which was already changed as a result of the first) and that in turn changed all the other cells. With four cells, it changes and changes back twice leaving them at their original formulas.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.