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.
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
Enter your email address below to download the sample workbook.
Download your own copy of the code in this macro enabled workbook.
Do you know someone with 8 monitors that could use this code? Then let them know.