How to Make Your Excel Dashboards Resize for Different Screen Sizes

Bottom line: Learn how to make your Excel dashboards automatically resize for different screen sizes with VBA macros.

Skill level: Advanced

Are Your Users Viewing Your Dashboards on Different Screen Sizes?

This was an issue for my friend Ryan (LinkedIn). Ryan has an Excel file that contains a dashboard, and he sends this file to a bunch of different vendors (users).

Each user is opening the file on a device with a different screen size. This could be anything from a small laptop to a large monitor. He wants to make sure that the user can see all the elements of his dashboard on one screen, without having to scroll horizontally or vertically.

Design Dashboards to Fit On One Page

Most good dashboard design books and courses will tell you that it is best to fit all the elements of your dashboard on one page/screen. This prevents your dashboard from getting too cluttered. It also allows the user to see relationships between the data on the different charts without having to scroll horizontally or vertically.

Modern devices come in just about every screen size you can imagine. It won’t be long before we are looking at Excel dashboards on our watch and our Hololens. So how do we accommodate for all the various sizes?

The Responsive Excel Dashboard Macros

I wrote this set of macros to help solve this problem. The macro allows you to easily create different “views” for different screen sizes, and then automatically apply those views when the user opens the file or resizes the workbook window.

Here it is in action (view in your web browser).

Download the File

You can download the file by clicking the link below. The file also contains instructions on how to use the macro.

This examples uses the dashboard I created in my free video series on pivot tables & dashboards.

How Does the Responsive Dashboard Work?

For this to work you first have to create “view macros” of the dashboard for different size screens. You manually setup the views by moving and resizing the elements on the dashboard.

Then create a “view macro” that contains the size and position properties for all the shapes on the page (charts and slicers are considered Shape objects in VBA). The file contains a “snapshot macro” that will create all this code for you.

When the user opens the file or resizes the workbook window, a macro runs that calls one of the “view” macros depending on the workbook’s window size. The “view” macro moves and resizes the dashboard elements to fit on the page (or based on how you setup the view).

How Do I Set It Up?

Step #1 – Modify the Dashboard

The first step is to create different views or layouts of your dashboard for different screen sizes. You can do this by adjusting the size of the Excel window to match one of you user’s screen sizes. Then manually move and resize all the elements (charts, slicers, shapes) to fit on the page.

Step #2 – Take A Snapshot of the Dashboard

Once you have it laid out just the way you want it, run the “Snapshot_View” macro. This will loop through all the shapes on the dashboard sheets and print out their size and position in the Immediate Window. It outputs the exact code you can use in a macro to create this view.

Step #3 – Create a “View” Macro

You then copy/paste this code to a “View_viewname” macro. You can rename these macros to describe the name of the view size. In this example I have four different View macros: View_Laptop_H720px, View_Small_Laptop, View_Tablet, View_Narrow

When one of these macros is run, it will move and resize all the elements on the dashboard to the layout I recorded with the “Snapshot_View” macro.

Step #4 – Modify the Change_View Macro

These views can then be used in the “Change_View” macro. The “Change_View” macro will call one of the View macros based on the height or width of the workbook window (ActiveWindow.Height and ActiveWindow.Width).

The “Change_View” macro is called by the Workbook_Open and Workbook_WindowResize events. So if the user opens the workbook on a different screen size or resizes the workbook window, the dashboard will resize accordingly.

Important Notes & Limitations

One limitation of this solution is that you have to know the approximate screen sizes of all your users. This isn’t too difficult to figure out. You might just want a few different views. One for the approximate size of a laptop screen, one for a large monitor, and one for a narrow view where the user has the dashboard in a smaller section of the screen. This last view would allow for vertical scrolling.

Another limitation is it doesn’t account for the fact that the user might zoom in/out on the dashboard by changing the window zoom in Excel. This can be accounted for in the code by first setting the zoom to 100% before the macro runs.

This solution also requires you to manually setup each view by moving and resizing charts and shapes. The major advantage to this is that you can control exactly how the chart will look. I think this is important because the labels on the charts can easily get distorted when the chart is resized.

Alternative Solutions

One alternative to this entire solution is to assign each shape a name that would represent it’s size property. Then write code that would automatically resize each shape based on it’s name. You could have elements that span 100%, 50%, 25% of the page, etc.

This would be similar to how responsive webpages are coded with CSS rules. Most modern websites are coded in a responsive manner. This means when you resize your browser window to be smaller/larger, the elements on the page also resize automatically. This prevents horizontal scrolling, and usually makes the page longer with vertical scrolling. I have another macro solution that does this, but it tends to create a lot of issues because the labels on the charts can get distorted and not display how you intended.

What Do You Think?

I’m curious to know if you have any suggestions on how to solve this issue or improve on it. Please leave a comment below with any questions or suggestions.

Also, leave a comment if you want me to create a video of how this works. Thanks! 🙂

How Do I Learn Macros & VBA?

If you are interested in learning macros & VBA then checkout my free video training series. In this series I cover the basics of writing your first macro and automating common Excel tasks.