This article shows 5 easy ways to make a group of cells negative in excel. The Paste Special feature, some formulas with functions, and Excel VBA are used in those methods. The following picture highlights the purpose of this article.
Here I am going to highlight the 5 methods for you to make a group of cells negative in Excel. We will use the following dataset to illustrate the methods. So, let’s start.
1. Using Paste Special Tool to Make a Group of Cells Negative in Excel
You can easily make a group of positive cells negative with the Paste Special feature in Excel. Follow the steps below to be able to do that.
📌 Steps
- First, enter -1 in an empty cell. Then copy the cell. After that, select the group of cells that you want to make negative as shown in the following picture.
- Next press CTRL+ALT+V to open the Paste Special dialog box. Then choose All except borders for Paste and Multiply for Operation. After that, hit the OK button.
- Finally, you will see that the cells are showing negative values as shown below. Now you can clear the cell containing -1.
Read More: How to Add Negative Numbers in Excel
2. Applying Excel Formula to Make a Group of Cells Negative
Alternatively, you can use a simple formula to do that.
- If you are not using Office 365, then use CTRL+SHIFT+Enter to enter the following formula in cell D5.
=-1*C5:C10
- Now you can copy the converted values and paste them as values on the Credit column and then delete the Conversion column.
Read More: [Fixed!] Excel Not Adding Negative Numbers Correctly
3. Inserting IF Function to Convert a Group of Cells Negative
You can also use the IF Function to get the same result.
- Use the following formula in cell D5. Then apply it to the cells below using the fill handle tool. After that, you can copy and paste as in the earlier method.
=IF(C5<0,C5,-C5)
4. Making a Group of Cells Negative with ABS Function
The ABS function also helps to make cells negative in Excel. You need to enter the following formula in cell D5 to do that. Use CTRL+SHIFT+Enter to enter the formula.
=-ABS(C5:C10)
Read More: How to Put a Negative Number in Excel Formula
5. Using Excel VBA to Make a Group of Cells Negative
Follow the steps below to make a group of cells negative with Excel VBA.
📌 Steps
- First press ALT+F11 to open the Microsoft Visual Basic for Applications window.
- Then select Insert >> Module to open a new blank module.
- After that copy the following code.
Sub MakeCellsNegative()
Dim Credit, Dataset As Range
On Error Resume Next
Set Dataset = Range("C5:C10")
For Each Credit In Dataset
Value = Credit.Value
If Value > 0 Then
Credit.Value = Value * -1
End If
Next
End Sub- Then paste the copied code on the blank module. Next press F5 to run the code.
- Finally, the cells will show negative values as follows.
- You can repeatedly run the following code instead to make the cells negative or positive.
Sub MakeCellsNegativeOrPositive()
Dim Credit, Dataset As Range
Set Dataset = Range("C5:C10")
For Each Credit In Dataset
Value = Credit.Value
Credit.Value = Value * -1
Next
End SubRead More: How to Show Negative Numbers in Excel
Things to Remember
- Don’t forget to press CTRL+SHIFT+Enter to enter the array formulas.
- Change the range in the code according to your dataset.
Download Practice Workbook
You can download the practice workbook from the download button below.
Conclusion
Now you know how to make a group of cells negative in Excel. Please use the comment section below for further queries or suggestions. Stay with us and keep learning.
