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.

Claim your free eBook


How to resize Excel sheet – 4 ways: manual & automatic

Auto resize excel sheet

This post looks at how to Resize an Excel sheet. It’s a common requirement for users and is especially useful when creating dashboards for different-size monitors.

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 that fitted nicely on my new big screen, but my managers had a mix of screen sizes. Some were 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…

This post details the options for resizing and Excel worksheet, and how to make it happen automatically with a VBA macro.

Manually zoom in/out

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

View ribbon

The zoom setting is available on the View ribbon. Click View > Zoom.

Zoom setting within Ribbon

After clicking the Zoom button, the Zoom dialog box appears.

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 triggers the following error message.

Zoom size limits

Status bar

The zoom setting is also available on the Status Bar.

Zoom setting within Status Bar

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

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 zooms in and out.

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

Zoom to Selection

A slightly better option for my situation was the Zoom to Selection feature. The user selects the range of cells to fill the screen, then clicks View > Zoom to Selection.

View Zoom To Selection

This option also exists within the Zoom dialog box, but for reasons 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.

Button click to resize Excel sheet

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

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.

Auto-resize Excel sheet 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 by clicking Developer > Visual Basic Editor, or press Alt + F11.

Developer Visual Basic Editor

The code looks 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 the ThisWorkbook module

auto resize Excel sheet

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

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

Whenever the file opens, the Workbook Open event triggers first, which resizes the spreadsheet to fit the window.

But, we have an issue with the code, it only refers to the ActiveWindow. What if we have multiple reports, or the workbook is not saved with the report as the selected window?

Let’s update the code to make it work with any sheets that we want.

Private Sub Workbook_Open()

'Select and Resize Sheet2
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1:O23").Select
ActiveWindow.Zoom = True

'Select and Resize Sheet1
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1:I15").Select
ActiveWindow.Zoom = True
Sheets("Sheet1").Range("A1").Select

End Sub

Using the code above as an example, add as many sheets as required for the scenario.

Conclusion

Excel is a very flexible tool, it is used for more purposes than I can imagine. Therefore we often need to turn to VBA macros to fine-tune the user experience. In this post, we saw the process of how to auto-resize an Excel sheet to fit the user’s screen.

Related Posts:


Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).


Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match 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:

Leave a Reply

Your email address will not be published.