**Bottom line:** Learn 2 ways to reverse the sign of a number from positive to negative or negative to positive in Excel.

**Skill level:** Beginner

My friend Robbie asked a great question on how to reverse the number signs in Excel. He had a journal entry sheet with a list of debits and credits that was exported from bank system. The numeric values needed to be flipped positive <-> negative for the general ledger accounting system he is using.

Like everything in Excel, there are a few ways to solve this problem. In this article and video we look at how to reverse the signs with a formula and with the Paste Special menu in Excel.

## Video: 2 Ways to Reverse Number Signs Positive Negative

## Method #1 – Multiply by Negative 1 with a Formula

The first method is pretty simple. We can write a formula to multiply the cell's value by negative 1 (-1).

This works on cells that contain either positive or negative numbers. The result of the formula is:

- Positive numbers will be converted to negative numbers.
- Or negative numbers will be converted to positive numbers.

We can then copy/paste the formula to get the results for all the cells in columns B & C.

## Method #2 – Paste Special Multiply Operation

The first solution with the formula works great, but is forces us to create new columns of data that contain formulas. We might want to just change the existing values in columns B & C instead.

This can be done with copy and paste. We will copy a cell that contains a -1, then use the Multiply operation on the Paste Special menu. This will perform the multiplication on the selected cells that we paste to.

Here is a quick screen cast animation that shows how to perform the Paste Special operation.

I explain this in more detail in the **video above**, but here are the steps to reverse the number sign using Paste Special:

- Type -1 in a blank cell on the worksheet.
- Select the cells that contains the -1 and copy it.
- Select the cell(s) that contain the numbers you want to reverse.
- Open the Paste Special menu: Right-click > Paste Special… (Alt,E,S)
- Click the “Multiply” radio button in the Operation section of the Paste Special menu.
- Press OK.

All of the values in the selected cells will be multiplied by -1. The result is that the signs will be reversed for each cell value.

### Only Reverse the Sign in the Non-blank Cells

In our original example column B & C contain some blank cells. If we select all the cells in the range including the blank cells, then do the paste special, the blank cells will converted to 0 (zeros).

Now, we might want to keep those blank cells blank. We can do this by using the Go To Special menu to first select non-blank cells (constants/formulas) before performing the paste.

I explain this in more detail in the **video above**, but here are the steps:

- Copy the cell that contains the -1.
- Select the range to paste to, including the blank cells.
- Open the Go To Special Menu (Home tab > Find & Select > Go To Special…)
- Click the Constants radio button to select cells that contain values (non-blank).
- Press OK.
- The cells that contain values (non-blanks) will be selected.
- Perform the Paste Special Multiply operation (Right-click > Paste Special > Multiply > OK

This time only the selected cells will be multiplied by negative 1 to reverse the sign.

### Only Reverse the Sign on the Visible Cells

We can use the Paste Special Multiply operation on a filtered range as well. We will first want to select the visible cells only on the filtered range. This can be done on the Go To Special menu or with the keyboard shortcut Alt+;

Checkout my article and video on how to copy and paste the visible cells only for more details on this technique.

## Reverse the Sign in Cells that Contain Formulas

The Paste Special Multiply operation also works on cells that contain formulas. The cells in the example below contain a VLOOKUP formula. We might want to reverse the signs of the results, but also keep the formula.

When we perform the paste special on a cell that contains a formula, the formula is modified to add a *-1 to the end of it. That means we do NOT lose the formulas.

We can also use the Go To Special menu here to first select cells that contain Formulas (non-blanks) before performing the Paste Special.

## Keepin Your Sheets Positive

Well, there are two ways to reverse the number signs in Excel. This is a common task when exporting data from financial systems, and needing to flip the signs for journal entries (debit and credits).

What techniques do you use to reverse number signs? Please leave a comment below with any questions or suggestions. Thank you! 🙂

Doesn’t work in scientific format

I used method 2 and now am unable to add my columns. Help

Hi John,

Thanks for your above explanation. i want to develop a formula in excel the formula will be like suppose in A1 i have stored a number 12345678 then in B1 i want to see the number like 785601234 like this way. Could u please help me on this ??

Copy the values and paste special the values with the subtract option

Jon,

Thank you very much for your “2 Ways to Reverse Number Signs Positive Negative”.

It is very helpful.

Hellow Jon,

That thick you showing us is awesome, thanks, for sharing that with all, and blessings to you and your family

Hi Jon,

I transfer data from one workbook to another via numerous cells. I use the formula “=” and the cell from the workbook that I want to copy from. When there is data in the copied from workbook, the data is shown on the active workbook. When there is no data in the inactive workbook, the active cell shows a “0”. How can I make this cell(s) not show anything until a value is inserted into the inactive workbook cell?

Thanks,

Ron Lalancette

ps: Love your newsletters!!

Hi Ron,

Great question! You can use a formula with an IF function.

=IF([Book2.xlsx]Sheet1!A1=””,””,[Book2.xlsx]Sheet1!A1)

The double quotation marks represent a blank cell. This formula checks if the cell is blank. If it is, then it returns a blank, otherwise it returns the cell’s value. We could also use the ISBLANK function instead of the =””.

I hope that helps. Thanks for your support! 🙂

Hi Jon, thank you for this article.

For people who want to reverse the sign into a dynamic range, they can use the format “# ##0;# ##0”. They will get only positive values.

Regards

Thanks a lot for giving tips all these are easy to apply as well as understand.

Thanks really clear and succinct.

Hi Jon

Thanks for the great tips that you share. I really am a fan and have purchased a couple of your courses.

I often work on files that I download in a CSV format off an accounting package. The problem is that when I try and use some of these cells in formulas I get a #Value error. The cells contain a character of sorts and hence why i get these errors. Is there a way of quickly getting rid of these “unseen” characters?

Regards

Craig

Hi Craig,

Great question! Yes, we can use the CLEAN function for this. The CLEAN function removes all non-printable characters from text.

I also cover this in detail in video lesson #7 of module 4 of The Ultimate Lookup Formulas Course.

I hope that helps. Please let me know if you have any questions. Thanks again for your support Craig! 🙂

Jon,

Good stuff! I have a question. Is there an easier way to do this for a column in a pivot table? Thanks.

Great tip another one to keep up my sleeve.

Hi John. Great tip.

Sap is common system for exporting and downloading

Negatives show as 567-

If I have 12 columns months best solution I have is to do click through column by column text to column convert.

Is there better way.

Also data that’s text in export. Best I have is *1 copy multiply send to convert text to value.

Is there better way

Hi Gavin,

Great question! We can use the Find and Replace menu for this to find the “-” character and replace it with blank. This can be done on multiple columns all in one step. I just created a video that explains this in more detail. In the video I also explain how to only convert the “negative” values if your columns contain both numbers stored as text (the negatives) and positive numbers. Here is a link to the video on YouTube. I will probably do a followup post on this as well.

https://www.youtube.com/watch?v=Su3qmTj2LbI

Let me know if that helps, or if you have any questions. Thanks!

The GoTo and Paste Special Multiply are super Excel options, thanks! To come back to your first solution, use extra columns and formulas (which allow better verfication of the results, one could also enter =-A1

Regards,

Rens

Thanks Rens! Yes, you can also use =-A1. Great suggestion! 🙂