Excel Tip: Copy Worksheets between Identical Models (Updated Mar 2023)

From time to time, I come across modeling situations that Excel does not have built-in functionality to address. One particular situation I come across regularly, involves needing to transfer the contents of a worksheet, including all inputs, formatting, and formulas from one copy of a model to another copy of the exact same model without linking the two models.

Updated Mar 2023: Our team has been in the process of creating Spanish versions of our real estate financial models, and in doing so this task has come up multiple times. I thought I’d update this post, both for them as well as for all A.CRE readers who find themselves copying data and formulas from one workbook to another.

In real estate, this is useful because we’re often taking a real estate model template (like those found in our Library of Excel Real Estate Models) and customizing the model for the specific property we’re trying to value. Thus, being able to copy information from one model to another, without the two files linking is an important skill to learn.

This post is a supplement to our Best Practices in Real Estate Financial Modeling. If you haven’t read that guide, I highly recommend you do prior to using any A.CRE model.

Understanding The Problem – Linked Workbooks

If you haven’t seen this problem before, allow me to provide an example of when this is necessary. For instance, you’re working with two Excel files with identical tabs, formulas, named ranges, etc (i.e. the same real estate model template) but with different inputs (i.e. different properties), and you want to copy, say the rent roll tab, from one workbook to the other.

However, when you built out the rent roll tab, you wrote formulas that linked to other tabs within the model to calculate certain inputs (such as gross rent). If you were to simple copy and paste the formulas, or if you were to use Excel’s copy tab feature, the copied formulas would link back to the original file and not to the intended tabs in the new workbook. This is because Excel assumes the workbooks are different, and that the intent is for the formulas to link to the original workbook. The tip I’m about to show you allows you to copy the contents exactly as they appear without creating unwanted links.

How to Copy Formulas Without Creating Links to the Previous Workbook (PC Instructions)

The trick I use to copy formulas over to a new workbook exactly as they appear in the original workbook, is to copy the formulas to a text (.txt) document, and then copy the contents of the text document into the new workbook. The steps are simple:

1. Open the original Excel workbook from which you want to copy the contents (formulas and values)
2. Navigate to the worksheet (tab) within the workbook you want to copy from
3. Reveal the formulas in the selected worksheet by selecting the ‘Formulas’ ribbon and clicking ‘Show Formulas’ under the ‘Formula Auditing’ section (you can also use the keyboard shortcut CTRL+~ to show the formulas)
4. Click the arrow located to the up and left of cell A1 to select all cells in the worksheet
5. Press CTRL+C to copy all cells
6. Right-click your desktop and select New>Text Document
7. Open the newly created text document
8. With the text document open, click anywhere in the text box and paste (CTRL+V) the worksheet contents into the text document
9. With the contents pasted into the text document, choose to select all (Edit>Select All or CTRL+A) and then copy (CTRL+C) the selected text
10. Open the new Excel workbook where you’d like to paste the contents (formulas and values)
11. Navigate to the worksheet where you’d like to paste the contents and place your cursor in cell A1
12. Paste the text contents (CTRL+V)
13. The formulas and values will now be in the new Excel workbook exactly as they appear in the old workbook without any reference (link) to the old workbook

How to Copy the Formatting, Column/Row Widths to the New Workbook

With the contents properly pasted, we can now format the new worksheet to be identical to the old. This functionality is supported by Excel (i.e. doesn’t require a work-around like the previous step) and is fairly straightforward:

1. In the old workbook, navigate to the worksheet from which you’d like to copy the formatting
2. Click the arrow located to the up and left of cell A1 to select all cells in the worksheet
3. Press CTRL+C to copy the worksheet
4. Navigate to the worksheet in the new workbook (where you just pasted formulas and values) where you’d like to copy the formatting
5. Click the arrow located to the up and left of cell A1 to select all cells in the worksheet
6. Right-click and select Paste Special…>Paste Special…
7. Select ‘Formats’ and press ‘OK’
8. Again right-click and select Paste Special…>Paste Special… (all cells should still be selected, if they’re not, re-select them using 5. above)
9. Select ‘Column Widths’ and press ‘OK’
10. The formatting from the old workbook will now be in the new Excel workbook exactly as it appeared in the old

This is a simple, yet powerful Excel trick that makes building real estate Excel models easier. As you begin to play with and add modules to your real estate models (check out a few of the real estate Excel modules we’ve shared), this tip will be invaluable to insure that the modules transfer seamlessly to your model.

If you have any questions, comments, or suggestions, please don’t hesitate to reach out.