How To Effectively Copy Your Excel Data: Tips for Making Full and Partial Copies, Including Formula Replication Options

Say you’ve created an Excel workbook you need to distribute, but you can’t reveal the formulas you used in that workbook . We’ll show you an easy trick that allows you to copy a worksheet to another workbook and quickly remove the formulas, so only the results show.

This method allows you to move or copy worksheets from one workbook to another, but in our example we’re going to copy (not move) a worksheet to a new workbook, so we preserve the formulas in the original workbook. Note that you can also copy (or move) worksheets from one workbook to another, already existing workbook.

To create a workbook where the formulas in the worksheets are removed, open your original Excel workbook and select the tab for a worksheet that contains sensitive formulas. Right-click on that worksheet’s tab and select “Move or Copy” from the popup menu.

In the Move or Copy dialog box, select “(new book)” from the “To book” drop-down list. Select the “Create a copy” check box so there is a check mark in the box. This ensures that the worksheet will be copied to the new workbook and not moved out of the original workbook. Click “OK”.

NOTE: Any currently open workbooks are available to select in the “To book” drop-down list.

A new Excel workbook is created and the copied worksheet is pasted into the workbook, along with the name of the worksheet tab. You can save this workbook with a different name to preserve the original workbook.

Select all the cells on the worksheet you just copied into this workbook by pressing Ctrl+A or by clicking on the square in the upper-left corner of the cells, between the column letters and the row numbers.

Make sure the “Home” tab is active. In the Clipboard section, click the down arrow on the “Paste” button and then click the “Values” button in the “Paste Values” section of the drop-down menu, as shown in the image below.

The worksheet now contains only the results from the formulas in the original worksheet, not the formulas themselves.

Follow this procedure for each worksheet in the original workbook from which you want to remove the formulas. Once you’ve created your new workbook, keep it open and you can select that workbook from the “To book” drop-down list on the Move or Copy dialog box.