If you want to maximise Excel on a single monitor you can just click on the maximize button
That's ok but this just maximizes it on the current monitor. If you have multiple monitors you may want to maximize it across all of your monitors, but Excel doesn’t provide any quick way of doing this.
You could position the program window so that the top left corner of Excel is in the top left-most corner of your left screen, then drag the edges of Excel so that it fits across both monitors.
Or you can use a little VBA to do the job for you.
I first came across this code at the Spreadsheet Page and have modified it for my own purposes.
Caveat
The code only works if your left-most monitor is your primary display. Your primary display is the one that has the Windows/Start button on it, or if you are using Windows 10, it’s the one where the Search box and clock are displayed.
If you are like me and have your primary monitor on the right, this code just does not work. The top-left corner of Excel is positioned at the top-left of the primary monitor, but I can’t get the code to set Excel’s width greater than that single monitors width (resolution). It may be some quirk to do with my graphics card, or Windows 10 or something else.
But, as long as the primary screen is on the left, with the 2nd screen on its right this worked fine.
I don’t have 3 screens but presumably if I did and the 3rd screen was to the right of the 2nd one, it would work too. If you have 3 screens please let me know.
Maximizing the Excel Application
In VBA you can use
Application.WindowState = xlMaximized
to maximize the program, but this only works on the active screen, the one where the Excel program is located.
You could also use
Application.DisplayFullscreen = TRUE
which maximizes the program on a single monitor and removes the Ribbon and other things that use up some screen space.
But to maximize across multiple screens you first need to find out the maximum screen width and height available to you.
To do this you use a Windows API call, and to allow us to do this we need to declare a function found in the Windows user32 library
Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
By declaring the GetSystemMetrics function we can use it to find out lots of things like how many monitors are on the computer
Number_of_Screens = GetSystemMetrics(SM_CMONITORS)
Or we can get the maximum size that we can use across all our monitors
Total_Screens_Width = GetSystemMetrics(SM_CXVIRTUALSCREEN) Total_Screens_Height = GetSystemMetrics(SM_CYVIRTUALSCREEN)
which gives us the maximum X and Y resolution for all our monitors.
We can set the application Top and Left values to 0, so the top left most corner of our left most screen is 0,0 (x,y co-ordinates), and then use the maximum X and Y values our screens support to set the application width and height.
We end up with Excel maximized across both (or all) our monitors. In reality Excel isn’t actually maximized in the sense that the Excel window is ‘stuck’ to the edges of our monitors, it is really just stretched across all the available space. If you click on the maximize button at the top left of the Excel window, you’ll see that it snaps to fit the entire primary screen.
Hiding the Ribbon and Other Toolbars
Now we have Excel maximized across our monitors, why don’t we squeeze as many cells as we can onto our screens? Let’s remove the Ribbon, Formula Bar, Status Bar and Workbook Tabs.
You can hide the QAT and Ribbon Tabs (the menus like File, Home, etc) too but in doing so you won’t be able to click your icon on the QAT to undo all of these changes.
That’s not an issue if you assign a shortcut key to the macro, but for this example I will leave the QAT and Ribbon Tabs visible so I can use a QAT icon to hide/unhide the Ribbon and toolbars.
You'll probably want to put this code into your PERSONAL.XLSB so you can use it with any workbook.
Download the Code
Download your own copy of the code in this macro enabled workbook.
64 Bit Excel
If you are using 64bit Excel you’ll need to change the function declaration slightly. Look in the code and replace the Private Declare ... line with this. Make sure this is all on one line, not wrapped onto two lines in your code.
Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As LongPtr) As LongPtr
Maximize Your Sharing
Do you know someone with 8 monitors that could use this code? Then let them know.
Dave says
I’m still having trouble with 64bit excel even with the workaround. Instead of the original error message, I’m now getting a “Compile Error: Expected: string constant” error.
Any ideas how to fix this?
Philip Treacy says
Hi Dave,
Check the double quote marks around user32 in
If you copied/pasted this line the quotes are probably ‘slanted’ rather than ‘straight’ so delete them and retype in the VBA editor.
Let me know if this doesn’t fix it.
Regards
Phil
Niall says
You need to add PtrSafe to the Declare statement in the Macro to make it compatible with VBA7 on 64 bit OS versions of Office.
It should read:
Private Declare PtrSafe Function GetSystemMetrics Lib “user32” (ByVal nIndex As Long) As Long
Then works fine.
Thanks for this Macro. Huge time saver.
Niall says
I see this is dealt with in your Posts, however it is recommending LongPtr is included. I understand my version will work on both 64 bit and 32 bit versions
Private Declare Function GetSystemMetrics Lib “user32” (ByVal nIndex As Long) As Long
see http://www.jkp-ads.com/articles/apideclarations.asp
So it then becomes a non issue for any system using VBA7.
In fact to work in all cases including earlier versions of Office than Office 2010 you would have to write:
Philip Treacy says
Thanks again 🙂
Philip Treacy says
Thanks Niall.
Col Delane says
Hi Phil
I know Excel 2010 requires two separate instances of Excel to have two separate workbooks on two different monitors, but understand Excel 2013+ overcame this problem and can now be done with just one instance.
Does this VBA technique:
1. require all monitors to be the same size, or does the code work out which is the smallest and then just maximise to its extent?
2. only allow you to stretch the active sheet across all monitors, or to locate different open workbooks on different monitors?
Tx
Philip Treacy says
Hi Col,
The code takes its settings from your primary display. If the primary is a higher resolution than the secondary (and other) displays, then the secondary display only shows as many rows as it can. So if your primary display can show e.g. down to row Z, the secondary display will only be able to show down to row T (for example).
If your primary display resolution is lower than the secondary, then the secondary will be able to show more rows than the primary.
In both cases, the sheet is stretched across the full width of both screens, and the code acts on the active workbook. So you could have other open workbooks that are unaffected. This is in Excel 2013.
Cheers
Phil
lou says
hello jon,
i use excel 64 bits and get an error
compiler error
code of this project has to be updated for use on 64-bits systems
check the instructions, update them and mark them with characteristic PtrSafe.
can you help?
Philip Treacy says
Hi Lou,
You need to update the function declaration. I’ve added instructions to do this to the bottom of the post.
Regards,
Phil
Trevor R Bird says
An even simpler way to spread your Excel Workbook (or any other Windows application) across multiple screens is to use a utility called “Ultramon” from http://www.ultramon.com. This utility will stretch Excel across multiple screens, including those on the screens of other computers connected via a LAN using a utility like “SpaceDesk” from http://spacedesk.ph/.
This approach gives a view of columns A:BR across my 3 screens. The height of Excel is limited to the smallest height of the the connected screens.
UltraMon place a couple of icons on all (well almost all) Windows applications that allows the User to stretch the application across all available screens of to send the application to a selected screen. Of course, if you only want Excel on 2 of your 3 monitors you can manually stretch it as desired
Cheers
Philip Treacy says
Thanks for this Trevor.
Jon S says
Useful tips! I have 3 monitors, but my middle monitor is the primary. The macro successfully extends the screen to the right monitor fully! This is actually fine for me anyways. I had a colleague test the macro with 3 monitors having the left most monitor as primary and it successfully extended across all 3.
Philip Treacy says
Hi Jon,
Thanks for letting me know it works on the 3 screens.
Cheers
Phil
jomili says
I’m getting a compile error saying “The code in this project must be updated for use on 64-Bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.”
jomili says
I think I’ve fixed it. Changed the Private Declare statement as shown below.
Private Declare PtrSafe Function GetSystemMetrics Lib “user32” (ByVal nIndex As LongPtr) As LongPtr
Philip Treacy says
Hi Jomili,
Good to hear you figured that out
Regards
Phil
MarkCBB says
Nice post, thank-you
Philip Treacy says
Glad you liked it.
Cheers,
Phil