Auto resize an Excel spreadsheet to fill the screen

Excel Auto Resize

Every office I have ever worked in has been an equipment battleground.  It didn’t matter what the equipment was; it just had to be better than everybody else.  A new keyboard, a bigger screen, even a mouse mat with a wrist rest was enough to make everyone else jealous.  I was once lucky enough to be the recipient of a new, bigger screen.  Wow, what a day that was!  I set about creating bigger dashboards with more charts and tables, more space meant more insight, right?!? I was sure the praise would come flooding in.

But it wasn’t long until my new screen was causing me problems.  Actually . . . it was causing my managers problems and therefore causing me problems.  I had created a dashboard which fitted nicely on my new big screen, but my managers, had a mix of screen sizes.  Some lucky enough to have the same as me, while others had the older smaller ones.  Would the small screen managers have to scroll, or would the big screen managers have a lot of white space?  That was a tough choice.  It was a lose-lose situation.  But, I wasn’t about to be defeated . . .

Here are the four stages of my thought process:

  • Manually zoom in/out
  • Manually zoom to selection
  • Click a button to resize the spreadsheet
  • Auto resize on open

Let’s consider each of these one by one

Manually zoom in/out

There is a zoom setting on Excel, which each user can apply themselves.  This setting is available in three places:

(1) The Ribbon
The zoom setting is available on the View Ribbon.  View -> Zoom

Zoom setting within Ribbon

After clicking on the button, the Zoom window will appear.

Zoom settings window

There are multiple preset options, plus a Custom option to select any size between 10% and 400%.  Entering a custom size outside of this range will trigger the following error message.

Zoom size limits

(2) The Status Bar
The zoom setting is also available on the Status Bar

Zoom setting within Status Bar

The [+] and [-] buttons will move the zoom level by 10% increments.  By clicking and dragging the slider it is possible to get any size between 10% and 400%.

(3) Mouse scroll wheel

If you have a mouse with a scroll wheel (which most do) there is another option. Hold the Ctrl key while moving the scroll wheel backward and forward.  The spreadsheet will zoom in and out.

Whilst manually resizing the spreadsheet was an option, it wasn’t quite the seamless user experience I was looking for.

Manually Zoom to Selection

A slightly better option for this situation was Zoom to Selection.  The user selects the range of cells to fill the screen, then clicks the Zoom to Selection button.

View Zoom To Selection

This option also exists within the Zoom window, but for a reason which is unknown to me, it is called by a different name Fit to selection.

Zoom settings Fit to Selection

Now, we’re on to something. If we could automate this feature, it would be a pretty good solution.

Click a button to resize the spreadsheet

If you’re not a VBA-wiz, we can turn to the Macro recorder to automate the process of selecting a range then using Zoom to Selection.

You will need to use the Developer Tab, follow these instructions if you have not already enabled it.

Display the Developer ribbon

Right-click on a blank part of the ribbon and select Customize the Ribbon… from the menuCustomize Ribbon to display Developer Ribbon

From the Excel Options window click Customize Ribbon, enable the Developer option then click OK.
Display Developer Ribbon in Excel Options

Click the Record Macro button within the Developer Tab.

Select the range of cells then click View -> Zoom to Selection.

Click in Cell A1 to return the selection to a single cell.

Click the Stop Recording button from the Developer Tab.

Zoom to Selection Macro Recording

Next, create a button using Developer -> Insert -> Form Control -> Button (Form Control).

Assign the macro created above to that button.

Attach zoom to Button

Now it is possible to resize the spreadsheet to the window just by clicking the button.  That’s almost an acceptable solution, but we can go one step further.

100 Excel VBA Macros

Do you know the fastest way to learn foreign languages?  It is to read, write, speak, and think in that language as often as possible.  Apart from speaking, programming languages are no different.  The more you immerse yourself in that language, the faster you will pick it up.

100 Excel Macros Book

Therefore, what most people like you need is lots of examples that you can practice.  That is why the 100 Excel VBA Macros eBook exists.  It’s the book for all Excel users who want to learn how to read and write Excel macros, save time, and stand out from their peers.  The book contains:

  • 100 example codes to practice reading and writing macros that will embed the language into your thinking.
  • An introduction to macros in Excel to ensure you can implement the VBA code in the book even if you have no prior knowledge.
  • Consistent code layout between examples to enable you to understand the structure and easily customize the code to meet your needs.
  • Downloadable workbook containing all the source code, so the examples can be added to your project to give you the benefit of VBA straight away.

Auto resize on open

Finally, we get to the good stuff.  Let’s make the resizing happen automatically.

View the recorded macro code within the Visual Basic Editor (Developer -> Visual Basic Editor)

Developer Visual Basic Editor

The code will look something like this

Sub Macro3()
'
' Macro3 Macro
'

'
 Range("A1:I15").Select
 ActiveWindow.Zoom = True
 Range("A1").Select
End Sub

Copy that code, and insert it into the Workbook Open event for ThisWorkbook

VBE Zoom Code

Delete the recorded macro and the button, as we no longer need them.

Save the file as a macro-enabled workbook, the close it.

Whenever the file opens, the Workbook Open event triggers first, which resizes the spreadsheet to fit the window.  That’s a much better solution.



Get our FREE VBA eBook of the 30 most useful Excel VBA macros.
Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Email Address * First Name *

By entering your email address you agree to receive emails from Excel Off The Grid. We’ll respect your privacy and you can unsubscribe at any time.


Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

11 thoughts on “Auto resize an Excel spreadsheet to fill the screen

  1. Hugo says:

    Great idea!
    Managers have to keep satisfied, totally agree. 🙂

    Is it sure, that in the WB open macro the ActiveWindow is the right Window?
    WB open could happen in the background, may be not visible.
    What about the ActiveWindow in this case?
    And what about different sheets?
    I suggest to add a Sheets(“dashboard”).Activate before.

    • Excel Off The Grid says:

      Hi Hugo,

      Generally, when opening a workbook it will become the active window, so the code will work OK for most single page dashboards. Which was the scenario I had at the time.

      You are correct, this is not the best solution for a multi-tab workbook. Or for users who might try to open the file in a different way.

      Your suggestion would definitely work. Personally, I would probably put the code into the individual Worksheet_Activate() events to achieve a similar result.

      • Hugo says:

        Worksheet_Activate() is much more precise, you are right.
        Possible a disadvantage:
        Every time you activate the dashboard sheet, changed zoom will be reset and cell A1 is always selected.
        If this is ok, then your solution is perfect.

        • Excel Off The Grid says:

          I agree.

          We could get around the selection issue as follows:

          Inserting the following at the top:
          Dim selectedCell as Range
          Set selectedCell = Selection

          Insert the following at the bottom:
          selectedCell.Select

          What do you think?

          • Hugo says:

            I would not prefer this solution. Why?
            If I change to the dashboard sheet a range is always selected.
            My possible workflow would be:
            1. WB open –> the dashboard gets maximized to 100%.
            2. I move down on the sheet, add something in cells, change zoom….
            3. I select another sheet and come back to dashboard.
            4. And now?
            4.1 Option 1 -> Zoom to previous 100%. My actions on the bottom can’t be seen. The range is selected; I have to deselect.
            4.2 Option 2 -> Keep setting as it is. Continue to work where I left (I’d prefer).

            Conclusion: Solution depends on what you want.
            There’s not better or worse.

  2. Jaka says:

    Hello,

    I have a technical question… I have made a workbook with 10 spreadsheets, each with different column widths and common toolbar with links to spreadsheets. When I use Activewindow.Zoom = True for the first one (“homepage”) and apply it the the others (Activewindow.Zoom = iZoom), I notice that the toolbar is not on the same location (object/toolbar is locked – “don’t move or resize with cell”) . It doesn’t look professional at all…

    Do you know a quick fix? Have I missed something?

    Thank you for a quick reply, greetings from Slovenia,
    Jaka

    • Excel Off The Grid says:

      The toolbar isn’t resizing, and the cells aren’t changing size either. Instead, you’re zooming in/out of the spreadsheet.

      If your toolbar is made of shapes, then you could resize the shapes based on the zoom setting, therefore the toolbar would look the same size on all sheets.

      Excel’s zoom does not keep proportions perfectly when it reaches smaller sizes. Sometimes a cell and show ####### because the cell is too small to fit the contents. But when you zoom in or print it, you can see it.

Leave a Reply

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