How to Copy Data From One Excel Sheet to Another

Copying data from one Excel sheet to another is a core task that we need to use often.

Being able to move data among sheets is a crucial skill whether you are consolidating data, preparing reports or adding a new perspective to the sheet 📗

Luckily, Excel offers a variety of methods you can use to move and copy your Excel sheets and even workbooks. From dragging to using VBA, Excel offers all.

In this guide, we will learn each of these methods in detail. Download our sample workbook here to practice copying data from one sheet to another in real-time 😃

Table of Contents

How to copy information from one sheet to another in Excel

There are a couple of ways you can use to copy data from one sheet to another. Some are faster while others are more accurate. We will see all of these below 🔽

Copy and Paste

Say, we have the following sample data. We want to copy the dataset in this worksheet to another without any changes or mistakes.

To do that.

Step 1) Select the data set to be copied by pressing CTRL + A.

Step 2) Press CTRL + C to copy the data 🧾

Step 3) Create a new sheet.

Step 4) Select the position where you want to paste the data.

Step 5) Press Enter or CTRL + V to paste the data.

And it’s done! 😃

The data will be pasted to the new location just like that.

Worksheet Reference

Another way you can use to copy some part of your Excel worksheet to another is to use worksheet reference 🧐

How this works is that you create a formula in the new sheet where you directly reference the values of the other sheet.

This way the values update automatically in the new sheet when the original data is changed and this also removes any risk of human error 😯

We will use the same dataset as earlier.

To copy values using worksheet reference,

Step 1) Select a cell in the new sheet where you want to place the data.

Step 2) Enter the following formula in the cell.

where,

Sheet1 refers to the name of the sheet containing the original data

A1:D10 refers to the dataset

Step 3) Press Enter.

Or you could use another method to get the same result.

Step 1) Select the cell in the new sheet.

Step 2) Insert = in the cell.

Step 3) Click on the previous sheet and select the data range you want to copy.

Step 4) Press Enter.

Tada! Excel will paste the entire selected range into your new sheet for you.

The data set in the new sheet will update as the original data updates.

You can choose whichever of the two methods you like 😃

How to copy information from one workbook to another in Excel

Copying information to another workbook is the same as copying to another sheet. Let’s see how to do it with a workbook reference.

Step 1) Open a new workbook.

Step 2) Select a cell in the new worksheet where you want to place the data.

Step 3) Enter the following formula in the cell.

where,

[Book1] refers to the name of the workbook you want to copy the data from.

Step 4) Press Enter.

The data from the original workbook will appear in the new workbook.

You could also simply use an equality sign, open the source workbook select the dataset you want to copy and press Enter ⬅

How to move or copy a sheet in Excel

If you want to move or copy an entire sheet to another, the process is slightly different but smooth. This is often used when you want duplicates of a sheet as backup or want to experiment with data.

There are three different ways to move or copy an Excel sheet and we will look at all three 🤓

By Dragging

We will use the same sample data as we did earlier. We want to copy all the data in Sheet1 as it is to another.

To do that,

Step 1) Click on the Sheet1 tab to activate it.

Step 2) Press CTRL and hold and drag the sheet where you want to place it.

A copy of the Excel spreadsheet will be made just like that 😎

All your data in Sheet1 has now been duplicated to a Sheet1 (2).

How cool is that? 😉

By Right-Clicking

Another cool method to copy or move data is by right-clicking the Sheet tab. This is the most commonly used method as most Excel users are not aware of the other two methods.

We will use the same sample data as earlier.

To copy a sheet by right-clicking,

Step 1) Select the Sheet tab to activate it.

Step 2) Right-click the tab to open a menu.

Step 3) Select the location where you want to create a copy of the sheet.

Step 4) Tick the Make a copy checkbox at the bottom.

Step 5) Press Ok.

And it’s done! Excel will make a copy of the selected sheet to the desired location as:

You can now rename the file as you like 😃

Pro tip

If you simply want to move the file, leave the Make a copy checkbox unchecked. This will simply move the sheet to the chosen location.

Using a Ribbon

The ribbon is truly the dark knight of this software – it has everything you can ask for in a spreadsheet software all in one place 😅

We copy a sheet using ribbon via the Format feature of Excel. We will use the same dataset as earlier.

To copy sheet using ribbon,

Step 1) Select a cell in the sheet you want to duplicate.

Step 2) Go to the Home tab and select Format under the Cells section.

Step 3) From the drop-down list that appears, select Move or Copy Sheet.

The Move or Copy dialog box appears and you follow the process mentioned above. The final result of copying the sheet should look something like this:

Easy, no? 😋

How to move or copy a sheet to another workbook in Excel

Copying an Excel sheet to another workbook is a straightforward process. You can use all three methods described above. We will use the same data set as earlier.

By Dragging

To copy the sheet by dragging,

Step 1) Open both workbooks.

Step 2) Minimize source workbook.

Step 3) Hold and drag the sheet to be copied from the source workbook.

Step 4) Drop in the new workbook at the desired location.

Simple, no? 🤠

By Right-Clicking

To copy the sheet by right-clicking,

Step 1) Open both workbooks.

Step 2) In the source workbook, right-click the sheet tab you want to copy.

Step 3) Select Move or Copy from the dropdown.

Step 4) Under the To book bar, select the name of the new workbook.

Step 5) Select the position to be placed at.

Step 6) Check the Make a copy option.

Step 7) Press Ok.

The selected sheet will be duplicated in the new worksheet.

Try it now! 🤓

By Ribbon

Copying sheet by the ribbon is the same as by right-clicking seen above. Select the Insert button from the Home tab and the remaining process is the same.

Isn’t it easy? 😎

How to copy multiple sheets at once in Excel

Copying each sheet can get tiring if you have plenty of sheets to be duplicated. Fortunately, Excel offers an easy way to copy multiple sheets in one go ✅

The process is pretty much the same as we saw earlier with only a slight difference. We will use the same sample data as used earlier.

Adjacent Sheets

If the sheets in your Excel workbook are adjacent, follow the procedure below:

Step 1) Open the workbook.

Step 2) Select the first sheet tab.

Step 3) Press and hold the Shift key.

Step 4) Select the last sheet tab.

The selected tabs will look something like this:

Step 5) Now, to copy the sheets you can use any of the three above explained methods.

Step 6) To drag, select any one of the selected sheets.

Step 7) Press CTRL and drag to the desired location.

Like this:

The other two methods are the same. Right-click and select Move or Copy or select the same from the Insert button under the Cells section on the Home tab.

Pretty cool, right? 😵

Non-Adjacent Sheets

The copying mechanism for non-adjacent sheets is pretty much the same.

To duplicate non-adjacent cells,

Step 1) Open the workbook.

Step 2) Select the first sheet tab.

Step 3) Press and hold the CTRL key.

Step 4) Select the sheets you want to duplicate.

The selected tabs will look something like this:

Step 5) Now, to copy the sheets you can use any of the three methods.

Step 6) To drag, select any selected sheet.

Step 7) Press CTRL and drag to the desired location.

It will look something like this:

You can use the other two methods too. Select the Move or Copy from the Insert button under the Cells section on the Home tab or right-click the sheet tab and select the same.

Neat, no? ✌

How to copy sheets using VBA?

You can also use VBA to copy different sheets in Excel. Unlike the methods we saw above, this requires you to write a VBA code and run a macro to perform the task.

It can be slightly difficult to get a grip on it, but once you do it becomes very easy 😵

We have the same sample data as earlier.

To duplicate it using VBA,

Step 1) Press Alt + F11 on your keyboard

Step 2) The Visual Basic editor will appear on the screen.

Step 3) Go to the Insert tab and select Module.

Step 4) Paste the following code into the window.

Step 5) Close the editor.

Step 6) Press Alt + F8 to open the Macros windows.

Step 7) Select CopyRange and press Run 🏃‍♀️

The dataset will be copied to the new sheet as:

Conclusion

In this tutorial, we saw how to move and copy one sheet to another in Excel. We saw copying methods like dragging, right-clicking and even using the ribbon 🎀

We also saw how we can do the same with a workbook. You can also copy multiple sheets simultaneously using the above-described methods.

VBA is also a method you can use to copy sheets but it is rather advanced. For beginners, the top three methods are best suited

To learn more about copying sheets and VBA, read our articles below.

Copy Only Visible Cells in Excel: Step-by-Step (2024)

How to Copy Values in Excel (and Not the Formula)

Learn VBA in Excel: These 11+ Tutorials Teach You VBA in 20 Hours

We hope you enjoy reading this article as much as we did crafting it! 🤗