My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Maximize Excel Across Multiple Screens

You are here: Home / Excel VBA / Maximize Excel Across Multiple Screens
maximize Excel across multiple screens
by

If you want to maximise Excel on a single monitor you can just click on the maximize button

maximize window 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.

Excel Toolbars and Ribbon Positions

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.

By submitting your email address you agree that we can email you our Excel newsletter.

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

Do you know someone with 8 monitors that could use this code? Then let them know.

maximize Excel across multiple screens

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Excel VBA Posts

macro to center across selection
Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category:

Reader Interactions

Comments

  1. Dave

    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?

    Reply

    • Philip Treacy

      Hi Dave,

      Check the double quote marks around user32 in 

      Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As LongPtr) As LongPtr

      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

      Reply

  2. Niall

    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.

    Reply

    • Niall

      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

      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:

      If VBA7 Then

Private Declare PtrSafe Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

Else

Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

End If
      Reply

  3. Col Delane

    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

    Reply

    • Philip Treacy

      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

      Reply

  4. lou

    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?

    Reply

    • Philip Treacy

      Hi Lou,

      You need to update the function declaration. I’ve added instructions to do this to the bottom of the post.

      Regards,

      Phil

      Reply

  5. Trevor R Bird

    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 https://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 https://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

    Reply

  6. Jon S

    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.

    Reply

  7. jomili

    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.”

    Reply

    • jomili

      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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *