How to Copy Excel Sheet to Another Workbook: Preserve Format and Formulas in Seconds

I spent years manually recreating spreadsheets until I found this method. Copying an entire Excel sheet to another workbook while keeping all formatting and formulas intact takes under 30 seconds when you know the right approach.

The secret is using Excel’s built-in copy function with a specific paste option. No macros, no add-ins, just a few clicks. I’ve used this exact method to migrate dozens of complex sheets without breaking a single formula or losing any conditional formatting.

You’ll get an identical copy with all cell references, data validation, and styles preserved. The destination workbook doesn’t even need to be open when you start the process.

Works in Excel 2016 through 365 on Windows and Mac. Here’s exactly how to do it.

What Youll Need

Step-by-Step Instructions for Copying Excel Sheets Between Workbooks

This method preserves every detail, from conditional formatting to complex formulas.

Launch Excel and open the workbook containing the sheet you want to copy. I always save both files locally first—cloud synced versions can cause unexpected conflicts during the transfer process.

Navigate to the target sheet and verify all formulas return expected values. Look for the green triangle in the top-left corner of cells with formulas—this confirms they’re active and calculating properly.

If your sheet contains named ranges, check they’re defined correctly under the Formulas tab. These will transfer with the sheet, but broken references can cause issues later.

Right-click the sheet tab at the bottom of the screen and select Move or Copy. This opens a dialog box that’s the key to preserving everything exactly as-is.

In the To book dropdown, select the destination workbook. Below that, you’ll see Before sheet—choose where you want the copied sheet to appear in the new workbook.

This is critical: check the Create a copy box at the bottom. Without this checked, Excel will move the sheet instead of copying it, which isn’t what you want.

After the copy completes, switch to the destination workbook. Click through several cells with formulas to confirm they’re calculating correctly. Pay special attention to cells that reference other sheets—they should now point to the same relative locations in the new workbook.

Check that all formatting transferred properly. Test conditional formatting rules by changing cell values to see if the formatting updates as expected. The Home tab’s Styles section can help verify named styles came across.

If your original sheet referenced data in other workbooks, these become external references in the copied version. Go to Data > Edit Links to see all external connections.

Update any that point to the wrong location. For absolute references that should now be relative, you’ll need to manually edit these in the formula bar.

Finally, save both workbooks. I recommend using Save As for the destination to create a backup before making any adjustments to the copied sheet.

Tips and Common Mistakes

Here’s how to avoid the most common pitfalls when copying sheets between workbooks.

Step 1 Preparation: Always save both files locally before starting. I learned this the hard way after losing changes when cloud sync conflicts corrupted my transfer. The green triangle in formula cells confirms they’re active, so double-check these in your source sheet to avoid broken calculations later.

The Copy Command Secret: In Step 2, that “Create a copy” checkbox is everything. Without it, Excel moves the sheet instead of copying it. I’ve accidentally deleted original data this way—now I triple-check that box before clicking OK.

Verification Hack: After copying, test conditional formatting by changing cell values in the new workbook. If the formatting doesn’t update as expected, you’ll know something went wrong during transfer. The Styles section under Home tab helps verify named styles came across properly.

External Reference Fix: In Step 4, use Data > Edit Links to catch external references that might break. I once spent hours debugging formulas because I missed updating these connections to their new locations.

Frequently Asked Questions

Copying an Excel sheet to another workbook while keeping the format and formulas intact is simpler than you think! 🎯 With the right method—whether using Copy-Paste Special or Move or Copy—you can ensure everything stays perfect. Try it out today and streamline your workflow with confidence!