Move or copy worksheets or worksheet data
You can move or copy a worksheet in the same workbook to organize your workbook exactly how you want.
You can use the Move or Copy Sheet command to move or copy entire worksheets (also known as sheets), to other locations in the same or a different workbook. You can use the Cut and Copy commands to move or copy a portion of the data to other worksheets or workbooks.
Move a worksheet within a workbook
-
Select the worksheet tab, and drag it to where you want it.
Caution: When you move a sheet to another workbook, check any formulas or charts that refer to data on the sheet because moving the sheet might cause errors or produce unintended results in your data. Similarly, if you move a sheet that is referred to by 3-D references, the calculation might include or leave out data on the sheet.
Copy a worksheet in the same workbook
-
Press CTRL and drag the worksheet tab to the tab location you want.
OR
-
Right click on the worksheet tab and select Move or Copy.
-
Select the Create a copy checkbox.
-
Under Before sheet, select where you want to place the copy.
-
Select OK.
When you need to reorganize your data, you can easily change the order of sheets in a workbook, or move or copy a sheet to another workbook. But be aware that calculations or charts that are based on sheet data may become inaccurate if you move the sheet.
Move a sheet
-
Drag the sheet tab to the location that you want along the row of sheet tabs.
Copy a sheet
-
Hold down OPTION.
-
Drag the sheet tab to the location that you want the copied sheet to appear along the row of sheet tabs.
Important: Release the mouse button before you release the OPTION key.
Move a sheet to another workbook
Caution: When you move a sheet to another workbook, check any formulas or charts that refer to data on the sheet because moving the sheet might cause errors or produce unintended results in your data. Similarly, if you move a sheet that is referred to by 3-D references, the calculation might include or leave out data on the sheet.
-
Open the workbook that you want to move the sheet to.
-
On the Window menu, click the workbook that contains the sheet that you want to move.
Click the sheet that you want to copy to the other workbook.
-
On the Edit menu, click Sheet > Move or Copy Sheet.
-
On the To book menu, click the workbook that you want to move the sheet to.
Tip: To create a new workbook that contains the moved sheet, click new book.
-
In the Before sheet box, click the sheet that you want to insert the moved sheet before, or click move to end.
-
Click OK.
Copy a sheet to another workbook
-
Open the workbook that you want to copy the sheet to.
-
On the Window menu, click the workbook that contains the sheet that you want to copy.
Click the sheet that you want to copy.
-
On the Edit menu, click Sheet > Move or Copy Sheet.
-
On the To book menu, click the workbook that you want to copy the sheet to.
Tip: To create a new workbook that contains the moved sheet, click new book.
-
In the Before sheet box, click the sheet that you want to insert the copied sheet before, or click move to end.
-
Select the Create a copy check box.
-
Click OK.
Change the color of a sheet tab
Coloring the sheet tabs can help you keep track of your information in a large workbook.
-
Hold down CONTROL and click the sheet tab.
-
Click Tab Color, and then click the color that you want to use.
In Excel for the web, you can duplicate (or copy) worksheets within the current workbook. Simply right-click the tab name at the bottom of the sheet and click Duplicate.
Note: You may receive an error when you are trying to duplicate a worksheet that contains a chart, picture, or shape. If you get an error, you can duplicate the sheet manually.
If you have elements in your worksheet that interfere with duplicating it, you can still create a copy of the worksheet manually by copying all the data and pasting it into a new sheet. Here’s how:
-
Select all the data in the worksheet.
Keyboard shortcut: Press CTRL+Spacebar, on the keyboard, and then press Shift+Spacebar.
-
Copy all the data on the sheet by pressing CTRL+C.
-
Click the plus sign to add a new blank worksheet.
-
Click the first cell in the new sheet and press CTRL+V to paste the data.
Note: You will lose any conditional formatting applied to the cells when you paste them into the new worksheet.
In Excel for the web, the option to move or copy a sheet isn’t available when you right-click a sheet tab, as it is in the Excel desktop application. In Excel for the web, you can achieve the same result by copying all of the data in the worksheet and pasting it into a blank worksheet in the other workbook.
Follow these steps to move or copy a worksheet to another workbook in Excel for the web:
-
Select all the data in the worksheet.
Keyboard shortcut: Press CTRL+Spacebar and then press Shift+Spacebar.
-
Copy all the data on the sheet by pressing CTRL+C.
-
Open the workbook in which you want to paste the data, then click the + in the status bar to add a new blank worksheet.
-
Click the first cell in the new worksheet, then press CTRL+V to paste the data into that worksheet.
Note: You will lose any conditional formatting applied to the cells when you paste them into the other workbook.
Need more help?
You can always ask an expert in the Excel Tech Community or get support in the Answers community.
Need more help?
How to Copy and Paste in Excel – Step-By-Step (2023)
Copy/pasting is something we have all known for ages now. But there’s so much more to the dynamic copy-paste tool of Excel than simple copying/pasting of values.
And the guide below will show you how resourceful the copy-and-paste tool of Excel can be. So let’s dive right in👇
Hold on! Download our sample workbook here to tag along with the guide.
How to copy and paste into Excel
Unlike any other spreadsheet program, Excel offers a huge variety of options for copying/pasting data.
You can paste anything – formulas, formatting, values, transposed values, and whatnot🖌
And the best part is that you can access a single option from multiple places, offering extra ease of use. So how do you copy and paste values in Excel? Let’s see below
Generally, there are three 3️⃣ ways in which you can copy/paste your data once you select a cell.
1. The clipboard group
The Clipboard section contains all the functions you need to copy and paste values in Excel. It sits in the Home tab of the ribbon.
You can use the Scissors option to cut data and the Two Sheets option to copy the data✂
The Clipboard icon is the paste button that holds all the copied data. The Paint Brush icon below is known as the Format Painter, which lets you copy the formatting🖌
And the options don’t just end here – Click on the arrow in the bottom right corner to view more copy/paste options.
2. The right-click menu
You can access the context menu by right-clicking the cell you want to copy. The dropdown list will show you a bunch of options.
Select Copy to make a copy of the selected cell in the clipboard. Once you copy a cell, a continuously moving border will enclose it.
Pro Tip!
You can also use CTRL + C to copy the data. It is the most common keyboard shortcut used in Excel and is very efficient.
Simply select the cell and press CTRL + C.
Then, select the destined cell and press CTRL + V to paste the copied contents into it 🥂
After you’ve copied the cell, navigate to the destination cell and paste it.
To paste the cell contents, right-click on the destination cell. From the context menu, select the option “Paste”📃
3. The CTRL button
This method is quite similar to using CTRL + C, but not many people know it🤔
- Select the cell.
- Press the CTRL key.
- Hover over the cell until the plus sign appears.
- Hold and drag the cell to a new location.
- You get an exact copy of your original cell in the new location.
How to copy formulas only in Excel
So now we know the basics of copy-pasting in Excel.
But do you know how to copy and paste only formulas in Excel? We do it using a trick.
Let’s see an example below.
The data set we use below shows if the given condition is true or false.
The function running behind these boolean values is the AND function. You can access it from the Formulas Tab 💻
Now let’s say we want to add another row at the bottom and copy the formula above it.
An easy way is to:
- Copy the formula above by selecting any cell that contains the formula and press CTRL + C.
- Right-click the cell where you want to paste the formula. A dropdown list will appear with the paste section like this ⏬
- Click on the Paste Special commands option.
- From the Paste Special menu, select the Formulas and Number formatting option (hovering over the icons shows their names).
The formula will be pasted into the new cell, and the cell references will adapt accordingly.
Similarly, if you want to copy the formula to multiple cells, you can do it using the Paste Special dialog box 💭
Launch the Paste Special Dialog box using the shortcut keys Alt + E + S.
Kasper Langmann, Microsoft Office Specialist
Simply select the Paste option you want to apply on the cell while pasting data. And since we are dealing with formulas, we will select the option “Formulas”.
How to make a copy of an Excel sheet
Making a copy of an Excel sheet may seem difficult with no options visible on the face of the worksheet. But believe us, it is just a click away.
Say, we want to make a copy of Sheet 1🧾
There are two ways to do this. First, use the right-click menu, and second, use the CTRL key.
The right-click context menu:
- Select the sheet you want to copy.
- Right-click the sheet and select the Move or Copy option.
- You will see a pop-up asking for the location and whether you want to create a copy.
- Check the option to Create a Copy.
What happens if you don’t check the option to create a copy🤔
Excel will remove the sheet from the present workbook. And move it to the destination workbook.
Kasper Langmann, Microsoft Office Specialist- Choose the pasting location from the To Book option.
- Click Ok.
- The subject worksheet appears in the chosen location💪
Using the CTRL key:
To copy a sheet using the Control key, follow the steps below:
- Select the sheet.
- Press the CTRL key.
- Drag the sheet to a new location to make its copy.
We have created a copy of Sheet 1 in the same book.
- A new file, Sheet 1 (2), appears on the Sheet tab.
Copy values not formula
It’s time we see how to copy only the values in Excel and not the underlying formulas.
From the dataset below, let’s copy the cell values only 🔢
To copy cell values, follow the steps below:
- Select the cell or the range of cells whose value is to be copied.
- Press Ctrl + C to copy the cell values.
- Go to the blank cells where you want to paste the selected range.
- Right-click the first cell and open the Paste Special dialog box.
- From the Paste Special options, select the Values option.
This tells Excel to paste the values of the copied cells only 🌟
- Click Okay. And there you go!
Values from the copied range appear in all the cells selected.
Note that Excel has pasted the exact values only. You can select the cell and view the formula bar to see that the values have no formulas to them.
Had you pasted them simply, Excel would have copied and adapted the formula of the copied cells for the destination cells as follows 😵
Shortcut to paste values
Oh, and there’s a very efficient shortcut to paste values in Excel too 💪
- Select the values to be copied.
- Press CTRL + C to copy them.
- Go to the destination cells to paste values. Select the first cell of the destination cell range.
- Press CTRL + Alt + V.
- Press V.
- Select Ok.
- You’d have the cell values pasted in Excel without any cursor movement 🖱
How to copy formatting
We have so far seen how to copy and paste formulas and values. Let’s now have a look at the copy-pasting of formatting.
Hint: It’s done the same way as formulas and values are copied/pasted✌
We are using the same data set for this example. And we want to paste the existing formatting to the new cells below.
To do so:
- Select the cells with the source formatting (the formatting that you want to copy) to copy them.
- Once copied, select the cell (or cells) where you want to paste the cell formatting🖱
- You can use the context menu to open the Paste Special dialog box and choose Formatting. Or press CTRL + Alt + V and then T to paste the formatting only.
The results look like this:
Note how Excel has pasted the format (including the font style and the font size) to the destined cells.
There is yet another way to copy cell formatting in Microsoft Excel – by using the Format Painter. We bet you didn’t see that coming😎
All you need to do is select the cells containing the source formatting. And click the Paintbrush icon on the ribbon to activate the Format Painter
With the format painter activated, select the cells where you want to paste the formatting.
And tada! The new cells are formatted like the source formatting.
Pro Tip!
If you want to paste the formatting to a single cell or a range of adjacent cells only, click on the format painter once. In this case, the format painter will deactivate after painting the format once.
But, if you want to apply the source formatting to multiple non-adjacent cells, double-press the Format Painter icon. Now the format painter will stay active until you manually deactivate it 🎨
That’s it – Now what?
In this article, we learned how to copy and paste values and formulas in Excel. We also saw how we could paste cell formatting to a range of cells in a few easy steps.
And even though this article covers most of the aspects of the copy-paste tool in Excel, there’s still so much to learn.
Like the three most important functions of Excel. The VLOOKUP, IF, and SUMIF functions.
To learn these functions (and more!), enroll in my 30-minute free email course today.
Kasper Langmann2023-01-19T12:05:51+00:00
Page load link
MS Excel provides copy paste option in different ways. The simplest method of copy paste is as below.
Copy Paste
-
To copy and paste, just select the cells you want to copy. Choose copy option after right click or press Control + C.
-
Select the cell where you need to paste this copied content. Right click and select paste option or press Control + V.
In this case, MS Excel will copy everything such as values, formulas, Formats, Comments and validation. MS Excel will overwrite the content with paste. If you want to undo this, press Control + Z from the keyboard.
Copy Paste using Office Clipboard
When you copy data in MS Excel, it puts the copied content in Windows and Office Clipboard. You can view the clipboard content by Home → Clipboard. View the clipboard content. Select the cell where you need to paste. Click on paste, to paste the content.
Copy Paste in Special way
You may not want to copy everything in some cases. For example, you want to copy only Values or you want to copy only the formatting of cells. Select the paste special option as shown below.
Below are the various options available in paste special.
-
All − Pastes the cell’s contents, formats, and data validation from the Windows Clipboard.
-
Formulas − Pastes formulas, but not formatting.
-
Values − Pastes only values not the formulas.
-
Formats − Pastes only the formatting of the source range.
-
Comments − Pastes the comments with the respective cells.
-
Validation − Pastes validation applied in the cells.
-
All using source theme − Pastes formulas, and all formatting.
-
All except borders − Pastes everything except borders that appear in the source range.
-
Column Width − Pastes formulas, and also duplicates the column width of the copied cells.
-
Formulas & Number Formats − Pastes formulas and number formatting only.
-
Values & Number Formats − Pastes the results of formulas, plus the number.
-
Merge Conditional Formatting − This icon is displayed only when the copied cells contain conditional formatting. When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range.
-
Transpose − Changes the orientation of the copied range. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed.
In this tutorial, you will learn how to copy and paste without changing the format in Excel and Google Sheets.
Copy & Paste Formula Without Changing Format in Excel
When you use CTRL + V to paste, everything from the source cell or range (data, formatting, validations, comments) will be pasted to the selected destination. Instead, you can paste only certain cell properties with Paste Special.
For example, you have a data range with a formula in each cell (in this example C2:C7 ) and you want to copy that formula in another range (E2:E7) without formatting. To do that, follow the next steps.
1. First, select the data you want to copy (C2:C7), then right-click it, and from the drop-down menu choose Copy (or use CTRL + C shortcut).
2. Then, select the range to paste without formatting, right-click on it, and in the drop-down menu click on Paste Special.
3. The Paste Special window will appear. Under the Paste section choose Formulas and when done, click the OK button.
As a result, the data range is filled with formulas without formatting.
Note: You can also use VBA code for Paste special option.
Copy & Paste Values Without Changing Format in Excel
If you have a certain data range with values in each cell (in this example A2:A7) and you want to copy those values in another range (E2:E7) without formatting. You can easily do that, just follow the next steps.
1. First, select the range (or cell) with values you want to copy, right-click it, and from the drop-down menu choose Copy.
2. After that, select the destination where you want to paste it, right-click it, and from the drop-down menu choose Paste Special.
3. In the Paste Special window, under the Paste section, choose Values and then click the OK button.
As a result, the data range will be pasted without formatting.
Copy and Paste Without Changing Format in Google Sheets
Let’s say that you have a data range filled with a formula (in this example C2:C7) and you want to copy it into another range without formatting. To do that follow the next steps.
1. Select the data you want to copy, right-click it, and click on Copy (or use CTRL + C shortcut).
2. Now, (1) select the destination where you want to paste the data, right-click it, (2) in the drop-down menu click on the Paste special, and (3) choose Paste formula only.
Note: Use Paste formula only because in this example, you are copy-pasting a range filled with formulas. If you need to copy & paste the range filled with values then use Paste values only.
As a result of the previous steps, the data range will be pasted without formatting.
How to Copy and Paste Excel Sheet
Here i will show you how to copy and paste excel sheet data and also copy compete sheet and paste on other sheet.
To perform this task first open your excel sheet, See left hand side and click on arrow which is display in below images (for select complete sheet data).
Steps to Copy and Paste Excel Sheet
- First Select all sheet data (See in below Image)
- Copy selected data using Ctrl+C or Right click and copy.
- Open new Sheet
- Again select complete blank sheet (see in below Image)
- Paste Copied data in i new sheet using Ctrl+V or right click and paste.
Steps to Copy and Paste Excel Sheet Without Lose any Setting
When you direct copy and paste any excel sheet, excel sheet lose all setting like print area, page margin etc. Use following way to prevent lose excel sheet setting
- First See bottom on Excel Sheet (See in below Image)
- Right Click on Sheet which you want to make copy
- Pop-Up menu will appear here with Move or Copy Option
- Again one Pop-Up menu will appear
- Mark on Create a Copy
- Choose where you want to paste your sheet
