How Can You Reverse Signs of Cell Values in Excel?
Simple tutorial to Reverse Cell Values Sign using Paste Special Function & VBA Code.
Sometimes, when you copy values from websites and paste them into Excel, the sign of the numbers may get reversed due to the way the content is designed in HTML. This issue often happens when copying stock prices or financial data from online sources into Excel. Thankfully, Excel provides several simple and effective methods to fix this. In this tutorial, I’ll explain how to reverse the sign of numbers in Excel using both formulas and VBA code, along with clear examples for better understanding.
You can reverse the sign values of the any cells values using the following methods:
- By using Paste Special Function.
- By using VBA Codes.
Method 1: Using Paste Special Function to Reverse Cells Values Sign
- First, you need to enter the value -1 in an empty cell. In this example, the value -1 is entered in cell G3.
- Now, you need to copy the cell G3.
- After this, you need to select the Range of cell values (B3:E13) that need to be reverse its sign.
- Once you selected the range of cells, you need to right click on the chosen range of cells and choose Paste Special -> Paste Special option.
- In the Paste Special dialog box, choose the Multiply from the Operation and then click the OK button.
- Boom, once you did this, The signs will be reversed in the selected range of cells.
That’s it. This is how you can reverse cell values sign using Paste Special function option.
Method 2: Using VBA Code to Reverse Cell Values Signs
This is the alternative method to do. You might need to use the VBA code to reverse your signs of the cell values. You can use the keyboard shortcut ALT + F11 to launch the VBA Editor in Excel.
- Firstly, you need to select the range of cell values that need to be reverse it’s signs (IMPORTANT) as shown in the image.
- Then, you need to use the the keyboard shortcut ALT + F11 to launch the VBA Code editor.
- Now, you need to choose the Insert from the Menu bar and choose the Module option as shown in the image below.
- Now, in the module, you need to paste the VBA Code to reverse the sign of cell values.
Sub Convert()
Dim C As Range
For Each C In Selection
C.Value = -C.Value
Next C
End Sub
- Now, in the Menu bar, you may see the option “Run“, click on it and choose the Run Sub/Userform F5 option to execute the VBA code. Alternatively, you can use the keyboard shortcut F5 to run the command.
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
Recently Published Tutorials:
- How to Sync Selected Range Across All Worksheets in Excel?
- How Can You Convert an Angle Between Degrees and Radians in Excel?
- How to Merge & Combine Columns without Losing Data in Excel?
- Excel File Options Shortcuts List & It’s Explanations!
- How to Use Excel YEAR Function?
