How To Make Numbers Negative in Excel (Three Methods)

Microsoft Excel offers a simple way of converting a group of positive numbers to negative numbers. This function is called the ABS function, and it is used to express a number in its absolute value without a positive or negative sign. Adding a “-” (minus) to the ABS function allows you to express the number in negative form after converting it to its absolute value. Besides the ABS function, you can also use special paste options given in Excel to convert a given selection of positive numbers to negatives.

Press and hold the left mouse button when the “+” sign appears, and drag the mouse downwards until fields B1 to B10 are selected. This will apply the formula “=-ABS(A1)” that you entered in cell B1 to cells from B2 to B10, expressing the corresponding negative values for the positive numbers in the cells A2 to A10 respectively.

Change the way negative numbers are displayed
  1. Select the cell or range of cells that you want to format with a negative number style.
  2. If you’re using Windows, press Ctrl+1. If you’re using a Mac, press +1.
  3. In the Category box, click either Number or Currency.
  4. Under Negative numbers, select an option for negative numbers.

Excel Tip: Make Number Negative – Convert Positive Number to Negative

How to make numbers negative in Excel using a multiplication formula

Follow these steps to make numbers negative in an Excel sheet by creating a formula:

1. Identify the data being changed

Begin by identifying the cell position of the data or range being made negative. Knowing the cell position allows you to write the necessary formula correctly. Having the right location ensures that the formula applies to the correct values.

2. Create a formula to multiply by -1

Using the cell location from the first step, write a formula that multiplies the cell by negative one. The formula looks like this: =[Cell number] * -1. Enter this formula in a blank cell near your desired data range.

3. Apply for all desired data

Apply the formula to the remaining values using the cursor. Select the cell with the original formula and place the cursor at the bottom right corner. The cursor appears as a white plus sign symbol. Use this symbol cursor to highlight the data range by clicking and dragging it across the cells. This applies the formula to the selected cells and makes all the data negative.

Reasons to make numbers negative in Excel

Here are some instances when you might use negative values in an Excel sheet:

How to make numbers negative in Excel using an absolute value formula

Follow these steps to convert positive values to negatives using an ABS formula in Excel:

1. Select the data

Begin by highlighting the cells containing the positive values. Use your cursor to select and highlight all desired values. Ensure that the full range appears highlighted and exclude any blank cells.

2. Enter the formula

In the column next to your data range, insert an ABS formula using the position of the first value. For example, if your data begins in cell “A1” enter the formula “=-ABS(A1)” in cell B2 and press “Enter”. This returns the negative value of the number in the cell.

3. Apply to the remaining data

Place your cursor at the bottom right corner of the cell containing the formula. A white plus sign symbol appears. Use this cursor symbol to drag and apply the formula to the remainder of the data range. This applies the formula to the other numbers and creates negative values.

How to make numbers negative in Excel using the “Paste Special” settings

Here are six steps you can use to make numbers negative using Excels “Paste Special” options:

1. Type “-1” in a blank cell

Locate a blank cell on your spreadsheet near the data set you want to change. Place your cursor in the cell and type negative one (-1). Click anywhere else on the sheet to exit the cell.

2. Copy the cell

Click once on the cell containing the negative one value to select it without editing the content. With the cell selected, copy the cell by right-clicking and selecting “Copy” from the menu. You can also copy the cell using the keyboard shortcut “Ctrl + C”.

3. Select the cells with the data being changed

After copying the cell with the negative number, select the range of data that youre making negative. Drag your cursor over the range of data to highlight all cells. Ensure that all cells appear highlighted and exclude blank cells or rows.

4. Open the “Paste Special” menu

With your desired range highlighted, right-click. Select “Paste Special” from the drop-down menu. This opens a dialog box of settings and functions.

5. Select “Multiply”

From this dialog, locate the “Operations” section. Under this section, select “Multiply”. Press “OK” at the bottom of the dialog box to apply the function. This multiplies the selected data by negative one and makes the values negative.

6. Delete the negative cell

Once you make your data negative, select the cell with the negative one. Press delete to remove the cell and data. This ensures that theres no extra data or values on your sheet to interfere with future functions or reports.

Tips for editing data in Excel

Use these tips to edit and calculate data in Excel:

Please note that none of the companies mentioned in this article are affiliated with Indeed.

FAQ

How do you make a number negative?

Change positive numbers to negative with Paste Special function
  1. Highlight the range that you want to change, then right-click and choose Paste Special from the context menu to open the Paste Special dialog box. …
  2. Then select All option from the Paste, and Multiply from the Operation.

How do I change text to negative in Excel?

You cannot multiply a number by itself to get a negative number. To get a negative number, you need one negative and one positive number. The rule works the same way when you have more than two numbers to multiply or divide. An even number of negative numbers will give a positive answer.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *