Duplicate your workbook in seconds
Want a new version of the current workbook? Here’s how to create and save a new copy in an instant.
Make a new copy of the current workbook
How to quickly create and save a new version of the current workbook.
| Download the PDF Quicksheet for this tip, and keep it at your fingertips for future reference! |
Keyboard shortcut to duplicate the current workbook
The “Save As…” command in the File menu will create a new copy of the current workbook, after asking you for a location to save the new file.
You can use this keyboard shortcut to access the “Save As…” command and save a copy of the current workbook:
| Action | Shortcut |
|---|---|
| Save a new copy of the current workbook | F12 |
On some computers, including many portable notebook computers, you need to use an additional modifier key (often labelled Fn or Function) to access the the function keys, like F12.
On these computers, this shortcut would become Fn + F12.
When using some file storage services such as OneDrive, this command is called “Save a Copy” instead of “Save As”.
The “Save As…” dialog
When you choose “Save As…” from the File menu or press the F12 shortcut, a dialog will appear asking you where to save the new file and what to name it.
Here you can also set other options such as the file format, or add password security.
Don’t worry if your dialog looks different; there are many variants. Your software version, shared storage access and other settings influence the look of this dialog, but it still serves the same purpose.
Trivia: did you know?
The three dots at the end of “Save As…” are used in many menu-driven interfaces to indicate that a command will not immediately do something or modify your files.
Instead, it needs to first ask you for further input information (in this case, where to save the new file and what to call it). You will also get an opportunity to cancel without doing anything.
In recent versions of Excel, these three dots are no longer present when the save options dialog is integrated into the flyout menu modal. Once you click “Save” in the save options, there is no need for an additional dialog step.
Default file path for new workbook
After you press F12, the “Save As…” dialog asks where you would like to save the new workbook.
The save location defaults to the location of the current workbook. If you don’t change anything, that’s where the new one will save too.
In some cases it can default to a standard documents location instead, such as:
- If the path to the current workbook cannot be found (for example, if the network connection was interrupted)
- If you are on a corporate network that has custom rules that default all new files to a cloud storage location
File formats for new workbook
You can choose the format for the new workbook. This is useful if you are creating a new copy for the purpose of saving it in a different format, such as text or comma-separated values.
There are 29 available formats*, although in some installations there may be more or less than this available in the dialog. This depends on the installation version and localization, as well as any enabled extensions or add-ins.
* These combine out to a total of 56 different variants once subtle variations (such as in text encodings) are taken into account. You can access these more specific variants programatically using VBA, if needed.
You will probably not need to use very many of the available formats.
List of available file formats
Available file formats include:
| Extension | Format |
|---|---|
| .xlsx | The default format for workbooks. If you are unsure which format to use, choose this one. |
| .xls | The default format for normal workbooks in Excel 2003 and prior versions. This format is now used for backward compatibility with old program versions. It does not contain all the features available to .xlsx workbooks. |
| .xltx | Template format. Workbooks saved as a template file create a new, unsaved workbook when opened. The template file remains unchanged and the new workbook must be saved independently. |
| .xlt | Template format for Excel 2003 and prior versions. This format is now used for backward compatibility with old program versions. It does not contain all the features available to .xlsx workbooks. |
| .xlsm | Macro-enabled workbook. If you need to record macros or save VBA code in your workbook, use .xlsm where you would otherwise use .xlsx. |
| .xlsb | Binary workbook. These files use binary format to store their content, instead of OpenXML format like .xlsx files. Binary workbooks can contain the same information as .xlsx or .xlsm formats can (including macros), with no loss of information. They are typically smaller and faster then their .xlsx or .xlsm equivalent formats. However, binary workbooks cannot be used with Power Query or with some of the available third-party software programs that can open .xlsx workbooks. |
| .csv | Comma-Separated Values file. This format is a text file that has: – each row on a new line; and – for each row, a comma in between cells to indicate a new column. Only the active worksheet is saved in a CSV file; other worksheets are omitted. |
| .txt (tab-delimited) | Text file. This format is a text file that has: – each row on a new line; and – for each row, a tab character in between cells to indicate a new column. Only the active worksheet is saved in a TXT file; other worksheets are omitted. |
What if the workbook has never been saved?
If the original workbook is a new workbook that has not yet been saved, then the “Save As…” function chooses where to save it and what to name it.
A duplicate copy is not created unless the workbook has been saved previously at least once.
What happens to the current workbook?
This now depends on whether you are using Office 365 with AutoSave activated.
With Autosave off, any changes to the original workbook since it was last saved are discarded in the original, and kept in the new copy.
Therefore the new copy is current, while the original is unchanged from when it was last saved.
This feature can be used tactically when you open a saved workbook and begin making changes. You can be confident that you don’t need to immediately choose between saving your changes and leaving the original unmodified. This is because duplicating the workbook after making changes will do both!
With Autosave on, any changes to the original workbook are retained due to the AutoSave function, and already saved before you go to duplicate the workbook.
Therefore both the original and the new copy are current.
This means that if you may need to retain the original unchanged, you should open it and then create a duplicate before making any changes, if working with AutoSave set to on.
Effect on links from other workbooks
When you duplicate a workbook, links and references in other workbooks may change from pointing to the original, to pointing to the new version.
Depending on your intended use, you might want to choose whether links in other workbooks are updated or not:
- If another workbook is open in the same instance of Excel when you duplicate the current workbook, then links in that other workbook will be updated to use the new copy instead.
- If another workbook is not open in the same instance of Excel when you duplicate the current workbook, then links in that other workbook will not be updated and will still point to the original.
For example
In this example we will consider a single link to the current workbook, which will then be duplicated to create a new copy.
Background
Suppose you have two workbooks:
- Book1, which you will duplicate; and
- Book2, which contains a formula linking to Book1 like this:
=[Book1]Sheet1!A1
Then suppose you press F12 while Book1 is active, and save the new workbook as Book3.
Effect
If both Book1 and Book2 are open when you press F12, then the formula in Book2 will automatically be updated to:
=[Book3]Sheet1!A1
But if Book2 is not open when you press F12 with Book1 active, then the formula in Book2 will remain unchanged as:
=[Book1]Sheet1!A1
When you next open Book2, it will look for cell A1 in Sheet1 in the original Book1, not the duplicate copy Book3. Any subsequent changes to cell A1 in Book3 will not flow through to Book2.
Putting this together
Therefore, if you want dependent workbooks to update and follow the new duplicated workbook copy, you should open those dependent workbooks before creating the duplicate.
On the other hand, if you want dependent workbooks to remain unchanged and continue to follow the original workbook instead of the copy, you should ensure those dependent workbooks are closed when creating the duplicate.
Duplicate becomes active workbook
When you duplicate an existing workbook using “Save As…” or F12, the current workbook is the new duplicate and the original is no longer open.
Any further changes you make will be reflected in the new duplicate file, not the original.
Terminology confusion with “Save A Copy”
In many software application programs, this is the primary difference that distinguishes the “Save As…” command from “Save a Copy…”:
- “Save As…” duplicates the file to a specified location and filename, and continues working on the new file; and
- “Save A Copy…” duplicates the file to a specified location and filename, but does not open the new file and instead continues working on the original.
However, in Excel:
- There is no equivalent function to “Save A Copy…” that works in the usual way; and
- With AutoSave set to on and OneDrive enabled, the “Save As…” command is renamed to “Save A Copy”. This is just an adopted name for “Save As…” in that situation, it is not equivalent to the common software meaning of “Save A Copy…” described above.
You might be familiar with using “Save A Copy…” in other software programs (such as AutoCAD, Photoshop etc.). It is worth remembering that Excel does not have an equivalent feature, and if you see “Save A Copy” in Excel it does not mean the same thing.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
View our plans to see what else you are missing, or join for free on our Standard plan.