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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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

 

22 comments

Your email address will not be published. Required fields are marked *

  • 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 ??

  • 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

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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly

Macros and VBA Training Webinar