Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change cells to list the number not the formula (97SR1)

    If I have a column of cells that contain formulas, how would I change the cells to contain only the results of the formula and no longer the formula. I only want the result in the cell.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change cells to list the number not the formula (97SR1)

    Do the following:

    1- Select the cells that contain the formula, or the whole column.

    2- From the Edit menu click on Copy.

    3- From the Edit menu click on Paste Special.

    4- In the top section of the dialog box labeled Paste, click on the radio button next to Values.

    5- Click the OK button.

    The cells should now be replaced with the values.
    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change cells to list the number not the formula (97SR1)

    1/ Select the cells
    2/ Copy the cells
    3/ Right click on the first cell and select paste special
    4/ In the options that come up select values

  4. #4
    Star Lounger
    Join Date
    Jan 2002
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change cells to list the number not the formula (97SR1)

    Thank you! Works great.

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Location
    Johannesburg, South Africa
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change cells to list the number not the formula (97SR1)

    I would highlite the range of cells containing your formulas, copy them and paste them over the highlited range with Edit>PasteSpecial>Values.
    You are left with numbers only, all formulas are gone

  6. #6
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    88
    Thanks
    58
    Thanked 0 Times in 0 Posts

    Re: Change cells to list the number not the formula (97SR1)

    That's the way I do it, too, Manfred. It saves a few extra steps.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change cells to list the number not the formula (97SR1)

    This is a TIP.

    Make Excel formulas permanent with your mouse (95/97/2000)

    One of the tasks that users commonly perform in Excel is converting
    formula results to fixed values. To do this, you probably copy the
    formula and then use the Paste Special dialog box to paste the
    permanent values over the original formulas. However, there's a much
    easier way that doesn't require any trips to the menu bar or Paste
    Special dialog box. First, select the range of formulas you want to
    convert. Then, using the right mouse button, drag the selected range
    one cell in any direction, and then return the range to its original
    location. When you release the mouse button, Excel displays a
    shortcut menu. Simply select Copy Here As Values Only (Copy Values
    in 95) to make the formula results permanent.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Change cells to list the number not the formula (97SR1)

    Hi Servando,
    You can also use a one line macro:
    selection.formula = selection.value
    I have this assigned to a menu item on the right-click shortcut menu for ease of use.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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