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.

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.

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 below:

1. Take your mouse cursor on the sheet tab.
2. Give a right-click on your current sheet.
   I am using a sheet called Sheet1.
3. Click on the Move or Copy command.
   To find the Move or Copy command:
4. Click on the Home tab.
5. Under the Cells group, click on the Format drop-down list.
6. Hit on the Move or Copy Sheet command.
   Both ways will lead you to the Move or Copy dialog box.
7. 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.
8. In the Before Sheet section, pick a selection of a place where you want your sheet to paste.
9. Give a tick mark on the Create a Copy checkbox.
10. Hit on the OK button.

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

Dragging the Sheet

Now this one is the quickest to follow. However, this method is only applicable for copying a sheet into the same worksheet. So, go through these steps below:

1. Take your cursor on the current sheet.
2. Press the CTRL button on the mouse and drag it to the right side of the sheet tab.

So, here are the copied a sheet into the same worksheet in Excel

• 3 Ways to Copy a Formula in Excel without Changing Cell References
• 4 Ways to Fill Formula Down to Specific Row in Excel

Using Shortcut Keys

This method is useful to copy an Excel sheet to another worksheet having the same formulas and format. Steps to use shortcut keys in Excel:

1. Click on the triangle mark on the left top corner of your datasheet.
   This will select the full datasheet.
   
2. Copy the datasheet (or press CTRL+C).
3. Move to a new sheet or on a different worksheet.
4. Select the top-left cell of the sheet (cell A1).
5. Paste the sheet (or press CTRL+V).

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

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.

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, go through the steps below:

1. Open your workbook first.  
2. Press the CTRL key and select the sheet tabs.
   I selected Sheet1 and Sheet2.
   
   
3. Click on the Move or Copy command.
   The Move or Copy dialog box will show up on the screen.
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.
   
   
5. Click OK.

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

Using Shortcut Key

You can make a copy of the sheets in the same workbook using the shortcut keys and dragging. To do that, follow the steps below with the shortcut key in Excel:

1. Press CTRL + SHIFT and select the sheet tabs.
   
2. Drag the sheet tabs and drop them before the ‘+’ symbol.

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)

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 these steps in Excel:  

1. Select the table.
2. Click on the Table Design tab.
3. From the Tools group, click on the Convert to Range.
   A dialog box will come along asking permission to convert the range.
   
4. Click on the OK button.
5. Now drag the data sheets like the previous methods.

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!

Frequently Asked Questions

What is auto-copy in Excel?

In Excel, “Auto Copy” typically refers to the automatic copying of cell contents to the clipboard when a cell or range is selected. This feature allows users to quickly copy data without explicitly using the CTRL+C keyboard shortcut. When a cell or range is selected, Excel automatically places the selected data into the clipboard, making it ready for pasting.

It’s important to note that this behavior is inherent in Excel, and there isn’t a specific “Auto Copy” feature that can be toggled on or off. Excel automatically manages the clipboard as you interact with cells, making copying and pasting more efficient for users.

How do you copy and paste in Excel with formulas and formatting?

To copy and paste in Excel with formulas and formatting, follow these steps:

1. Select the cell or range of cells you want to copy.
2. Press CTRL+C to copy the data.
3. Go to the cell where you want to paste the data.
4. Right-click on the destination cell.
5. Choose “Paste Special” from the context menu.
6. In the Paste Special dialog box, select Formulas to paste the formulas and Formats to paste the formatting.
7. Click OK to complete the paste.
   Alternatively, after copying the data (CTRL+C), you can use the following keyboard shortcut to open the Paste Special dialog directly: For Formulas and Formatting, ALT + E > S > V, ENTER.

By following these steps, you can copy and paste data in Excel while retaining both formulas and formatting from the source to the destination.

How do you automatically copy formulas?

In Excel, you can automatically copy formulas to adjacent cells using the Fill Handle feature. Here’s how:

1. Click on the cell containing the formula that you want to copy.
2. Move the cursor to the lower-right corner of the selected cell. You’ll notice a small square, known as the Fill Handle.
3. Click and drag the Fill Handle over the range of cells where you want to copy the formula.
4. Release the mouse button to fill the selected cells with the copied formula.

Excel will automatically adjust the references in the formula to match the relative positions of the cells in the new range. Using the Fill Handle is a quick and efficient way to automatically copy formulas to adjacent cells in Excel.