How to Make a Group of Cells Negative in Excel (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Make a Group of Cells Negative in Excel


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.

Make a Group of Cells Negative with Paste Special

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

Make a Group of Cells Negative with Formula

  • 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)

Make a Group of Cells Negative with IF Function

Read More: How to Group Cells in Excel (6 Different Ways)


Similar Readings


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)

Make a Group of Cells Negative with ABS Function

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.

Make a Group of Cells Negative with Excel VBA

  • 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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo