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.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
It is possible to create macros that send out reports, via e-mail, from within Excel. Frank did this and ran into ...
Discover MoreUsing a specialized calendar control is a great way to let users add dates to a worksheet. Unfortuantely, Microsoft ...
Discover MoreEver wonder what the macro-oriented equivalent of pressing Ctrl+End is? Here's the code and some caveats on using it.
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