- Select the cell or range of cells that you want to format with a negative number style.
- If you’re using Windows, press Ctrl+1. If you’re using a Mac, press +1.
- In the Category box, click either Number or Currency.
- Under Negative numbers, select an option for negative numbers.
A straightforward method to change a collection of positive numbers to negative numbers is provided by Microsoft Excel. The ABS function is used to express a number in its absolute value without using a positive or negative sign, and it is known for doing so. After converting the number to its absolute value, you can express it in negative form by adding a “-” (minus) to the ABS function. In addition to the ABS function, Excel offers specific paste options that can be used to change a given selection of positive numbers to negative numbers.
When the “+” symbol appears, press and hold the left mouse button while dragging the mouse downward to select fields B1 through B10. This will apply the formula you entered in cell B1 to cells B2 through B10, expressing the corresponding negative values for the corresponding positive numbers in cells A2 through A10, respectively.
Excel Tip: Make Number Negative – Convert Positive Number to Negative
How to make numbers negative in Excel using a multiplication formula
Create a formula using the following steps to make numbers negative in an Excel sheet:
1. Identify the data being changed
Start by locating the cell in which the data or range is to be made negative. You can correctly write the necessary formula by knowing the cell position. The formula will apply to the proper values if it is in the proper place.
2. Create a formula to multiply by -1
Write a formula that multiplies the cell by a negative number using the cell location from the first step as a starting point. The formula looks like this: =[Cell number] * -1. Put this formula close to the desired data range in a blank cell.
3. Apply for all desired data
Apply the formula to the remaining values using the cursor. Place the cursor in the bottom right corner of the cell that contains the original formula. The cursor appears as a white plus sign symbol. To highlight the data range, move this symbol cursor by clicking and dragging it across the cells. When the formula is applied to the chosen cells, all the data is rendered negative.
Reasons to make numbers negative in Excel
The following situations call for the use of negative values in an Excel sheet:
How to make numbers negative in Excel using an absolute value formula
To use an ABS formula in Excel to change positive values to negatives, follow these steps:
1. Select the data
Begin by highlighting the cells containing the positive values. Use your cursor to select and highlight all desired values. Make sure the entire range is highlighted and remove any empty cells.
2. Enter the formula
Insert an ABS formula using the position of the first value in the column next to your data range. For instance, enter the formula “=-ABS(A1)” in cell B2 and press “Enter” if your data starts in cell “A1”. This gives the cell’s number’s negative value.
3. Apply to the remaining data
Set your cursor to the cell’s bottom right corner to display the formula. A white plus sign symbol appears. Drag this formula to the remaining data range using this cursor symbol. In doing so, the formula is applied to the other numbers, producing negative values.
How to make numbers negative in Excel using the “Paste Special” settings
The following six steps can be used with Excel’s “Paste Special” options to make numbers negative:
1. Type “-1” in a blank cell
On your spreadsheet, find a blank cell close to the data set you want to modify. Type “negative one” (-1) in the cell where your cursor is located. Click anywhere else on the sheet to exit the cell.
2. Copy the cell
To select the cell with the negative one value without making any changes to the content, simply click on it once. Copy the cell by right-clicking it and choosing “Copy” from the menu while it is selected. Using the “Ctrl + C” keyboard shortcut, you can also copy the cell.
3. Select the cells with the data being changed
Select the range of data you’re making negative after copying the cell with the negative number. To highlight every cell in a range of data, move your cursor over the data. Make sure that all cells are highlighted, and omit any rows or cells that are blank.
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”. To activate the feature, click “OK” at the bottom of the dialog box. This produces negative values by multiplying the selected data by one.
6. Delete the negative cell
Select the cell containing the negative value once you’ve made your data negative. Press delete to remove the cell and data. This makes sure that there are no extra values or data on your sheet that could affect how it performs or produces future reports.
Tips for editing data in Excel
Use these tips to edit and calculate data in Excel:
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
How do you make a number negative?
- To access the Paste Special dialog box, right-click and select Paste Special from the context menu after selecting the range you want to modify.
- Next, choose the All option for Paste and Multiply for Operation.
How do I change text to negative in Excel?
A number cannot be multiplied by itself to produce a negative number. One negative and one positive number are required to produce a negative number. When multiplying or dividing more than two numbers, the rule still applies. A positive result will be produced by an even number of negative numbers.
