5 Ways To Change Numbers To Negative In Excel

Numbers are an essential part of any Excel spreadsheet, and sometimes you may need to change positive numbers to negative or vice versa. This can be useful in various scenarios, such as tracking expenses, calculating losses, or representing negative values in financial data. In this article, we will explore five ways to change numbers to negative in Excel, along with some practical examples and tips.

Method 1: Using the Negative Sign (-)

The simplest way to change a positive number to negative in Excel is by prefixing it with a negative sign (-). You can do this manually by typing the negative sign before the number, or you can use the keyboard shortcut Ctrl + -. This method is useful when you need to change a small number of cells.

For example, suppose you have the number 100 in cell A1 and you want to change it to -100. Simply type -100 in cell A1, or select cell A1 and press Ctrl + -.

Example:

Cell A1	Formula
100	-100

Method 2: Using the NEGATE Function

The NEGATE function is a simple way to change a positive number to negative in Excel. The syntax for the NEGATE function is:

NEGATE(number)

Where number is the value you want to negate.

For example, suppose you have the number 200 in cell A2 and you want to change it to -200. You can use the NEGATE function as follows:

=NEGATE(A2)

Example:

Cell A2	Formula	Result
200	=NEGATE(A2)	-200

Method 3: Using a Formula with the Asterisk (*) Operator

You can also change a positive number to negative using a formula with the asterisk (*) operator. The syntax for this formula is:

=-A1

Where A1 is the cell containing the positive number.

For example, suppose you have the number 300 in cell A3 and you want to change it to -300. You can use the formula as follows:

=-A3

Example:

Cell A3	Formula	Result
300	=-A3	-300

Method 4: Using a VBA Macro

If you need to change a large number of cells from positive to negative, you can use a VBA macro. Here's an example macro that does this:

Sub ChangeToNegative() Range("A1:A10").Value = Range("A1:A10").Value * -1 End Sub

This macro selects the range A1:A10 and multiplies the values by -1, effectively changing them from positive to negative.

Example:

Range A1:A10	Result
100, 200, 300,...	-100, -200, -300,...

Method 5: Using Power Query

Power Query is a powerful data manipulation tool in Excel that allows you to change data types, perform calculations, and more. You can use Power Query to change positive numbers to negative.

To do this, go to the "Data" tab and click on "From Table/Range". Select the range containing the positive numbers and click "OK". In the Power Query Editor, go to the "Add Column" tab and click on "Custom Column". Enter the formula =-[YourColumn] and click "OK".

For example, suppose you have the following table:

Number
100
200
300

You can use Power Query to change the numbers to negative as follows:

Number	NegativeNumber
100	-100
200	-200
300	-300

Gallery of Excel Negative Number Images

Excel Negative Number Image Gallery

We hope this article has helped you learn five ways to change numbers to negative in Excel. Whether you're using the negative sign, the NEGATE function, a formula with the asterisk operator, a VBA macro, or Power Query, you can easily change positive numbers to negative in your Excel spreadsheet. Do you have any other Excel questions or topics you'd like us to cover? Let us know in the comments below!