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

Written by Allen Wyatt (last updated January 12, 2022)
This tip applies to Excel 2007, 2010, 2013, and 2016


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()
    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.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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


Comments in Text Boxes

If you use text boxes in your documents, you may sometime want to place a comment in the text box, the same as you can do ...

Discover More

Moving a Table Column

Want to move a column in a table very easily? You can do so by using the same editing techniques you are already using.

Discover More

Printing Post Office Permits on Envelopes

When preparing to snail-mail information, you may want to print your envelopes with permit information in the upper-right ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Automatically Renaming Worksheets

Excel allows you to easily add and remove worksheets from a workbook. You may want a way to automatically rename all of ...

Discover More

Testing for an Empty Worksheet

If you are using a macro to process a number of worksheets, you may have a need to know if the worksheet is empty or not. ...

Discover More

Limiting How Many Times a Worksheet Can Be Calculated

Excel, by default, recalculates your worksheets as you make changes in those worksheets. If you want to limit the number ...

Discover More

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

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 two more than 7?

2021-09-08 05:19:49


Thanks so much for this. God bless you.

2021-06-29 15:35:53

Ali Abdullah

thanks a lot. it is useful

2020-11-17 08:40:59


I tried the Macro as shown. The result was that the copied tab in the new workbook retained formulas while the original tab in the original file had no formulas.

2019-08-01 17:01:38


Thank you!

2018-03-07 11:26:21

Peter Atherton


[c8].PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Copy from B6 to C8

2018-03-06 05:19:50

Steve Jez

Proceed as above, after you have pasted values, do Paste Special and select Formats. All formatting returned as the original sheet.

2018-03-05 10:35:32


The source formatting is completely stripped still looking for a solution that takes away the formulas but leaves the formatting.

2017-10-07 07:04:02


I am using below code to save date from one sheet to another sheet and it works fine. All i want is i want to copy only VALUES instead of formulas to other sheet. Any suggestion?
Sub Save()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("Invoice").Range("A1:J49")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("Saved Copies of Invoices")
Lr = DestSheet.Cells(Rows.Count, "A").End(xlUp).Row

'With the information from the LastRow function we can
'create a destination cell and copy/paste the source range
Set DestRange = DestSheet.Range("A" & Lr + 1)
SourceRange.Copy DestRange

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

pls help

2017-09-13 11:33:05


I have Excel 2016 I have done the move copy past values and all the formatting goes away. I have tried copying to clipboard but it gives an error message that states "We can't make this change for the selected cells because it will affect a PivotTable. I am trying to replace the pivot table in the new workbook so as to remove the link to the original file. I want a version that I can email out without the links. Can anyone assist me with this issue?

2017-07-10 10:39:06

Dennis Costello

In response to the concerns raised by both Bob and Steve Clark, a different approach may be in order. While I use the "Create a copy <of this tab into a New Book>" facility all the time, in this case I would instead follow these steps:

Create a new workbook (Ctrl-N, for instance, will create a new workbook Book1.xls in my version of Excel). If you need it to be .xlsx - notably because you anticipate having more than 256 columns and/or 65,526 rows - create a new Book1.xlsx.

In the source workbook and worksheet, use Ctrl-A to select All the data. Ctrl-C copies it to the clipboard.

Shift to the new, destination workbook and worksheet, position at cell A1. Paste-Special-Values, either via the mechanism that Allen specified, or by Alt-E-S-V (I do this a lot, so I've memorized the Paste-Special shortcuts).

What you'll be missing in this approach is the formatting that was present in the source range, including row heights and column widths, and Defined Names, all of which Allen's approach would have implicitly copied over. If you need the formatting you can do a second Paste-Special - this time with Formatting (Alt-E-S-T - it's not Alt-E-S-F because that copies over the formulae, which of course you don't want). If you need to copy over the Column Widths, that's yet another Paste Special (Alt-E-S-W). I'm not aware that there's a way to carry over the row heights - oddly that isn't a Paste Special option, at least not in the versions of Excel I've used. But Autofit Row Height (Alt-O-R-A) will set all the rows to the minimum height to fit their contents properly - leaving empty rows unchanged.

Clearly if you need all the formatting, Allen's approach is better than mine - but if like Steve the target worksheet doesn't have the data you need, then my approach will win out.

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


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


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

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.