2 Ways to Reverse Number Signs Positive Negative in Excel - Excel Campus
16

2 Ways to Reverse Number Signs Positive Negative in Excel

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

2 Ways to Reverse Number Signs Positive Negative in Excel 602

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).

Reverse Number Sign - Formula to Multiply by Negative 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.

Reverse Number Sign with Paste Special Multiply Negative 1 in Excel

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

  1. Type -1 in a blank cell on the worksheet.
  2. Select the cells that contains the -1 and copy it.
  3. Select the cell(s) that contain the numbers you want to reverse.
  4. Open the Paste Special menu: Right-click > Paste Special… (Alt,E,S)
  5. Click the “Multiply” radio button in the Operation section of the Paste Special menu.
  6. 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).

Blank Cells Converted to Zero 0 after Paste Special

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.

Select Non-blank Cells with the Go To Special Menu

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

  1. Copy the cell that contains the -1.
  2. Select the range to paste to, including the blank cells.
  3. Open the Go To Special Menu (Home tab > Find & Select > Go To Special…)
  4. Click the Constants radio button to select cells that contain values (non-blank).
  5. Press OK.
  6. The cells that contain values (non-blanks) will be selected.
  7. 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+;

Select Visible Cells Only in Excel

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.

Formulas Modified To Include Multiply by Negative with Paste Special

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! 🙂

Last Day for The Power BI Bonanza

In case you haven’t heard, my friend Mynda Treacy has launched her brand new online course on Power BI.  It’s called Power BI for Excel Users.  In this course you will learn everything you need to know to work with Microsoft’s cool new (free) business intelligence tool.

To share in the excitement, I have also create a new online mini-course on Power BI.  It’s called the Power BI Dashboards & Data course.

Create Interactive Visualizations in Power BI 640

Right now I am giving my course away for free when you enroll in Mynda’s course, Power BI for Excel Users.  Just enter the coupon code TABS at checkout to get my course for free.  Here is a page that explains everything in more detail.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here.
My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career.
I’ve been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP.
I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I’m not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 16 comments
Vignesh - August 24, 2017

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

Reply
Jian - March 12, 2017

Jon,
Thank you very much for your “2 Ways to Reverse Number Signs Positive Negative”.
It is very helpful.

Reply
Wayne Lawrence Thomas - March 8, 2017

Hellow Jon,

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

Reply
Ron Lalancette - March 3, 2017

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!!

Reply
    Jon Acampora - March 6, 2017

    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! 🙂

    Reply
Hocine - March 3, 2017

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

Reply
MARehman - March 3, 2017

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

Reply
Kanize - March 2, 2017

Thanks really clear and succinct.

Reply
Craig - March 2, 2017

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

Reply
    Jon Acampora - March 2, 2017

    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! 🙂

    Reply
Michelle - March 2, 2017

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

Reply
Matilda Paton - March 2, 2017

Great tip another one to keep up my sleeve.

Reply
Gavin - March 2, 2017

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

Reply
    Jon Acampora - March 2, 2017

    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!

    Reply
Rens - March 2, 2017

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

Reply

Leave a Reply: