How to Convert Positive Numbers to Negative in Excel

Converting positive numbers to negative values in Excel can be a handy skill. You’ll need it for financial modeling, data analysis, data compilation, and even routine data manipulation.

If you’re thinking there would be a function for it in Excel – no there’s no in-built function offered by Excel for value conversion. Yet, there are a handful of methods that you can choose based on what suits you the best.

Want to know how to efficiently flip positive values to negative? 🤏

Follow me through the end of this guide since I am on my way to walk you through the 4 most efficient and quick methods to convert positive (and a mix of positive and negative values) into negative values in Microsoft Excel.

What is learning without practice? Grab your free practice workbook for this tutorial to practice along the way.

Table of Contents

Multiply by -1

The first method to convert a positive number to a negative one is to multiply it by -1. Let’s do it together.

Here I have a list of some positive values in Excel

Step 1) Activate a cell and write the following formula:

Multiplying a positive number by -1 will convert it into a negative number ✖

Step 2) Drag and drop the same to the whole list.

Now if you only want the negative values and no formulas running at the backend:

Step 3) Select the column containing the negative values.

Step 4) Press the Control key + C to copy the same.

Step 5) Go to the destination cell (where you want the negative values to appear).

Step 6) Press the Shift key + F10.

Step 7) Press the V key.

Step 8) Press Enter.

Excel will paste these as values (the formula of multiplication will be replaced by the result) 🧐

Pro Tip!

Make sure your list has all positive values to use this method. If it has a mix of positive and negative values, it might distort the results.

For example, suppose your list has some negative values in between.

As you drag the formula down and multiply it with -1, the already negative numbers will turn positive (a negative value multiplied by another negative value makes a positive value).

Use Paste Special

You’d surely know about the Paste Special feature for sure, but you’d have used it the way I am going to show next too – thin chances 🤯

To convert a positive number into a negative one using the paste special feature, follow the steps below.

Step 1) Activate a cell and type in -1.

Step 2) Copy this cell.

Step 3) Select the list of positive numbers to be converted into negative numbers.

Step 4) Press the Alt Key > E > S.

This will launch the Paste Special dialog box.

Step 5) Select the option ‘Values’ and ‘Multiply’ from the Paste Special box as shown below.

Pro Tip!

Lazy to reach out for the cursor? You can perform both the above steps by using the shortcut keys: Alt key > E > S > V > M

However, make sure to press them in a sequential order (one after another) and not at once.

Step 6) Press Enter.

Excel copied -1 and pasted it to all the selected positive values by multiplying. Amazed, right?

Using the ABS function

The ABS function stands for Absolute. It turns any number into an absolute number. 🎯

This is the only method out of this whole list that you can carefreely apply even if your list has a mix of positive and negative numbers and not only negative numbers.

To show you how, I have purposefully amended the list of positive numbers above to include some negative numbers in it.

To convert all these values into negative values:

Step 1) Write the ABS function with a preceding minus (-) sign and the cell reference of the positive value.

Step 2) Drag the fill handle to apply the formula to the whole list.

All values turn into negative numbers. Pay attention to the yellow highlighted negative values. Excel returns them as negative values, too 🔎

For a mixed values dataset, use this method for conversion.

Pro Tip!

After we’ve seen the promising results this method offers, time for some insights. This formula works in two steps:

• It converts any given value (positive or negative) into an absolute value (positive value).
• Then the preceding minus sign converts that absolute value into a negative value.

This is why even if there are any negative values in the dataset, they are still returned as negative values.

Using Flash Fill

The next and last method is the Flash Fill method. Excel is smart, and we all must always leverage its smart abilities 🚀

So here we go.

Step 1) Activate a cell next to the list of positive values.

Step 2) Write the first value of the list in this cell with a preceding minus sign.

Like I have written 9 as -9 in Cell B2.

We have done this to indicate to Excel how to populate this column (by taking the value in Cell A2 and adding a minus sign before it).

Step 3) Select Column B up to the cell where the adjacent list of positive values goes.

Step 4) Go to the Home tab > Editing group > Fill button > Flash Fill.

Excel will flash-fill the remaining cells based on the pattern indicated above, and here are the results.

Smart enough, no?

Don’t try this method if your list has a mix of positive and negative values because Excel is smart for sure but not that smart. This is what will happen:

It combines negative values with another negative sign making them double minus values which is not desired (and that doesn’t even make sense).

Conclusion

With this guide, you now know all the methods to convert positive numbers into negative numbers in Excel.

I hope you enjoyed learning these smart ways of converting values and why only for conversion? You can also use these methods for a variety of other purposes. Read my following Excel tutorials to learn how and what purposes and enhance your Excel skills.

• Add Days to Date Using an Excel Formula (Easily)
• How to Add Total Row in Excel: Best 2 Methods (2024)