Posted by: Anthony Drewery | August 29, 2006

Utilising dual monitors with Microsoft Excel 2003

I often work with dual monitors and recently needed to compare two Excel spreadsheets side by side. I found that although they showed as separate instances on the Windows Taskbar I couldn’t have them displayed on separate screens. After some investigation I found that to do what I wanted I had to force Excel to open each document as a separate instance of the application. To do this try the following:

In Explorer go to Tools > Folder Options.

filetypes.jpg

Choose the File Types tab then find XLS under Extensions. Highlight it and click the Advanced button.

advanced1.jpg

Select Open then the Edit button.

open.jpg

Now in the Application used to perform action field go to the end and add a space followed by "%1". Be sure to include the quotes. You also need to unselect Use DDE. OK all of your changes. Now when you open multiple Excel spreadsheets each will be a separate instance of the application so you can move them to separate monitors. The downside is that you’ll use more of your PC resources.

This issue has been addressed in Excel 2007 beta 2.

[Tags]Excel[/tags] 

About these ads

Responses

  1. “Now in the Application used to perform action field go to the end and add a space followed by “%”. Be sure to include the quotes. ”

    Error in text above?

    “%” doesn’t work, but “%1% shown in screen shot above text does work.

    Thanks for the tip, it is helping me a lot!

    Jason

    • just what I needed;) Thank you!

    • Anyone here can tell me how to be viewed and modified an excel file by two users at the same time. I have this problem that when my excel file was opened in my pc, the other pc can only open it that became “read only” file, it can’t be modified anymore. I really thought of it as its security but i wanted to be modified by at least 3 users at the same time and it has to be updating. Kinda need a big help here. pls.

      • It is a security setting built into Excel, there’s no way to get around it that I’m aware of. All the M$ suite is like that, you open it once, then it’s locked for other users till you close it.

    • Hi Guys,

      I tried out everything mentioned in here but no joy till now. The second file too opens up on the same monitor and not as i want, on the second monitor. I am using MS Office EXCEL 2007. Also, should there be a space after /e to add “%1″ or do we add the “%1″ after the /e without a space.

      Further, in the list of file types I have a XLS (microsoft office excel 97-2003 worksheet) and a XLSX (microsoft office excel worksheet) mentioned against each. Do I have to add the “%1″ under both types.

      Also, after I had added the “%1″ under both file types, the “OK” on the first screen shot (i.e. the folder options menu window) changes to Close and the Apply tab remains inactive. Would that have anything to do with the system not accepting the changes. I am at my wits end to set this up. Please help me.

  2. Thanks for spotting that! It should actually be "%1". I’ll update the post.

    • Hi Ant, I tried that fix in MS Excel 2007 but doesnt work. Is there any other way of opening two Excel files in seperate windows in MS Excel 2007. Need some quick help please.

      • Hi Ant and other guys who had been giving tips on how to open two instances of excel for use on two different monitors. There seems to be some confusion here. Are the changes mentioned above for Excel 2003 or 2007 version. The screen shot above says XLS file the folder option. But if you are using Excel 2007 you get XLSX file as well. I tried to make the necessary changes in both, but it doesnt work. Need more help please. This is getting on my nerves as I have a lot of work to do on excel with multiple files open. Any help is welcome, help pleeeeeaaaase.

  3. If you’re already running Excel and you hold down on the control key and then start excel again, you get another instance of excel running in it’s own window. There’s an additional trick which I go into further at How to open Excel in two windows

  4. However, doing it the control key way, the copy and paste doesn’t really work between the two instances of Excel. The data being copied loses the “excelliness” as if it went through a text only transformer. Does your way behave the same?

  5. Very nice tip. It’s unfortunate that M$ missed this…but I suppose as long as they understand, and it’s being addressed in the next version, all is well. =)

    Thanks again!

  6. OK. Implementing the workaround as Ant suggested (including the 8/31 fix Ant posted – “%1″) allows me to get two separate spreadsheets up and running-(Hooray!) BUT… with this change I cannot open “.xls” documents outside of Excel. If I try to open a .xls file from anywhere other than when I am in the application (after opening Excel directly), I get this error message, ” ‘C:\Documents.xls’ could not be found. Check the spelling…..file location is correct.” On my laptop (XP Home) if I click the “x” on the error message, I get additional error messages for each of the excel documents in the directory. Finally, after all of these error messages are “x’ed out”, the spreadsheet I wanted to open finally opens. GRRRR!!!!. I never saw this on my main PC (XP Pro) because I have too many spreadsheets saved and I would be “x’ing out” for several days. LOL
    Seriously, this is making me crazy so I would like to restore the system to the defaults in the advanced edit screen BUT I can’t find the sheet of paper I wrote them down on.
    So: if anyone reading this has their ORIGINAL settings intact or saved what was in the boxes before attempting this workaround, please post them here or send a screen shot to me at a57chevnut at yahoo dot com. I would be most appreciative.

    Lastly, here are the settings on my screen in the “Editing action for type: Microsoft Excel…”
    Action:
    &Open
    Application used to perform action:
    “C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e %1
    Use DDE
    Box is checked ( I never could get this to stay unchecked on either PC)
    DDE Message:
    [open("%1")]
    Application:
    Excel
    DDE Application Not Running
    Box is Blank
    Topic:
    system

    TIA

  7. Have you tried %1 in quotes? I.e. “%1″. When you make this change also uncheck Use DDE before selecting OK.

    If that doesn’t help then the default settings are:

    Action: &Open
    Application used to perform action:
    “C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e
    Use DDE is ticked.
    DDE MEssage: [open(”%1″)]
    Application: Excel
    DDE Application Not Running: blank
    Topic: system

    • Hi Ant, By the way we have an “/n” after

      “C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e
      and should I put in “%1%” after the “/n” or should I remove the “/n” at the end and then add “%1″.

      I tried to do both but no much of a difference and neither are two files opening in two seperate windows.

      Any help from any one else too is welcome.

  8. Hey Ant, thank you so much for posting this information. I’ve been trying to get Excel to do that for some time.

    I had the same problem as Cris when trying to fiddle with the open settings. It was really stressing me out.

    I ‘did’ make sure to copy all of the default values in case I messed it up….but…

    A problem with setting the ‘Application used to perform action:’ back to the original is that the %1 is re-added for you!
    As soon as you alter it, you seem to be stuck with the %1.
    Just re-open the setting after you remove it and save to see it back again.

    Quotes will therefore be required, as most people will need to open files with spaces in the path.

    Microsoft made sure of that, eh?

  9. Hi Ant and fellow Excelnuts!
    Success….
    I tried Ant’s suggestion to put quotes around %1 in this area:
    Application used to perform action:
    Now the line looks like this:
    “C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e “%1″
    As soon as I did that, I could finally uncheck DDE. The software wouldn’t let me do that before. As soon as I unchecked DDE this time, the other boxes below that disappeared so I clicked on “OK” all the way out and started testing to see what happened.
    This all came about after I attempted to restore the settings to the default or original settings before I started messing with them. I had the same problem as Jess. If I removed the %1 from the line:
    Application used to perform action:
    “C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e %1
    The software wouldn’t allow the deletion to stick.
    So I went back to the Edit screen and added the quotes per the above, was able to uncheck DDE, and the rest is history.
    Someone a lot smarter than me (Ant??!!!) will hopefully deal with and solve this problem (about not being able to get back to original settings).
    In the meantime, I’m happy because I now can open as many Excel windows as I want. PLUS, no more error messages when I open Excel documents. :-)))) !!!! My thanks to Ant for posting this solution and staying with it until it worked for folks like me. THANK YOU!

  10. You say that this issue is resolved in Microsoft excel 2007 beta. Can you explain?

  11. Hi Avi, my understanding is that with Excel 2007 you can simply move your spreadsheets to separate monitors in the same way that you can currently move Word docs in Word 2003.

  12. I find that I can get two instances of Excel running simply by holding down the shift key whilst clicking on the application shortcut.

  13. Does anyone else have a problem with the second instance of Excel giving the error message: “PERSONAL.XLS is locked for editing”?

  14. Thank goodness I can have two windows open in Excel, but turns out PowerPoint 2007 can ONLY have 1 open. No matter what I try.

  15. I have similar error message as Arkayngel when I already have a excel window open (without shift click) and then use shift click to open another workbook.

    “Cannot open Microsoft Office Excel Add-In for editing. Please edit the source document instead.”

    However if I use shift click on the application shortcut (not on the excel file itself) to open two instances of Excel, it works fine. I personally prefer this method as I don’t have to change the setting and I have both options when open the excel files for viewing on two different monitors.

    Instead of modify the “Open” action, maybe we can just create a new action called “Open in new window” with the modified command line. When we right click on an excel work book we will also have both option to open it in the normal way and open in a new window.

  16. Hi Ant,

    Thanks for the tip! One other thing I would like to be able to do. Can you open each tab of a single workbook in its own window or on its own screen? So that if you are working with a complex, multi tabbed spreadsheet, you can enter data on one tab and see it effecting things on other tabs?

    Any help is appreciated.

    Thanks,
    Alex

  17. I can’t get excel to accept the change of adding the unselecting the Use DDE. Each time i accept changes and close, then go back in, it is always checked. What gives?

  18. Yep, I get the same problem of not being able to delete the “%1″, and now I can’t open up any excel documents in our document management system, which is really quite worrying…

    Does *anyone* have a fix for this?!

  19. I ran excel /unregserver and excel /regserver got rid of the %1, but it still doesn’t work. It is most odd!

  20. Hi guys
    I’m not sure I’m in the same problem as you but I found this website while searching for solution for my Excel problem and I’m really confused after reading so many technical messages here.

    The problem:
    I’ve lots of excel files (contracts & agreements). Have been using them since 2005 with no problem. But just yesterday, when I double clicked the file from its folder, the error message that Excel gave me was: C:\Folder Name\File Name could not be found. Check the spelling of the file name, and verify that the file location is correct.

    But the location and file name are definitely correct since I’ve been using it for years.

    Any solution please? Really appreciate the help. Thank you.

  21. I am having the same problems that Donna is having, I am trying to change back to defaults. Example removing the “%1″, and it lets me remove it but then I go back into the editing againg ant %1 is back in there without the quotes. I keep getting the error check the spelling. Is there a regeistry entry that this might have changed. let me know thanks
    Shawn

  22. Thank you SO much for this post! I work with several Excel reports on a daily basis, using dual monitors, and I have been struggling with this for months – YOU ROCK!!!!

  23. w00t! This little tip saved me so much heartache… thanks!

  24. @Donna & SHawn:

    http://support.microsoft.com/kb/207574/en-us

    This one seems to be related to solution in this forum.

  25. Thanks :D

  26. THANK YOU ALL!! I needed to open excel across multiple monitors. I asked this question on Yahoo! Answers and “Benjamin M” pointed me here. It works. I can’t tell you how excited I am. Thanks all.

  27. I could always open multiple instances of Excel, just by using the “file, open” command within a running instance of Excel and have them running on seperate screens. The problem is that formulas cannot be copied from one instance to the next. All data is copied as text, even if I “paste special”.

    I can only successfully copy formulas from one window to another window within a single running instance of Excel. I tried ANT’s solution in hopes that it would fix the problem for multiple running instances….no change.

    DDE does remain checked, no matter what I do, is that the problem?

  28. I came across a good little article on multiple monitors and MS office at CineMassive Displays. checkout their “resources section”. Good stuff. Definitely worth a quick read.

  29. How does one apply this fix on Office 2007 when running Windows Vista? The above advice is for WindowsXP, but doesn’t seem to translate over for Vista.

  30. Assuming that you have an nvidia based card – if you want to just ‘maximize’ excel across both monitors make sure to turn on nview desktop manager. Now if you r-click on an application in the taskbar (or it’s own title bar) you can ‘maximize to desktop’ or send the application instantly to any monitor you want.

    I assume that other video cards have similar capabilities.

  31. Hi, Please I have a problem in my excel. May it is not to difficult but in fact I have no solution to it and waiting ur solutions. It is that, when I open any excel sheet it is open as blank one and I need to go to file menu and press open and go to the path of the sheet to open it so please any help to not loss this time waiting.

  32. Great tip. Thanks a lot – Really helpful.
    Have you guys a mailing list?

  33. I can’t keep the dang Use DDE box unchecked. I make the changes, uncheck the box. Click ok out of all the Folder options boxes. Then even before I open excel if I go back in the Use DDE box is checked again. Excel is lcosed during tbgis whole process. Any ideas what I am doing wrong?

    Thanks,

    Noel

  34. WOOT! Leave it to people other than microsoft to provide the best answers! (and not just to tell you “buy office 2007″)

    Thanks much!

  35. Hi,

    Ive come back to this page after I got a new laptop.

    I now also have the same problem as above, the fix is not working because the ‘Use DDE’ Box is always checked when I return. Are their any other parameters I need to change.

    Ali

  36. GUys.

    I resolved my earlier probem, which may also help you.

    You need to select and edit ‘Open’, and not ‘New’ which is at the top. When in the ‘Edit File type Screen’

    Nazim

  37. Guys,

    This fix is not working for me; however, I tried it on my co-workers computer and it worked.

    The only difference between us is that he has Administrative privileges and I do not.

    Funny thing is though that in both case the “USE DDE” box will not remain unchecked.

    Wade

  38. Is there any setting or VBA code that I can use in a workbook to ensure that when someone opens it, it will open in a new Excel session?

    Steve

  39. OK. I’ve come up with a solution for the more advanced. My issue is that i have an application written in VBA in Excel and i need two things to happen related to this topic.

    The first thing is that when a user opens my application i need my application to open in a seperate session if there is already a workbook open. Otherwise when my userform displays, they will be unable to continue working on their own spreadsheet. My workaround for this is to count the open workbooks and if more than 1 (my app) re-open the app in a new session.

    This code can run from the from a macro called auto_open in any module:

    Public Sub Auto_Open()
    If Workbooks.Count > 1 then
    Dim XL as New Excel.Application
    set XL = createobject(“Excel.Application”) ‘ this creates a new session
    XL.workbooks.open (“C:\myApp.xls”) ‘ open my app in the new session
    XL.Run “myApp.xls!Auto_Open” ‘ run my auto_open macro
    set XL = nothing ‘ lose reference to new session to allow session to close
    workbooks(“myApp.xls”).close False ‘Close this session’s workbook
    End if
    End Sub

    The 2nd thing i need is that while my app is open, any other excel documents that are opened need to be thrown into a new session as well. I achieved this by creating a class module for the application object to capture another workbook opening. If you are getting lost, don’t worry! Just follow my instructions. Create a new Class Module, rename it as ‘clsApp’ and enter the following code:

    Public WithEvents App as Application
    Private Sub App_Workbook_Open(ByVal wb as Workbook)
    If wb.name “myApp.xls” Then ‘check it’s not my app opening
    Dim xl as new excel.application

    end if
    End Sub

  40. Sorry – i submitted by accident! I’ll continue where i left off…

    Public WithEvents App as Application
    Private Sub App_Workbook_Open(ByVal wb as Workbook)
    If wb.name “myApp.xls” Then ‘check it’s not my app opening
    Dim xl as new excel.application, strName as string
    strName = wb.fullname ‘ store the workbook path
    set xl = createobject(“Excel.Application”) ‘create new session
    xl.visible = true ‘make sure the new session is visible
    wb.close ‘ close the workbook
    xl.workbooks.open strName ‘ Open the workbook in the new session
    end if
    End Sub

    Once you have the class module created, you need to add the following line to the top of the module we created previously (with auto_open):

    Public myApp as NEW clsApp ‘ This creates a copy of our class app

    You also need to amend the Auto_Open Procedure we wrote earlier with the Else statement shown below:

    Else:
    Set myApp.App = Application ‘assign current application to our class
    End If

    So, now you’re all confused (I apologise!!) the original module should look like this:

    Public myApp as NEW clsApp ‘ This creates a copy of our class app
    Public Sub Auto_Open()
    If Workbooks.Count > 1 then
    Dim XL as New Excel.Application
    set XL = createobject(”Excel.Application”) ‘ this creates a new session
    XL.workbooks.open (”C:\myApp.xls”) ‘ open my app in the new session
    XL.Run “myApp.xls!Auto_Open” ‘ run my auto_open macro
    set XL = nothing ‘ lose reference to new session to allow session to close
    workbooks(”myApp.xls”).close False ‘Close this session’s workbook
    Else:
    set myApp.App = Application ‘ Assign current application to our class
    End if
    End Sub

    So with just a module and a class module we have acheived a workbook that runs entirely in its own session at all times.

    Good luck and sorry for the confusion!!

    sugarflux
    http://www.broox.co.uk

  41. Hi Alex,

    You asked: “Can you open each tab of a single workbook in its own window or on its own screen? So that if you are working with a complex, multi tabbed spreadsheet, you can enter data on one tab and see it effecting things on other tabs?”

    This one isn’t too much hassle. Just go to Window>New window and a new window will appear of the same workbook. You can then go to a different worksheet in the second window. Then go to Window>Compare side by side with… (or just position the windows manually) to see two worksheets at the same time. Changes made in one are updated instantly in the other.

    Hope this helps, Mat

  42. It makes sense, but it looks like using this fix/hack/whatever does not allow a second instance of “personal.xls” for macros.

    I’m not well versed with excel, but I don’t think this will pose an issue for using already created macros document-to-document. but it may cause issues when you are trying to create a new macro.

    Solution would probably be to close all instances of excel and just open the one you are using to create the macro.

  43. Do you know of a way to do the %1 thing in Vista? The file extensions mapping in Vista works differently – I can’t modify (or seem to fin the place to modify) the command-line parameters to add the %1. Also, I am using Office 2007 with Vista. Thanks for the great tip.

  44. Everytime I open an Excel file now, I get the message that the Personal.xls file is locked for editing by “my user name”… I know Personal.xls is for storing the macros, so how do I get rid of this message and does this mean I can’t create any more macros?

  45. Has anyone solved the Vista problem? I have XP and am VERY thankful for your tip, but my boss is using Vista and I would love it if he would stop whining about not being able to use the 2 monitors

  46. Ant – thanks. Since my “Use DDE” box wouldn’t remain unchecked, the comment above you left on “September 21st, 2006 at 7:24 am” solved things for folks I support with one change… where you wrote “DDE Message: [open(”%1″)]“, I had to change that to {open(”%1″)} as the brackets were opening my debugger. So, the settings that worked for me were:

    Action: &Open
    Application used to perform action:
    “C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE” /e
    Use DDE is ticked.
    DDE MEssage: {open(”%1″)}
    Application: Excel
    DDE Application Not Running: blank
    Topic: system

    Thanks again for your help.

    GeneH @ http://genehuller.com

  47. Wow! This really works! Easy to modify, difficult to find to this info!

    Too bad this isn’t by default but I sincerely appreciate the help on this. Now I longer have open a blank worksheet and then open the file.

    If I have one Excel window open, there is at least another one or more open. With dual or single monitors, this is a perfect solution.

    I had to resort to using Open Office’s Spreadsheet. While I am a fan of opensource programs, this OO2.2 spreadsheet resulted in way too many errors, particlularly entering a cell’s data (linking) from another open SS. This caused a lot time and grief! I use Excel for tax prep and can’t afford erros!

    This little tidbit of info saved me $200 from buying Excel2007, when Excell97 and 2002 work just fine for me.

    While this is a minor inconvenience, before knowing this, I must say kudos to MS for a great, flawless (in my opinion and use) product with Excel.
    If you read this and do use OO2.2’s SS, be prepared for trouble if converting from Excel to OO SS.

    tt

  48. Ant,

    A user (Excel 2003 – XP) told me he tried your solution but says that every time he shuts down his computer, he has to make your suggested change.

    What is he doing wrong? Unfortunately, since I do not have two monitors, I cannot test it myself.

    Thanks!

  49. This hack brings me much joy. I have to wonder why Microsoft doesn’t have problems with Word out of the box but Excel seems to think that multi-monitor might as well not exist.

    PS This works fine with Excel 2007 as well.

  50. Has anyone solved this Personal.xls problem? I love using multiple instances with multiple monitors. But that personal.xls error is for the birds. Has anyone figured out a way to fix this? I use macros across multiple excel workbooks so deleting the personal.xls is not an option.

  51. In order to open a second copy of Excel 2007 on a computer running Vista, press and hold the shift key when clicking on the Excel icon. This will open a second instance of Excel 2007. This is very helpful when your second screen isn’t the same size as the primary screen (such as a laptop) and isn’t next to the primary monitor).

  52. In order to open a second copy of Excel 2007 on a computer running Vista, press and hold the shift key when clicking on the Excel icon. This will open a second instance of Excel 2007. This is very helpful when your second screen isn’t the same size as the primary screen (such as a laptop) and isn’t next to the primary monitor).
    To open the spreadsheet in this new instance, open Windows Explorer, find the excel document and then drag and drop it onto the blank Excel application.
    Hope this helps as it has worked for me.

  53. for those having trouble with the personal.xls file generating an error when you open two instances: try setting the personal.xls to read only.
    I no longer get an error.

  54. Thanks for all the help… Why does MS change things like this?

  55. “Why does MS change things like this?” – because times change, software is developed, and interfaces and options change along with it.

    Otherwise, we’d all still be running Visicalc on a TRS-80…

  56. Thanks for your help! I’ve been looking to open excel spreadsheets in seperate instances of excell for a while. Figures it wouldn’t be as simple as an option in the menu!

  57. using the %1 with Vista and Excel 2007, this is what I did: using regedit change these keys: hkey_classes_root\excel.sheet.12\shell\open\command
    edit default and add the “%1″. the next key under command is ddeexec remove [open("%1")] from default. you will also have to make the same changes to hkey_classes_root\excel.sheet.8… same as above. Hope this helps.

    • I am using Vista and Office 2003.

      Every option I have seen for Vista changes the opening for all files. This is not a true “Open with” in new Excel Window. Luckily I saw an article about opening anyfile with notepad (really any application but it was for notepad).

      A solution: Sendto

      Open the folder below.

      Users\\AppData\Roaming\Microsoft\Windows\Sendto\

      I found it easiest to navigate to by right clicking the windows button, selecting Explore, clicking the Windows parent folder in the address bar, and clicking the sendto folder.

      Create a shortcut to Excel.exe. In this folder.

      If you use Vista 64 and 32 bit office like me, look under Program Files (x86) instead of Program files.

      Name it something like Excel in New Window

      Anytime you want to open an Excel document (or anyfile for that matter) in a new Excel window, right click, select sendto and whatever you named your shortcut.

      The folder is different for XP but otherwise it should work .

  58. In 2003 sp2 in options->general, you can select “Ignore other applications”. Then if you open a worksheet from the explorer window it’ll open as a different window (as a separate process). If you open from excel itself it’s still the same window.

  59. Thanks! You are my hero for the day.

  60. This works for Excel 2007 in compatibility mode (essentially saving docs in the old 2003 XLS format, which I prefer to do so that everyone I work with can use the docs. rather than dealing with some proprietary bs).

  61. I have changed my filetype and excel over dual monitors works well. Thanks for the info. Only problem now is i can’t use Sheet Copy/Move “To Book” as no other workbook names are displayed. Can anybody assist with vba or other solution to be able to copy/move sheets across multiple excel instances.

  62. To answer your question, Tammyl:

    Once you get the Excel files to open as separate instances (and hence on different screens) you may run into problem copying/moving worksheets between different workbooks (files) because worksheets don’t copy/move across instances.

    The way to get around it is to pick an instance of Excel, use “File -> Open” or drag and drop the file icon to have multiple workbooks opened in the same Excel instance. Of course you can’t put the workbooks in the same instance on different monitors (back to the original setting). So, do it only when you want to copy/move entire worksheets between files.

  63. All of my Excel work involves VBA so I created a module I insert in all of my workbooks. I invoke two subs, one in Workbook_Open and the other in Workbook_BeforeClose. One sub “isolates” Excel from opening additional workbooks (unless you explicitly use File–>Open menu). The other “exposes” it once again upon close.

    This is accomplished by using VBA to set an Excel Application flag “IgnoreRemoteRequests” to True or False. This flag can also be set manually in Tools–>Options (General Tab)–>”Ignore Other Applications”

    “IgnoreRemoteRequests” is an APPLICATION flag which means it alters the functionality of your copy of Excel. It is not a workbook flag and therefore is not saved with the workbook. The state of all application flags are only saved upon exit of Excel, and the last instance closed will overwrite all flag settings of the previously closed instance. The settings of any already open instances of Excel will not be “seen” by opening another instance. Also, this setting will be for your installed copy of Excel only. Sending someone one of your workbooks will not change their Excel settings (unless your workbook contains the VBA that changes the settings.) *

    *Note: our workstations have local copies of Excel installed. I don’t know how application settings are saved in a shared environment.

    It can get confusing. Say you have no copies of Excel open and the current state of Ignore=No. You open a workbook containing the VBA subs which set Ignore=Yes. (You can see the “Ignore” box checked in the options menu, General tab.)

    You Dbl-click another workbook, one that does not have the VBA subs. It will open in a new instance of Excel because the first one open is now ignoring open requests. But, if you look in the options menu you will see the second Excel is not ignoring requests. If you Dbl-Click a third workbook it will open in the second instance of Excel, not in a new instance and not in the first one.

    You decide to close both Excel instances. Whichever instance you close last will be the settings used the next time Excel opens. This is REGARDLESS of whether or not you save any workbook changes — remember, this is an application flag not a workbook flag.

    If you close the first instance last the Ignore flag will still be set to True. This can be a problem. The next time you Dbl-Click an .xls file, or attempt to open an Excel mail attachment, etc. you will only see an empty blue window. Excel is ignoring the open request!

    This event can be a surprise. It appears as if Excel is “broken.” Not to worry, simply use File–>Open to open your workbook, then go to the Options menu and uncheck the “Ignore Other Applications” box.

    To automatically prevent this situation from happening, if you turn on the “Ignore” flag upon starting Excel it is imperative that you turn it off before exit. Here are the subs that accomplish these tasks:

    ‘ (put in ThisWorkbook module)

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ExposeExcel

    End Sub

    Private Sub Workbook_Open()

    IsolateExcel

    End Sub

    ‘ (put these in Module1)

    Global Const Yes As Boolean = True
    Global Const No As Boolean = False

    Public Sub ExposeExcel()

    ‘ accept remote requests (double-clicking a workbook file will now open in this
    ‘ instance of Excel instead of starting a new instance)

    Application.DisplayAlerts = No
    Application.IgnoreRemoteRequests = No
    Application.DisplayAlerts = Yes

    End Sub

    Public Sub IsolateExcel()

    ‘ ignore remote requests (double-clicking a workbook file will now start a new
    ‘ instance of Excel instead of opening in this instance)

    Application.DisplayAlerts = No
    Application.IgnoreRemoteRequests = Yes
    Application.DisplayAlerts = Yes

    End Sub

    Note: if ever you are debugging or working in Excel and your VBA subs stop due to an error, (or you set Application.EnableEvents = No while in development phase) it is possible the ExposeExcel sub will not run on exit. You will know the next time you Dbl-Click an .xls file and Excel comes up blank! Simply do File–>Open as described above.

  64. thank you for this great tutorial about how to automatically open multiple instances of microsoft excel !

  65. Rich,
    In using your registry change, separate instances of Excel 2007 in Vista do open. However, the files themselves that I’ve opened Excel with do not actually open. Is there an extra step I’m missing? Also, it would be FANTASTIC if there was a way to give users the option of switching between having spreadsheets open in separate windows or in the same–for instance, have 2 open in one window, and another 2 in a different window.

  66. Works great. Had to set this for both Excel 2003 and 2007 documents, as i open both the old and new….
    Thanks.

  67. Great help! This Excel problem has been one of my major frustration, now you have helped me solved it.

    More power to you!

  68. Now is there any way to do this in powerpoint as well?

  69. Please disregard all above comments as their is an easier way to do this.

    When you are looking at the file types advanced properties there is a checkbox that states browse in same window uncheck this and all of your issues are resolved. Excel works in as many windows and on as many screens as you want.

  70. As for Powerpoint it is the same as excel to do this uncheck browse in same window under the advanced propety file type ppt.

    This was a nice improvement by Microsoft

  71. Thanks, this is a very useful thread for learning how to open two Excel files over two monitors.

    I now have a new question:

    Is it possible to have two separate sheets from the same Excel file open in separate monitors?

  72. Thanks for the tip buddy. it did work. Thanksazillion

  73. Thanks for sharing this. It works fine.

  74. Does anyone know how to work between 2 open instances of excel on a dual monitor, and add links, copy formulas, etc. between the two files? Would be really helpful to be able to see both worksheets while doing my edits. Maybe I’m missing an add-in?

  75. See http://support.microsoft.com/kb/207574 if you want to remove the changes from this blog.

  76. so I’ve done everything prescribed in this walk-through, however each time I uncheck “use DDE” the system reverts back to using “DDE.” After clicking Ok to get out of the dialog box, I can go back in by selecting “edit” and DDE is checked off again, except nothing is in the DDE message field.

    Now, this does allow me to launch excel shortcuts from my desktop and have it create separate instances such that I can view one excel document on one monitor and another excel spreadsheet on my laptop; HOWEVER (and this is huge) — whenever I launch an excel spreadsheet from within a program that we use at my work, it opens every spreadsheet within 1 instance of Excel. How do you get around this? 2003 allowed you to “use new instance of Excel” or something like that….

    Help would be greatly appreciated…I would revert back to 2003 if I could to solve this issue.

  77. there are two ways to revert back to default settings:

    – one, edit the registry per instructions in the MS link above.
    – two, go to Add/Remove in Control Panel, select Change, select Repair

    both tested, both work.

  78. Ant,

    I am having trouble getting started with the instruction: In explorer go to Tools.

    Can you help this novice, me, get to the Folder Options window that you display above?

    Does using Vista make a difference? Many thanks, Greg

  79. Thanks you have just explained something that has been puzzling me for months. Much appreciated.

  80. Woohoo! After about a year of using dualview I finally had enough of using excel on one screen only. Thanks to Google, and more so you, its sorted! Brilliant!

  81. Much to my surprise I found something that works in Excel 2003 that was so easy I couldn’t believe it.
    When you right-click on the file in Explorer and select New, your files open in separate windows. Double-click opens the worbooks in the same window. I’d like to know if this works for anyone else.
    I’m still trying to figure out how to do it on my home computer using Excel 2002 with Vista. It doesn’t care right-click or not, they open in the same window.

  82. I changed the folder settings as stated by “ANT” spreadsheets open as advertised, but spreadsheets with links to databases freeze. Is there a workaround for this?

  83. This works for both XP and Vista so far

    create a notepad doc with the following content between the lines and save as excel.bat

    ______________________
    @echo off

    cls
    echo Set File types to Allow Multiple Excel windows
    echo Make sure Excel is closed before you continue

    pause

    reg export “HKCR\Excel.Sheet.8\shell\Open” “%~dp0\Excel8_Open.reg”
    reg export “HKCR\Excel.Sheet.12\shell\Open” “%~dp0\Excel12_Open.reg”

    reg delete “HKCR\Excel.Sheet.8\shell\Open\ddeexec”
    reg delete “HKCR\Excel.Sheet.12\shell\Open\ddeexec”

    reg import “%cd%\excel.reg”
    ________________________________________

    then create another file in notepad called excel.reg with this content that is between the lines:

    ______________________________________

    Windows Registry Editor Version 5.00

    [HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\Open\command]
    @=”\”C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE\” /e \”%1\””

    [HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open\command]
    @=”\”C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE\” /e \”%1\””
    ______________________________

    keep them in the same directory and run the excel.bat file. From vista you have to open “cmd” as administrator and run it from the command line

    Enjoy,
    Britt

  84. I put together a pretty simple solution that allows opening Excel in two seperate windows when using Windows Vista with Excel 2007.

  85. For this business advise use-how to repair xls file,software is free as how as i remember,it recover worksheets in Microsoft Excel format,program will permit to save many hours of your time and efforts of your colleagues,will allow to repair your workbooks in Microsoft Excel format just in several mouse clicks,will be repaired automatically by our software for repair .xls files and without blind-spots, cause if you get something very serious, for example HDD failure, all your information on it will be lost, together with workbooks in Microsoft Excel format.

  86. Is there a way to undo the above. I tried but it is not reverting back to the previous setting of opening excel workbooks in the same instance everytime as i need to copy paste formulae from one to the other. Please help!

  87. [...] a new Word or Excel file, it adds it to current instance of the program. Well, I finally found the solution to this problem. Although this is for Excel, it worked for me for *.doc files. Post a comment — Trackback [...]

  88. If you’re looking for a solution that works with Vista – check out Brett’s link (4 posts above)!!

  89. I asked a few people in my office, including someone in IT, if this could be done and was told no but I thought “there’s gotta be a way” and found this solution. Simple fix and works like a charm! I’ve shared it with several office mates and all are very happy with the results. Thanks for the GREAT tip!

  90. Thanks Ant!

    Three years after your original post and it’s still helping people! I finally got fed up with manually resizing my Excel window across two monitors just so I could compare files side by side. A quick Google search led me to your site.

    Until now I always thought Excel 2003 was MDI only, but that seemed strange since Word 2003 is SDI.

    The “%1″ addition to the command line does the trick and forces Excel to open my spreadsheet files in SDI mode.

    It’s wonderful! Thanks!
    -Chris

  91. Ant,
    I tried your steps, but I didn’t see the “folder options” in the Tools menu of Internet explorer. I’m not sure if it has anything to do with the fact that I have the Microsoft 2007 version. Regardless, I would still like to know how I can utilize dual monitors with Microsoft Excel 2007. Would you or anyone know how for this version??
    Thank you

  92. I just found what looks like a solution to my earlier question. If anyone has the same question then check out this link:

    http://brett.batie.com/microsoft/open-excel-in-multiple-windows/

    It requires you to download this software, but so far it’s working.

  93. Guys, be careful with this one. You’ll notice references to registry changes throughout the post because if you try to change the modification back, it will not work. You’ll get the %1 stuck on the end (no quotes) and see the error Chris reported on September 27, 2006 at 5:47 am. I would suggest backing up the registry before making this change (and before attempting to change it back to defaults). The Microsoft KB article that edjez referenced on February 1, 2007 at 10:58 am (http://support.microsoft.com/kb/207574/en-us) has a link to tell you how to do it. Also, I did confirm that the KB article does work to allow you to set the configuration back to the default if you want to. However, I’m on XP with Office 2007 and I set both xlsx and xlsm files to open in multiple windows. When I went to change them back, I had to modify two registry keys; and neither was the key the KB article said. I believe the two were HKEY_CLASSES_ROOT\Excel.SheetMacroEnabled.12\shell\Open\command and HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\Open\command. But you probably just need to look through all HKEY_CLASSES_ROOT\Excel.Sheet* folders, find the *shell\open\command key, and remove the %1 from the end if it exists. Rich mentioned something similar on October 23, 2007 at 2:46 pm. Again, this is if you *DON’T* want Excel to open multiple windows and you are trying to reverse this post’s suggested hack.

    The hack is great and I do like opening in multiple windows by default. So, why would you not want multiple windows? Well, Knox said it on August 31, 2006 at 11:36 pm – “However, doing it the control key way, the copy and paste doesn’t really work between the two instances of Excel. The data being copied loses the “excelliness” as if it went through a text only transformer.” So if you’re copying lots of formulas between workbooks, you’ll need them to be in the same window. I don’t know why Microsoft did this, but I don’t know another way around it – and, yes, it’s still this way on Excel 2007. Of course, you can set your default, as this post suggests, to open multiple windows. But when you need to copy formulas, simply open the first file then open the second file from within the first Excel window. This will cause both files to be in the same window and allow formula, format, ect. copying.

  94. I have a demande how i can change this on windows7?

    Thanks for answer

  95. This did work for me opening excel files from the hard drive. They opened into seperate instances. But if I want to check-out two Excel files from a Sharepoint library it will not open two instances. They open in the same Excel instance and the two monitors can’t be used. Any ideas for how to get this to work when dealing with Excel files from Sharepoint?

  96. Thanks for the post. This has bothered me for a long time!

  97. [...] This is the copy of Anthony Drewery post. [...]

  98. By the way if you accidentaly mess anything up while doing this you can return it to the default settings by running office repair.

    apparently just adding the text back the way it was didnt work to well on my computer.

  99. I didn’t read through all the comments (lots! and such good info) but was wondering if you knew a way to have multiple templets within one excel file opened in each monitor?

  100. Oh and I have Windows XP Professional

  101. Excellent, this worked perfectly. Thanks!!

  102. I have XP SP3 and Excel 2007 SP2 with Dual Monitors. I tried the above “%1″, but it doesn’t help. My scenario: I have Spreadsheet #1 open. I go to our SharePoint site and open Spreadsheet #2. It opens it within Spreadsheet #1.
    I want to compare the spreadsheets or copy & paste back and forth, so the only way I found to make it work is open a blank Spreadsheet (second instance of Excel), then open Spreadsheet #2. It will typically open under Spreadsheet #1, but if I close it and re-open it a few times it will eventually open it under the Blank Spreadsheet. Then, I can place Spreadsheet #2 on Monitor #2. What a hassle.

    Another issue. I have Excel 2007 SP2, Dual Monitors, Remote Desktop. If I have Remote Desktop running on my Second Monitor, and try to copy and paste into Excel 2007 which is on Monitor #1, I have to click in the Spreadsheet 2,5,10 times before I can type or paste anything in it. I found this issue on another forum but no one had a fix.

  103. I should explain more… When I used “%1″, I could uncheck “Use DDE”, but after clicking okay twice, if I go back in those settings, “Use DDE” is re-checked. Now when I try to remove “%1″ it keeps coming back.

  104. how do I change it back?

  105. What’s wrong with: “restore down” the main Excel window, stretch it’s size until it fills both of your screens and then arrange your individual windows within the supersized Excel window however you like?

    Works for me everytime and no messing with macros, settings etc

    Sometimes the answer is simple.

  106. Thanks, there’s no point having two monitors if programs insist on being grouped together on one!

  107. Wow, who would have thought dual screens could be such a huge topic!!
    Can anyone tell me if it’s possible to use excel 2003 in dual screen mode AND beable to link between the two without the need for administrator permissions…?
    Thought I was being smart going for the 2 screen option to stop me going mad but I’m no further forward (although checking the links once their done is much better!).
    Thanks folks!!
    Any help would be much appreciated

  108. I have the same problem with XL 2007. All i want to do with my 2 instances of XL, is be able to link between the two…. anyone able to assist?

  109. This tip fixed my office 2007 issue where I could not open files via double click! Thanks for your help, much appreciated. I searched many M$ KB articles and FixIts, none helped except for yours. Appreciated

  110. THANK YOU THANK YOU THANK YOU.
    For the love of god.
    I’ve been to easily a dozen sites purporting to solve this, but none did, until this one.
    (Did I say thank you?)
    THANK YOU.

  111. (ps – this fix worked for me in v2007)

  112. thanks for the tip. now i can open excel ssheets in two monitors.

    • Hi Bobby. Are you able to link between the two separate instances that you have opened?

  113. ok it’s not working anymore: all my downloaded docs open with a numeric filename starting with X|000… and save somehow as 03. I must have done something wrong, going to back out my changes, but LORD I hate microsoft for this.

  114. [...] http://blog.drewery.net/2006/08/29/utilising-dual-monitors-with-microsoft-excel-2003/ [...]

  115. hi,

    its working perfectly in excel 2003.but its not working in excel 2007.

    pls let me know the Excel 2007 setting

  116. Thank you thank you thank you! As a dual-monitor user, this has made my life SO much easier! I’ve shared the info with other users here in our office, too.

    Not sure how you figured this out, but it worked GREAT!
    Thanks again!
    Colleen

    • Hi Colleen S … are you able to link between your two instances that you have opened?

  117. Simple fix: Open up two blank Excel docs (they will open up as two separate instances) and put them on either monitor. Go to ‘File’ on each one and open up each of the two .xls spreadsheets you require. ‘Viola’, you now have two separate instances you can work with on dual screens. Not sure if you would be able to link between them though.

  118. THANK YOU!!!

  119. Genius – excellent. Thanks for making my job easier!

  120. Excel won’t open on double-click. Excel opens to a blank screen. Docs can be opened through File>Open. I tried selecting in Excel Options>Ignore other Apps that use DDE, but that didn’t work. Any suggestions would be greatly appreciated!

  121. THANK YOU!! I have been going insane trying to work on two excel spreadsheets on only one monitor.

  122. Excellent advice, this was driving me crazy. Worked perfectly.

  123. [...] http://blog.drewery.net/2006/08/29/utilising-dual-monitors-with-microsoft-excel-2003/ Dieser Beitrag wurde unter Allgemein veröffentlicht. Setze ein Lesezeichen auf den Permalink. ← internal error 2753 regutils.dll bei Java- install. [...]

  124. Hello,
    How do I use this work-around with Internet Explorer 10? I don’t see Folder Options under Tools (I’m using Excel 2010)

  125. I followed this and now, for some strange reason none of my shortcuts to my excel files work anymore. I have to open Excel and navigate to the file I want to open it. Cannot even open them from the explorer. I get the message ‘file not found’ even when opening from the explorer.

    I tried to change it back but this has not solved the problem.

    Tried even re-installing excel but still no luck – anyone got any ideas ?

    Running XP Pro SP3 with Excel 2003

  126. I’ve just seen some of the posts above and found a way to modify the registry back froim a microsoft kb article :

    http://support.microsoft.com/kb/214334/en

    This means I don’t get the error messages any more, however the file still does not open !

    Only way to open the file is to open excel then use file-open and search for the file on the network.

    Not good when working on a company network and the file is quite difficult to find.

    Can anyone help me get my shortcuts to work again or allow me to be able to open directly from the explorer ?

    It must be a registry edit…

  127. I’ve just seen some of the posts above and found a way to modify the registry back froim a microsoft kb article :

    http://support.microsoft.com/kb/214334/en

    This means I don’t get the error messages any more, however the file still does not open ! I cannot open any excel attachments I receive by email either – I have to save them, then open excel, then use “file-open” – what a time-waster !

    Can anyone help me get my shortcuts to work again or allow me to be able to open directly from the explorer ?

    It must be a registry edit…

  128. Finally looking through the posts above : I ran excel /unregserver and excel /regserver

    Got my Excel back the way it should be – still need to find a way to opentwo files in separate windows though but at least my shortcuts work and I can open outlook attachments


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: