Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. 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 November 23, 2019)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 Excel in Microsoft 365. 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 Excel in Microsoft 365. 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!
When developing a macro, it is often necessary to step through the various code lines so you can see what is happening on ...
Discover MoreOne of the powerful programming structures available in VBA is the Select Case structure. This tip explains how you can ...
Discover MoreWant to run a macro when you first select a worksheet? You can do so by using one of the event handlers built into Excel, ...
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 © 2023 Sharon Parq Associates, Inc.
Comments