How to Make Your Excel Dashboards Resize for Different Screen Sizes - Excel Campus
20

How to Make Your Excel Dashboards Resize for Different Screen Sizes

Resize Excel Dashboards to Fit On Different Screen Sizes - Responsive

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.

Dashboard Elements Should Fit on One Page - No Scrolling

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

Resize Excel Dashboards Based on Screen Size - Responsive GIF

Download the File

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

Responsive Dashboard.xlsm (371.1 KB)

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 the Responsive Excel Dashboard Macro Works

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.

Snapshot_View Macro for Responsive Excel Dashboard

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.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 20 comments
Seshu - September 11, 2017

Hi Jon,

Could you please send me that fit to different screens code to me as I’m working on a dash board. And need small advice that one excel can be used by 500 people at a time in readonly. .. if posible please share the way.

Reply
Christian - October 17, 2016

Hi Jon,

Have you consider making a video about how to modify this template?

Reply
H - October 4, 2016

Hi Jon,

I have the same issue where i have a few sheets on my dashboard workbook. Not everything is in a shape and are im cells.

Now i want to be able to create a responsive dashboard and be able to view it exactly the same on the other type of monitors – typically it will opened on a projector , laptop or 19″ monitor

Now how can i get it to be viewed exactly the way i created it?

It would take forever to resize everything on 1 monitor for each sheet and then open the workbook again in another size etc..

Is there a work around this and can i send you my workbook for you to lool at?

Thank you

Reply
Oleg - March 23, 2016

Hi Jon,

This is brilliant piece of code, Thank you for sharing it.

I am running in to a problem, where I maximise my workbook and start to resize window of Excel (with my workbook in it), it does not change anything.

whilst if I keep excel maximised and will start amending the workbook window (inside of Excel application) it works perfectly.

Am I missing something here?

Once again thank you for a brilliant lesson and looking forwards to your reply

Oleg

Reply
Owen Pearson - February 17, 2016

Great tool and idea. I am running into an issue though, when I try and open the file, it runs the Change_View script and finds an error in the first line of “Sub View_Laptop_H720px()” with “.Shapes(“Picture 2″).Top = 8.025826”.

Any suggestions about this? Seems to be saying that it cannot find “Picture 2” although I have copied it from the Immediate Window after running the Snapshot_View Macro.

Would love to get this to work!
Many thanks,
Owen

Reply
Josh - January 10, 2016

Hi Jon,

This is a very neat workaround, which I think I will use in order to adapt to the various resolution settings of my clients’ monitors.

If I have text/formulas that I would also like repositioned accordingly, do I need to do anything different in the code?

Thanks for your support,

Josh

Reply
    Jon Acampora - January 22, 2016

    Thanks Josh! The code is only going to move and resize shapes. So you might want to put the text or formula results in a text box, if possible. A lot will depend on how many cells of data your dashboard contains. You make a text box shape display the contents of a cell by selecting the shape, then typing a formula in the Formula Bar that references the cell.

    So if cell A2 contained data you wanted to display in the dashboard, create a text box shape, then in the formula bar you will put the formula: =A2

    The only issue is that the text box is going to get resized, which you might not want. You could modify the code to move the textbox, but not resize it.

    Let me know if you have any questions.

    Reply
Alvaro Fagundes - October 21, 2015

To me appear this error… Anyone can explain why?

http://postimg.org/image/mjjhxfwbd/

Reply
Chaminda - September 11, 2015

Hi,

I like your approach and solution, if you could make a Video like you did for “Zoom on Excel Charts” , it would be a greater help.

Thank you for sharing your innovations with us.

Reply
Jamil M - July 22, 2015

Jon,
i like your approach rather than Jordan’s Zoom solution

Reply
    Jon Acampora - July 22, 2015

    Thanks Jamil, I think it’s just good to know that both solutions exist. Which one you use will depend on what outcome you are trying to achieve.

    Reply
Jon Acampora - July 21, 2015

Checkout this article by my friend Jordan Goldmeier for a simple solution using zoom. The difference is that his solution zooms in/out on the entire page, making all the elements larger/smaller.

My solution above moves and resizes each element to fit on the page and ensure readability on different screen sizes.

Reply
Jana Musselwhite - July 21, 2015

I really like your macro set for “How to Make Your Excel Dashboards Resize for Different Screen Sizes”. Thanks for sharing and I will definitely give it a go. I would really love a solution to having to re-size everything on a sheet for each view, though. Several of my documents have multiple sheets and setting this up will take some serious time and thought. I’ll let you know how it turns out. (I hope I can as questions if I run into a glitch).

Reply
    Jon Acampora - July 21, 2015

    Thanks Jana! Yes, the setup work is definitely a little labor intensive depending on how many screen sizes you want to create views for, and how many sheets you have. Please leave another comment with any questions. Thanks again!

    Reply

Leave a Reply:

Free Webinar: The 5 Secrets to Understanding Pivot Tables

x