Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Creating a Plus/Minus Button.
Written by Allen Wyatt (last updated December 10, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
On some calculators there is a little button that can come in very handy: the plus/minus button. This button, when pressed, will switch whatever value is on the display between its positive and negative values. For instance, if the display shows the number 57, then pressing the button will change the display to -57. Pressing it again will switch the value back to 57.
If you would like a "button" that does this in Excel, you'll quickly find that there is none built into the program. You can quickly create one, however, by using a macro:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Sub PlusMinus()
Dim cell As Range
On Error Resume Next 'copes with cells that are not numeric
For Each cell In Selection
If Not cell.HasFormula Then cell.Value = -cell.Value
Next cell
End Sub
Note that the macro simply steps through whatever range of cells you selected when the macro started. Each cell is tested to make sure it contains a numeric value. When would a cell not contain a number? The most critical time is when it contains a formula, you don't want to mess those up. Another instance is when the cell contains a date. You don't want to change those dates to minus values. Cells that contain either a formula, a date, or a label will generate an error since "cell.Value" isn't a numeric value. If the cell truly contains a number, then the result is a switch in sign for the number.
You can assign this macro to a shortcut key or add it to the Quick Access Toolbar to make it easy to use at any time.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9271) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Creating a Plus/Minus Button.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Want to know how much of a time difference there is between your machine and a different machine? This tip provides some ...
Discover MoreWant to print a document by using a macro? One way is to display the Print dialog box and allow the user to interact with ...
Discover MoreWhat would you do if you had a macro-enabled workbook that refused to work properly on computers using later versions of ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-12-05 10:25:50
J. Woolley
The subject of this Tip is very similar to a recent Tip titled Negating a Cell Using a Macro. That Tip attracted a plethora of Comments.
See https://excelribbon.tips.net/T010127_Negating_a_Cell_Using_a_Macro.html
My Excel Toolbox includes the Negate macro to negate all numeric constants in a Selection (ignoring other cells). The Negate macro supports Undo.
See https://sites.google.com/view/MyExcelToolbox/
2021-12-04 19:48:37
Fernando Ramos
Hi Allen.
Great code.
A small observation: If you apply this code to empty cells it will change it to a cell with a zero.
This small change should fix that.
Sub PlusMinus()
Dim cell As Range
On Error Resume Next 'copes with cells that are not numeric
For Each cell In Selection
If Not cell.HasFormula and cell.Value <> 0 Then cell.Value = -cell.Value
Next cell
End Sub
Greetings from Mexico
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments