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.
- 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.
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.
- Now you can copy the converted values and paste them as values on the Credit column and then delete the Conversion column.
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.
- 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.
5. Make a Group of Cells Negative with Excel VBA
Follow the steps below to make a group of cells negative with Excel VBA.
- 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
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.
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.
- 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)