How to Create a Copy of an Excel Worksheet Without Formulas

00_lead_image_removing_formulas

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.

RELATED: Defining and Creating a Formula

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.

01_selecting_move_or_copy

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.

02_move_or_copy_dialog

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.

03_selecting_all_cells_in_worksheet

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.

04_selecting_paste_values

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

05_cell_with_result

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.

Lori Kaufman is a writer who likes to write geeky how-to articles to help make people's lives easier through the use of technology. She loves watching and reading mysteries and is an avid Doctor Who fan.