If you ever work with Excel data that is spread across multiple tabs or if you ever have to shuffle data between different spreadsheet files, you’ll probably appreciate this weeks tip on moving and/or copying an entire worksheet tab.
If you don’t already work with multiple tabs, you might want to check out how to use formulas that reference other tabs or files and how to use 3-D formulas (no goofy glasses required).
I’ve seen a number of people who could save a whole lot of copying and pasting by adding this to their bag of tricks. So without further ado, here’s how to copy and move worksheet tabs.
How It Works:
1. Right-click the tab you want to move/copy
2. Select Move or Copy…
3. Then select which Excel file you want to send the sheet to by picking it from the To book: drop-down list [ NOTE: All open Excel files, will be listed here.]
4. By selecting an existing sheet in the Before sheet: box you can control the order of where it appears in it’s new home. (You can always move it later if you need to.)
5. If you just want to copy the Excel sheet instead of moving it, click the check box next to “Create a copy“.
Drag & Drop
If you want to be even more efficient, (and who doesn’t?) you can use the drag & drop method to manage your worksheet tabs. Simply click and drag any tab to a new location or hold the CTRL key while you drag to make a copy instead of moving it. It even works between files. See it in action below.
Beware that existing calculations or charts could get broken if you move a worksheet. Similarly, if a moved or copied worksheet is inserted between sheets that are referred to by a 3-D formula reference, data on that worksheet might be unexpectedly included in the calculation.