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.
Download Practice Workbook
You can download the practice workbook from the download button below.
5 Ways to Make a Group of Cells Negative in Excel
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. Make a Group of Cells Negative with Paste Special
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 Group Cells with Same Value in Excel (3 Methods)
2. Make a Group of Cells Negative with Formula
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: How to Lock a Group of Cells in Excel (7 Different Methods)
3. Make a Group of Cells Negative with IF Function
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)
Read More: How to Group Cells in Excel (6 Different Ways)
Similar Readings
- How to Drag Cells in Excel Using Keyboard (5 Smooth Ways)
- Divide a Group of Cells by a Number in Excel (3 Methods)
- How to Protect Selected Cells in Excel (4 Methods)
- Unlock Cells without Password in Excel (4 Methods)
- How to Check If Multiple Cells Are Equal in Excel (4 Methods)
4. Make 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 Move a Group of Cells in Excel (4 Easy Ways)
5. Make a Group of Cells Negative with Excel VBA
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 Sub
Read More: How to Select Cells with Certain Value in Excel (5 Methods)
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.
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. You can also visit our ExcelDemy blog to read more on excel. Stay with us and keep learning.
Related Articles
- How to Move All Cells Down in Excel (5 Methods)
- How to Click One Cell and Highlight Another in Excel (2 Methods)
- [Fix]: Arrow Keys Not Moving Cells in Excel (2 Methods)
- How to Lock Cells in Excel When Scrolling (2 Easy Ways)
- Select All Cells with Data in Excel (5 Easy Methods)
- How to Shift Cells Down in Excel without Changing Formula (4 Methods)