+ Reply to Thread
Results 1 to 6 of 6

Negative values in the excel column excel

  1. #1
    Registered User
    Join Date
    06-18-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    76

    Negative values in the excel column excel

    Hi all,
    My question is so simple for you. I have a large excel column containing many values that some of them are negative. These negative values should be multiplied by -1 to change them as the positive value. Could you please let me know how I can do it in excel? in fact, a formula is needed to look for the negative values in the column and changed them to positive ones.

    Thanks for your help in advance.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,115

    Re: Negative values in the excel column excel

    Assuming the list is in column A, starting with A1, then you can put this formula in B1 (insert a new column B if you already have data in there):

    =ABS(A1)

    Then copy this down by double-clicking on the fill handle (the small black square in the bottom right of the cursor, with B1 selected). If you want to get rid of the original data, you can fix the values in column B (with those cells still highlighted after the fill-down, click copy, then right-click and choose Paste Special, click against Values in the panel that pops up, then OK, then press the |Esc| key), and then you can delete column A.

    Hope this helps.

    Pete

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Negative values in the excel column excel

    Can you just use find/replace?
    Select the range to be impacted
    CTRL+H....Shortcut for Home.Find&Replace.Replace
    ...Find what: -1
    ...Replace with: 1
    ...Check: Match entire cell contents
    ...Click: Replace all


    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    06-18-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    76

    Re: Negative values in the excel column excel

    Thanks a lot for your nice solution.

  5. #5
    Registered User
    Join Date
    06-18-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    76

    Re: Negative values in the excel column excel

    Dear Ron,
    my issue was solved by Pete. It's not as simple as just find and replace.
    Last edited by Mary22; 01-02-2016 at 04:34 PM.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,115

    Re: Negative values in the excel column excel

    Glad to help, Mary - thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. negaTIVE VALUES DISPLAY IN EXCEL BAR CHARTS
    By RACKY_E in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-15-2015, 03:03 PM
  2. Replies: 8
    Last Post: 07-28-2012, 03:22 PM
  3. Replies: 0
    Last Post: 05-25-2012, 08:52 AM
  4. Excel query - Represent negative values
    By Siva Rao in forum Excel General
    Replies: 4
    Last Post: 08-10-2011, 09:00 AM
  5. [SOLVED] entering negative time values in excel
    By dona in forum Excel General
    Replies: 5
    Last Post: 03-13-2006, 04:25 AM
  6. how to turn positive to negative values in excel
    By Paula1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-23-2006, 07:00 PM
  7. [SOLVED] how do i hide negative values in excel
    By ccarolla in forum Excel General
    Replies: 3
    Last Post: 12-13-2005, 08:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1