To make a number negative in Excel, type a minus sign before it, or use a formula such as =-ABS(A2) to return a negative version of a value in A2. For a range of positive numbers, you can also multiply the selected cells by -1. Choose a method based on whether you are entering a new number, keeping the original data, or changing existing values in place.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows, or a compatible version of Excel. The basic entry, formula, and Paste Special methods are also available in recent Excel versions for Mac.
- A worksheet with the number or numbers you want to change. If the data matters, keep a copy of the original column or save a backup before replacing values.
- For the formula method, a blank column or other empty cells to hold the results.
Step-by-Step
- Enter a new negative number directly. Click an empty cell, type a minus sign immediately before the number, and press Enter. For example, enter
-42to store negative forty-two. Excel treats the entry as a numeric value, so you can use it in calculations, sort it as a number, and format it as currency or a percentage. Do not put the minus sign after the number, and do not add an apostrophe at the beginning; an apostrophe makes the entry text rather than a number. - Make a negative result with a formula. Use this when a number already exists and you want the negative result in a different cell, leaving the source untouched. With the source value in A2, click B2 and enter
=-ABS(A2), then press Enter. If A2 contains 18, B2 returns -18. If A2 contains -18, B2 still returns -18. ABS returns the value without its sign, and the minus sign in front makes the result negative. If A2 is zero, the result is zero; zero has no negative or positive sign in ordinary Excel arithmetic. - Apply the formula to more rows. With the formula in B2 and source values in A2:A20, select B2 and drag the small square at its lower-right corner down to B20. You can also double-click that square when the adjacent source column has continuous data. Excel adjusts the references as the formula fills, so B3 refers to A3, B4 refers to A4, and so on. Check a few rows, including any existing negative numbers and zeros, to confirm the results match your needs. If the adjacent column has gaps, dragging to the last row yourself is safer than double-clicking.
- Replace the original values with formula results, if needed. Keep the results in the helper column until you have checked them. Select the completed results, copy them, then select the original cells. On the Home tab, choose Paste > Paste Values (the clipboard menu may show a values icon). This replaces the originals with fixed numbers rather than formulas. Confirm the values before deleting the helper column. If you want to retain the original data for reference, leave it in place instead of pasting over it.
- Change positive numeric cells in place with Paste Special. This is a quick option when every selected number is positive and you specifically want to flip its sign. Type
-1in an unused cell and copy it. Select only the positive numeric cells to change, then use Home > Paste > Paste Special. In the dialog, choose Multiply and select OK. Each selected value is multiplied by -1, so 12 becomes -12 and 3.5 becomes -3.5. After checking the results, clear the temporary -1 cell. This operation changes the selected cells, so keep a backup if you may need the original values. - Choose a method that handles mixed signs correctly. Multiplying by -1 flips a sign: a positive becomes negative, but an already negative number becomes positive. If your range contains both positive and negative values and your goal is for every nonzero result to be negative, use
=-ABS(A2)in a helper column, fill it down, and paste values only if you want to replace the source. If you instead want to reverse each number’s existing sign, multiplication by -1 is the appropriate operation.
Common Mistakes
Changing how a number looks instead of its value: A custom number format can display a value with parentheses or a minus sign, but formatting does not turn a positive value into a negative one. For example, a positive amount displayed as “(25)” may still add to a total as positive 25. Check the formula bar or use a calculation if you need to verify the underlying value.
Using Paste Special on mixed signs: Multiplication by -1 reverses signs; it does not guarantee that every result is negative. Use the ABS formula for mixed data when all nonzero results should be negative.
Overwriting the source too soon: A formula can be correct and still refer to the wrong row if it was copied incorrectly. Keep the original column until you have checked the results, especially before using Paste Values or Paste Special.
Turning a number into text: Typing an apostrophe before a minus sign, or importing a value with a leading space, can make Excel treat it as text. Text may align differently and may not behave as expected in arithmetic. A number format does not repair text; convert the entry to a number or re-enter it as a numeric value.
Tips
- Use
=-ABS(A2)when you need to standardize a column so positive and negative inputs both produce negative results. It keeps the source value available for comparison. - Use Paste Special > Multiply only when you intend to reverse the signs of the selected positive values and are sure the selection contains the right cells. Avoid including labels, formulas, or unrelated numeric data.
- For a one-off entry, typing the minus sign is simpler and less error-prone than creating a formula or changing a range.
- Format results after changing their values. Select the cells and use Home > Number Format to choose Number, Currency, or Percentage. The format controls appearance; the minus sign or formula controls the sign.
- When checking a larger range, compare a few values against the source and inspect the total. A sign change from 20 to -20 changes a sum by 40, so an unexpected total can reveal a missed or reversed value.
Troubleshooting
The formula appears in the cell instead of returning a result: The cell may be formatted as Text, or the formula may have been entered with a leading apostrophe or space. Change the cell format to General using Home > Number Format > General, then edit the formula and press Enter. Changing the format alone may not recalculate an existing text entry; re-enter it after changing the format.
The result stays positive: Check the formula spelling and the referenced cell. =-ABS(A2) should return a negative result for any nonzero numeric value in A2. If you used Paste Special, remember that multiplying a negative number by -1 makes it positive. Undo immediately with Ctrl+Z on Windows if the wrong operation was applied; on Mac, use Command+Z.
Excel shows an error or unexpected zero: Confirm that the source cell contains a number rather than text, a blank, or a formula returning an empty string. Click the source and inspect the formula bar. If an imported number is stored as text, use Excel’s warning icon and choose Convert to Number when offered, or remove unwanted spaces and re-enter the value. A true blank referenced by the formula may be treated like zero, so check for missing source data before relying on the result.
The results are in the wrong rows: Check whether the formula references moved as intended. Select a result cell and look at the formula bar; a formula in B5 should normally refer to A5 if the source is in column A. Refill from the first correct formula cell, and make sure the source and result ranges start on the same row.
Paste Special does not show Multiply: Use the full Paste Special dialog rather than a regular paste. Copy the cell containing -1 first, select the destination cells, and open Home > Paste > Paste Special. If the cells contain formulas or a mix of data types, use a helper formula instead. Excel for the web has a more limited interface in some workbooks; if the dialog option is unavailable, use the formula method or open the workbook in the desktop app.
Frequently Asked Questions
How do I make a positive number negative without changing the original?
Put a formula in another cell. With the source value in A2, enter =-ABS(A2) in the result cell. The original stays unchanged, and the result is negative unless the source is zero.
How do I make a whole column negative?
If all the values are positive, copy a cell containing -1, select the numeric cells, and use Home > Paste > Paste Special > Multiply. If the column may already contain negative values and every result should be negative, fill down =-ABS(A2) in a helper column instead.
How do I change a negative number back to positive?
For one value, remove the leading minus sign. To return a positive result from a value in A2, use =ABS(A2). To reverse signs throughout a positive-or-negative range, multiplying by -1 flips each sign.
Can I make a number negative with formatting only?
No. Number formatting changes the display, not the value used in calculations. To make the underlying value negative, enter a minus sign, use a formula, or change the value with Paste Special.
Does the formula work if the source cell is already negative?
Yes. =-ABS(A2) returns a negative result for either a positive or negative nonzero value in A2. By contrast, =-A2 simply reverses the sign, so a negative source would produce a positive result.