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.

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.

32 comments

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

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

    • 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!

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

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

  • 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

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

  • 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

      • Hi Jon. I was wondering if a solution was found for the issue Owen Pearson was having with his “Picture 2”. I have tried your code and after pasting from the Immediate Window to Change_View, I get a Run-time error stating “The item with the specified name wasn’t found”. I get this error for linked pictures, and slicers. I have a text box and it finds it no issues.

  • 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

  • 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

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

  • Hi

    In Excel – what area does the active window cover?

    If i hide the tabs – scroll bar, tabs, ribbon etc – would the active window height and width be the same?

    The reason why i ask is because i want to use your code to resize or set a zoom but when i open it on my home laptop and work laptop – the view is different – So i was thinking of 1st hiding everything in the workbook open code – ensuring the window is maximised and then check to see what the height and width of the window is so that i can set it and vcompare it equally and see what the height and width it is (comparing to my work laptop, home laptop, desktop at work and also standard laptop screen size). Whats the best way to ensure im capturing the right height and width for the active window with everything hidden?

    With your code – it doesnt matter on the screen resolution but the height and width of the excel window so i want to ensure that it definitely fits within the screen sizes of my work desktop monitor, laptop at home, laptop at work and monitor at work. If doesnt fit within any of those sizes then set the zoom to 50

    • In Change_View, you can add to have message boxes thrown up with the window dimensions to get data for customization

      MsgBox (dWidth)
      MsgBox (dHeight)

      Because I have a workbook with several worksheets that I started using on workstations with different size monitors from where I first started developing it, I am putting his code in individual worksheets of interest and just having the functions zoom to different levels for now. Did not really want to mess with all my buttons.

      It was pretty neat to see his code populate the Immediate Window – never saw or used that before, but was able to find under View menu in the Visual Basic Editor.

      Thanks Jon for posting this on dashboards. I figured you are out surfing, so wanted to return the favor and try to answer this question.

  • Hi, this works fine when the workbook size changes, but I need also a solution if the application size is changing.

  • Hi Jon,

    thank you for the above ideas and something worthwhiling putting into practice

    I was wondering if you have come across a problem with resolution causing havoc with shape/textbox/graph layouts and whether you have found a solution for this?

  • Hello,

    I have a technical question… I have made a workbook with 10 spreadsheets, each with different column widths and common toolbar (horizontal group of text objects on the top of each spreadsheet with hyperlinks and macros). When I use Activewindow.Zoom = True for the first spreadsheet(“homepage”) and apply it to the others (Activewindow.Zoom = iZoom), it works great for my screen resolution but as soon as I change screen resolution or zoom level on certain spreadsheets, the toolbar “moves” – it is not on the same place (object/toolbar is locked – “don’t move or resize with cell”).

    Since I plan to offer my workbook to customers I want it to look as close as real toolbar.

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

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

  • Hullo thank you for the great concept, hopefully this site is still active, Im requesting if you could make a video for this Macro for responsive dashboard because have tried following the instruction and i failed to implement it on my dashboard.

    I really appreciate the knowledge.

  • I lock the cells, place my dashboard elements, and set my dashboard element properties to Move and Resize with cells. Then I have a macro that runs on WorkbookOpen and WidowResize that …
    Turns off screen updating
    Selects the cell range that contains the dashboard elements
    Call Application.CurrentWindow.Zoom TRUE
    Selects a cell behind a dashboard element
    Turns on screen updating

    Presto-chango the dashboard fits the available screen.

  • I’m unable to access the VBA code to make this work… The download option just provides me with Responsive-Dashboard.xlsx; a file format that is notably *not* macro-enabled. Can you please assist and send me the macro-enabled version, or at least the VBA code for it? Thank you!

  • thank you for this great work ! is it possible to have the XLSM file, the download button offer the xlsx without any code in it.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter