Copying a Worksheet

There are many times I have needed to duplicate a worksheet. Often it is because I have spent quite a bit of time developing a worksheet and I want to use it as a starting point for another. Excel lets you copy worksheets in the following manner:

1. Right-click on the worksheet tab of the worksheet you want to copy. Excel displays a Context menu.
2. Choose Move or Copy Sheet from the Context menu. Word displays the Move or Copy dialog box. (See Figure 1.)



Figure 1. The Move or Copy dialog box.

3. If you want to copy the worksheet to another workbook, select that workbook's name in the To Book pull-down list. (The target workbook must be open within Excel.)
4. In the Before Sheet list, select the worksheet that should come after the worksheet you selected in step 1.
5. Make sure the Create a Copy check box is selected.
6. Click on OK. The worksheets are reordered.

There is a faster way to copy worksheets in the same workbook. All you need to do is hold down the Ctrl key as you drag a worksheet tab to a new position in the workbook. Excel automatically copies the worksheet to the new position, leaving the old sheet intact.

There is also a "gotcha" here that you need to be aware of: Excel can sometimes balk if you try to copy a worksheet from a workbook saved in Excel's native format to a workbook that is saved in the older (pre-2007, XLS) format. This is because Excel can handle, currently, many, many more rows and columns in a worksheet than it could in earlier years. That means that when you try to transfer, Excel needs to let you know that it will truncate some data that may be in areas not supported in the earlier-formatted workbook.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12359) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Copying a Worksheet.