Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Copying Between Instances of Excel.

Copying Between Instances of Excel

by Allen Wyatt
(last updated July 7, 2017)

9

If you've ever worked with a system that is configured for two monitors, it is really quite amazing; it can change the way you work. For example, you might open two instances of Excel and put one of them on one monitor and the other on the other monitor. You can then work with both workbooks onscreen at the same time.

One thing you'll notice, however, is that copying information from one instance of Excel to another works differently than when you copy within the same instance. If you copy a cell that contains a formula and then paste it in the other instance, what gets pasted is the result of the formula, not the formula itself. You can't even use Paste Special from the Edit menu to paste formulas; that isn't an option when working with two instances.

Unfortunately, there is no easy way to get formulas from one instance of Excel to another. The best you can do is the following:

  1. In the source workbook, select a single cell that you want to copy.
  2. Press F2. Excel switches to Edit mode.
  3. While still in Edit mode, select everything in the cell (the entire formula).
  4. Press Ctrl+C. Excel copies the formula to the Clipboard.
  5. Press Esc to exit Edit mode.
  6. In the target workbook, select the cell where you want the formula pasted.
  7. Press F2. Excel again switches to Edit mode.
  8. Press Ctrl+V. The formula is pasted into the cell.
  9. Press Enter. Edit mode is exited, and the formula is now safely in the target cell.

This tedious procedure must be used for each formula you want to copy between instances. For this reason, many people choose not to use separate instances of Excel, even if they have the luxury of multiple monitors. Instead, they work with different windows within the same instance of Excel. Follow these steps:

  1. Open the two workbooks you want to work with.
  2. Display the View tab of the ribbon.
  3. Click the Arrange All tool in the Windows group. Excel displays the Arrange Windows dialog box. (See Figure 1.)
  4. Figure 1. The Arrange Windows dialog box.

  5. Choose Tiled. (You can choose other options, if you prefer, but I find that Tiled works best for me.)
  6. Click OK. The two workbooks are now side-by-side in the program window.
  7. Make sure the Excel window is not maximized. (Click the Restore button at the upper-right corner of the program window.)
  8. Position the non-maximized window in the left-most of your monitors.
  9. Drag the right border of the Excel program window onto the second monitor. A single instance of Excel now covers both monitors.
  10. Within the Excel window, use the mouse to arrange the two workbooks so one is on each monitor.

Since you are using a single instance of Excel—even though it extends across two monitors—you can now cut, copy, and paste as you normally would.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12549) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Copying Between Instances of Excel.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Adjusting the Width of Characters

Need to adjust how your characters look horizontally? Word provides an easy way you can scale the horizontal appearance of ...

Discover More

Formatting Footnotes and Endnotes

Footnotes and endnotes can be formatted in the same way that you format regular text in your document. There is an easier way ...

Discover More

Turning Off Hyperlink Activation

Does it bother you when you enter a URL and it becomes "active" as soon as you press Enter? Here's how you can turn off this ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Entering Data as Thousands

There are many different ways you may need to enter data in a worksheet. For instance, you might want to enter data in ...

Discover More

Synchronizing Lists

Two lists of similar data can be challenging to synchronize. Here are some ways that you can align data in two different ...

Discover More

Splitting Information into Rows

Got too much information in a single cell? Here's how you can use a macro to pull apart that information and put it into ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 + 8?

2017-07-07 11:32:08

Gary M. Rose

I find that a simple way to copy formulas from one instance of Excel to another is the following:

1. In the first instance, select the cells whose formulas you want copies.
2. Use CTRL-h to replace all "=" with "#". The formulas have all been converted into text.
3. Copy the selected cells.
4. Paste the cells into the second instance.
5. Use CTRL-h to replace all "#" with "=". The text has been converted back into formulas.


2017-07-07 02:44:24

Niefer

Excel 2010 is the last version which supports MDI (multiple document interface). In Excel 2013 and 2016 it is impossible to have "different windows within the same instance". This is the reason why I stay with 2010.

@zeddy - you could do this all the time - Excel 2010, 2007, 2003, '97 and maybe even earlier versions allow to open multiple workbooks each in their own window. 2013 removed the possibility to open them in a single window.


2013-04-08 12:25:47

Dennis Costello

It is possible to copy multiple formulae at once into the clipboard - first enter <Ctrl-`> (Control accent grave, which is above the Tab key). This toggles between displaying formulae (for the entire worksheet) and displaying their values. Once in this mode, if you select a range of cells and do a <Ctrl-C>, it will indeed copy the formulae onto the clipboard.

Here's the fun part - how these contents are interpreted when you paste them depends on where you paste them:

- Onto any worksheet of any workbook in the same Excel instance: they'll be copied as formulae, but the references will be translated (e.g., if you copy a formula from cell B5 in one worksheet to cell D7 in another, a reference to cell A1 would become a reference to cell C3 - or might end up as a #REF)

- Onto any worksheet of any workbook in a different Excel instance: the values will be pasted, not the formulae

- Into Notepad: the raw text of the formulae will be pasted into the Notepad window, with cells separated by tabs and rows on separate lines. From there, you can select again and paste the formulae, en masse, into the worksheet in the other Excel instance. There might also be a way to do this using the Excel Clipboard that avoids using Notepad as an intermediary.


One quirk with using Notepad this way is that it has a limitation of 1,024 characters per row (with the Tab counting as 1 character, regardless of how many columns it occupies on the screen). If you exceed that, the characters wrap around and are displayed on multiple lines in Notepad, but they're still effectively a single row (and when you copy and paste the text all the formulae will still be on a single row in Excel).


2013-03-19 07:32:37

Trevor Shuttleworth

@Jo: I can't help feeling that "unreadable" is a bit of an exaggeration :) I see a sort of camouflage green at the top of the screen fading down to white.

The only links up there in blue, for this thread, are: Tips.Net > Excel Home > Editing > Copying Between Instances of Excel. And you don't need to follow them because you're already here.

I would have said the vast majority of the page, at least 80%, maybe 90%, is black text on a white background ... which might give some people an issue but it's not blue on green.

Regards, Trevor


2013-03-18 10:54:35

Jo

This site is unreadable--blue links on dark green--no color or light/dark contrast, and I have good eyesight and am not colorblind.


2013-03-16 20:11:36

Janice M

Thanks for the tip. I work with two monitors daily and never even though of doing it that way.


2013-03-16 08:19:12

Willy Vanhaelen

You can use this macro to perform steps 2 to 5 in the first example:

Sub CopyFormula()
Dim X
Set X = New DataObject
X.SetText ActiveCell.Formula
X.PutInClipboard
End Sub

In step 9 you must of course press [CTRL]+[Shift]+[Enter] if it is an array formula.

Willy Vanhaelen, Belgium
http://www.wv-be.com


2013-03-16 06:06:51

Trevor Shuttleworth

When I have the luxury of two monitors, I find it really useful, when I'm developing code, to have the workbook open full screen on one monitor, and the VB Editor on the other.

Windows 7 makes it much easier to split a single monitor but it's sometimes frustrating when you can't see the effect of your code in all its glory.

Trevor


2013-03-16 06:04:24

zeddy

The new Excel 2013 allows you to open multiple workbooks, each in their own window. So you can now easily have workbooks on multiple monitors, and copy between each of them.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.