removing formulas from excel 2007

Posted on 2011-10-08
Last Modified: 2012-05-12
Dear Sir ,
               i have  a excel sheet in which i have made some rows and columns and given some formulas , i have copied same excel sheet and want to remove the existing formulas .on second sheet
how can i do that ? please advice
Question by:sanjeevkmrs
    LVL 50

    Expert Comment

    by:barry houdini
    If you want to remove the formulas but leave the values derived from those formulas try this:

    Copy whole range of data (whole sheet if required)

    Keep that range selected then use

    Edit > Paste Special > Values > OK > ESC

    regards, barry

    Author Comment

    where is edit option
    LVL 1

    Accepted Solution

    If you want to remove only formulas from the worksheet when someone copies the data of your sheet then try this:

    Select the cells which have formulas in it.

    then press ctrl + 1

    then go to protection tab and select hidden

    Now select raw data cells and press Alt + t + p + a

    then click new and press ok again ok

    Now press Alt + t + p + p and press ok

    Then save the file now your formulas will not be copied

    This is an alternative way from the one which is suggested by Mr. barry and it will keep your formulas hidden when anyone else views your worksheet.

    Have fun
    LVL 1

    Expert Comment

    Hi Mr. Sanjeev,

    If you are not getting edit option then try this shortcut

    Alt + E + S + V

    then press enter and escape

    Have fun
    LVL 9

    Expert Comment

    If you'd like to retain only the results of the formulas, and not the formulas themselves, do the following:

    In your worksheet with the formulas, press Ctrl + A to select the entire worksheet.
    Then, press Ctrl + C to copy the whole sheet.
    Then, click on the worksheet you'd like to copy these to (on the tabs at the bottom, ex: Sheet2).
    Click in cell A1 to select it.
    Then, right-click in cell A1 (in Sheet2), Paste Special, Values.

    Please let me know if you need any further clarification!
    Take care.
    LVL 50

    Expert Comment

    by:barry houdini
    >where is edit option

    ...sorry, that's Excel 2003 - in 2007 you can select the "Paste" option to the left of the Home ribbon then use "Paste Special" from there - although I see other good suggestions using shortcuts etc....

    regards, barry
    LVL 18

    Expert Comment

    by:Richard Daneke
    A great way to select your formulas is to press F5 and choose the Special... button.  Then, choose Formulas - this will highlight only the formulas.
    If you want to replace the forulas with their results, use the Paste Special... as suggested.
    If you want to clear the formulas, press the delete key.

    Author Closing Comment


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now