Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Creating a Copy without Formulas.

Creating a Copy without Formulas

by Allen Wyatt
(last updated September 17, 2016)

3

John knows how to create a copy of a worksheet, but he needs to create a copy that uses only static values, not values based on formulas. He wonders if there is a quick way to make a copy (perhaps with a macro) that maintains all formatting and column widths, but has all formulas replaced with their results. For the work John does this would be very helpful in sending out worksheets to individuals outside his organization.

This task is rather easy to accomplish, with or without a macro. If you want to do it without a macro, follow these steps:

  1. Right-click on the worksheet tab of the worksheet you want to copy. Excel displays a Context menu.
  2. Choose Move or Copy Sheet from the Context menu. Excel displays the Move or Copy dialog box. (See Figure 1.)
  3. Figure 1. The Move or Copy dialog box.

  4. Check the Create a Copy check box.
  5. Using the To Book pull-down list, choose New Book.
  6. Click OK. Excel copies the worksheet to a new workbook.
  7. Make sure the newly created workbook is the one displayed.
  8. Select all the cells in the worksheet by pressing Ctrl+A.
  9. Copy all the cells to the Clipboard by pressing Ctrl+C.
  10. Display the Home tab of the ribbon.
  11. Click the down-arrow under the Paste tool. Excel displays some different ways you can paste information.
  12. Choose the Values option; it looks like an icon that has 123 on it.

That's it. Your newly created worksheet doesn't contain any formulas, only the results of the formulas in the original worksheet. If you prefer to use a macro-based approach, it only takes a few lines of code:

Sub CopyWorksheetValues()
    ActiveSheet.Copy
    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Of course, if you want to distribute only the results of your worksheet, you might consider simply printing a PDF file and then distributing it. The added benefit is that your recipients don't need to have Excel to view it. The downside is that if your worksheet is very large, a PDF file can be rather unwieldy.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12382) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Creating a Copy without Formulas.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Standardizing Note Reference Placement

Want to modify where an endnote or footnote reference appears in relation to the punctuation in a sentence? Here's a way you ...

Discover More

Incrementing Months in Dates

Excel can easily store dates. If you want to increment a date by one month, there are a number of ways you can accomplish the ...

Discover More

Renaming a Document

Want to rename a document that is already on your hard drive? You can, of course, do it in Windows, but you can also do it in ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Getting the Name of the Worksheet Into a Cell

Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in a ...

Discover More

Changing the Height of Worksheet Tabs

Do you need your worksheet tabs to be taller than what they are? You can't make the adjustment in Excel, but you can make it ...

Discover More

Using Very Long Worksheet Tab Names

Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides some ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 4?

2017-03-07 17:36:38

Steve Clark

Allen...Works well except for one thing...if the formulas make use of VB macros (functions), they come over to the new workbook with #NAME? errors. Is there any way to bring these cells over as values also?

Thank You,
Steve C.


2016-11-17 11:50:26

Bob

I did all of the above and it didn't work. :-(. My Excel worksheet was exported from MS Dynamics as a static worksheet as I want to be able to edit the data but I still can't seem to be able to edit it despite trying many different types of copy and pastes.


2016-11-01 02:41:24

bobby

I have a work book in which user enter data in one sheet and extracted (after formatting, and parsing -- a report if you will) in another sheet.
The extraction is done via a Macro, so the user only clicks a button after data entry and gets the report in a second workbook named by date.
My problem. I want to protect the extraction sheet (report) so that a user can't inadvertently or intentionally change the formulas that build the report. But when the Macro runs it hangs up on the protected sheet, spinning or crashes. When I use the Unprotect.Sheet command it requires the password (which I'd like to insert automatically) and after the extraction it doesn't re-protect the sheet again -- at least not the way I tried to do it (via Macros).
So what am I missing?

And yes I realize I may be taking advantage here, but I'm getting desperate.

Thank you for your time.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.