Copy Excel Sheet to Another Sheet with Same Format and Formulas

More or less every Excel user often needs to copy formulas from one worksheet to another sheet. But many of you don’t know how to copy a sheet with the same formulas and format. In this article, I will show you 3 cases of how to copy an Excel sheet to another sheet with the same format and same formulas.

Case #1:Copy a Single Excel Sheet to Another Sheet with the Same Format and Formulas

You can apply one of these approaches if you want to copy a single sheet.

Method #1: Using Move or Copy Dialog Box

I am going to start by showing you the use of the Move or Copy dialog box.

Now Follow The Guide 

Step_1: Take your mouse cursor on the sheet tab.

Step_2: Give a right-click on your current sheet.

I am using a sheet called Sheet1.

Step_3: Click on the Move or Copy command.

Another way to find the Move or Copy command:

Step_1: Click on the Home tab.

Step_2: Under the Cells group, click on the Format drop-down list.

Step_3: Hit on the Move or Copy Sheet command.

Both ways will lead you to the Move or Copy dialog box.

Step_4: Below the To Book drop-down list, choose the workbook where you want to create the copy.

I am not going to change the option from the list, because I will make the copy in my current workbook.

Step_5: In the Before Sheet section, pick a selection of a place where you want your sheet to paste.

Step_6: Give a tick mark on the Create a Copy checkbox.

Step_7: Hit on the OK button.

Final Result 

The Sheet1 has a new copy named Sheet1 (2) now.

9 ways to Copy Formula Down in Excel without Dragging

6+ Ways to Copy a Formula in Excel with Changing Cell References

Method #2: Dragging the Sheet

Now this one is the quickest to follow. But this method is only applicable for copying a sheet into the same worksheet.

Now Follow The Guide 

Step_1: Take your cursor on the current sheet.

Step_2: Press the CTRL button on the mouse and drag it to the right side of the sheet tab.

Final Result 

3 Ways to Copy a Formula in Excel without Changing Cell References

4 Ways to Fill Formula Down to Specific Row in Excel

Method #3: Using Shortcut Keys

This method is useful to copy an Excel sheet to another worksheet having the same formulas and format.

Now Follow The Guide 

Step_1: Click on the triangle mark on the left top corner of your datasheet.

This will select the full datasheet.

Step_2: Copy the datasheet (or press CTRL+C).

Step_3: Move to a new sheet or on a different worksheet.

Step_4: Select the top-left cell of the sheet (cell A1).

Step_5: Paste the sheet (or press CTRL+V).

Final Result 

This method is easier to apply than using the Move or Copy dialog box in case of copying the sheet in two different workbooks.

5+ Proven Methods to Copy a Formula to Entire Column in Excel

4+ Ways to Repeat Formula Pattern in Excel

Case #2: Copy Multiple Excel Sheets to Another Sheet with the Same Format and Formulas

The procedures of copying multiple Excel sheets to another sheet are quite alike the techniques of copying a single sheet.

Method #1: Using Move or Copy Dialog Box

Again you can use the Move or Copy dialog box to copy multiple sheets from one workbook to another workbook.

Now Follow The Guide

Step_1: Open your workbook first.  

Step_2: Press the CTRL key and select the sheet tabs.

I selected Sheet1 and Sheet2.

Step_3: Click on the Move or Copy command.

the Move or Copy dialog box will show up on the screen.

Step_4: Under the To Book drop-down list, choose the workbook where you want to create the copy.

From the workbook Copy with Same Format and Formulas.xlsx, I am making the copy of Sheet1 and Sheet2 in WorkBook1.xlsx.

Step_5: Click OK.

Final Result

The formulas and the formats are intact in the other workbook.

Method #2: Using Shortcut Key

You can make a copy of the sheets in the same workbook using the shortcut keys and dragging.

Now Follow The Guide

Step_1: Press CTRL + SHIFT and select the sheet tabs.

Step_2: Drag the sheet tabs and drop them before the ‘+’ symbol.

Final Result 

This way you can copy multiple sheets in the same worksheet keeping the formats and formulas in the exact place.

Pros: It’s a really quick technique. It’s gonna save you a lot of time.

Cons: Sadly, you can not copy multiple sheets if one of the selected sheets has an Excel Table format.

How to Copy a Formula in Excel to Another Sheet (4+ Solutions)

Apply Same Formula to Multiple Cells in Excel (5+ Solutions)

Case #3: Copy an Excel Sheet to Another Sheet Having Tables

To copy the Excel sheets having tables, first, you will need to convert the table range into a normal range.

Now Follow the Guide 

Step_1: Select the table.

Step_2: Click on the Table Design tab.

Step_3: From the Tools group, click on the Convert to Range.

A dialog box will come along asking permission to convert the range.

Step_4: Click on the OK button.

Step_5: Now drag the data sheets like the previous methods.

Final Result 

Following these steps, you can copy the sheets with the same formulas and formats.

Conclusion

I hope you can relate one of the three solutions to what you were looking for in this article. Feel free to reach out to us in the comment section below. Also, let us know how you like the blog. Have a good day!