Master Excel with my tips, tricks & tools!
Subscribe to my free email newsletter
& receive a Free Gift!

Zoom on Excel Charts

When you have a dashboard with small panel charts it is nice to be able to zoom in on the charts to see the trends better.  The ‘Zoom_Chart’ macro included in the workbook allows you to add a zoom button (shape) over the top left corner of the chart to zoom in on the chart.  It’s available for free download below.  The macro actually resizes the chart to enlarge it, then returns it to it’s original size when the zoom button is pressed again.  See the animated screen capture below.

Zoom on Excel Charts

Zoom on Excel Charts with VBA Macro

You can control the zoom amount by changing the percentage values for Zoom Width and Zoom Height in cells P5:P6 on the worksheet.  These can also be hardcoded in the code if you don’t want the user to change the size of the zoom.

Video Overview & Basic Tutorial

Implementation

The video above contains a detailed tutorial on how to add the zoom buttons to your own workbook.  It’s really a matter of copying and pasting the macro (VBA code) and buttons into your workbook, and then assigning the macro to the buttons.  You should still be able to implement this even if you aren’t familiar with VBA or macros.  It is important to line up the buttons on the chart correctly and give each button a unique name.  So it’s best to watch the video to make sure you don’t miss anything.  The steps covered in the video are listed below.

The zoom feature works really well with dashboards and reports where your screen area is limited.  If you would like to learn more about charting and dashboards I highly recommend the dashboard course from My Online Training Hub.  Checkout my full video review of the course and learn how to become an Excel Superhero! :)

Installation Guide

  1. Open the Zoom on Charts workbook and your workbook.
  2. Open the VBA Editor and drag & drop the code module into your workbook.
  3. Save your file as a macro enabled workbook.
  4. Copy the zoom button into your workbook.
  5. Place the zoom button on the top left corner of a chart.
  6. Assign the macro to the button.
  7. Change the zoom references in the code.
  8. Duplicate the zoom button.
  9. Give the button a unique name.

Enhancements

The VBA can be further enhanced to add data labels, legends, axis labels, and any additional chart components when the zoom in button is pressed.  Please leave a comment with some of the enhancements you made, or would like to see.

Download

Zoom on Excel Charts.xls (81.0 KiB)

Zoom on Excel Charts - Bottom Right.xls (82.5 KiB)

The “Bottom Right” file contains modified code that allows you to place the zoom button in the bottom-right corner of the chart.  The chart zooms from the bottom-right and expands up and to the left.  Zooming from the top-left or bottom-right are the only two options for button placement with this code.

Questions

Please leave a comment below with any questions or modifications you have made.

Come Learn Excel With Us (it's Free!)

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "hey, how did you do that?"

You will also received email updates with new blog posts and videos to help you save time everyday and learn Excel.

CLICK HERE TO DOWNLOAD MY FREE EBOOK

42 Responses to Zoom on Excel Charts

  1. yuhanna says:

    jon,

    i was the one that requested this function but i have a problem with the zoom icon? where did you get the icon? also, i would like the ability to add data labels and chart components. I am not a vba expert but need your help since i think this is an excellent tool especially for someone like me who do alot of charts for analysis.

    if you can provide me a video tutorial, i would appreciate it.

    thanks

    • Jon says:

      The icon is actually a circle shape that contains text. The text is the “+” and “-” sign. So you can actually change all the properties of the shape (color, size, text, font, etc.) to suit your needs. Is the problem with the icon just the look of it?

      I will create a video tutorial and add the additional functionality.

      Thanks again,
      Jon

  2. yuhanna says:

    Jon,

    the code you gave me for the zoom function, when i copy and paste it in the visual basic editor and changed the name of the button, i get an object error message. all i did was copy and paste the exact same code in the specified sheet where the charts are located. I did not create a module and copy and paste it in there

    please let me know what i need to do to fix it. i am trying to learn VBA.

    thanks

    • Jon says:

      The code needs to be pasted in a module in the workbook. To create a module, select the VBAProject for your file in the Project Explorer window on the left side. Right click it and select Insert > Module. Double click on the newly created module to open it, and paste the code there. Then you will need to assign the zoom buttons to the newly created macro. Right click on the button and select Assign Macro… Select Zoom_Chart from the list.

      I’m working on creating an instructional video for implementing this.

      Thanks

  3. winston snyder says:

    Jon,

    Well done!

  4. Raghu says:

    Excellent. I love this feature.

    Thank you.

  5. Jon says:

    Thanks Winston & Raghu! I’m glad you found it useful.

  6. It’s beautiful. Works perfectly! Thanks Jon.
    Wanted to check, if there is a way to zoom it in any direction that we want?
    For eg: Some times the graphs may be at the extreme right side, and hence the chart may zoom, but to the left side. Also, the zoom button may need to move to the top left hand corner of the zoomed graph.

    Thanks

    • Jon says:

      Thanks Sriram! Zooming to the left is a little more complicated, but possible.

      The macro uses the “TopLeftCell” property of the chart object to determine which button is being pressed on the sheet. Unfortunately, there is no “TopRightCell” property to do the same procedure for the top right cell. If there was, then we could use that and change the scalewidth method to scale from the bottom right.

      There are two possible workarounds that I can think of:

      1. Create a unique copy of the Zoom_Chart code, modify it to zoom to the right, and assign it directly to the button. In the new copy of the code you would not need to find the cell (top left) that the chart and button intersect. Instead, you would just reference the name of the chart in the code. The button could then be placed anywhere on the chart or outside of it. The only drawback to this is that you have to have a unique code procedure for each button. It really won’t add much size to your file, just more code to maintain.

      2. You could also make the sheet scroll to the right when the button is pressed by using the application.goto method to move the horizontal or vertical scroll to the right. This is probably not the best method because you will be scrolling the other charts off the screen.

      My explanations above assume that you have some knowledge of modifying the code. Let me know if you are interested in either of these solutions, but not sure how to implement it. I’d be happy to help.

      Thanks,
      Jon

  7. Darren says:

    Hey Jon, your video instructions are indeed fantastic! Great job!! And most of all thanks for the valuable contribution to the excel community, at least for me it’s definitely valuable.

    However I would like to ask: If I have varying sizesof charts in a workbook, that means the chart magnification will differ. How do I assign an additional macro to those different charts?

    Darren

    • Jon says:

      Thanks Darren! I appreciate the comments and kind words, and I’m really glad you’re finding this information useful.

      There are a few ways you could accomplish the issue of having different size charts. It will probably depend on how many different sizes of charts you have. If it is just a few different sizes, then the easiest solution would be to duplicate the macro code and rename it. You could specify the zoom size in each macro, and then assign the buttons to the different macros based on the size of the chart.

      For example, let’s say you have 3 different size charts in your workbook: small, medium, large. We will need to create 3 different macros for each of these.

      1. Open the VBA editor (Alt+F11) and double click the m_ZoomCharts module to open it.
      2. Copy all the code from “Sub Zoom_Chart” to “End Sub” (including those two lines).
      3. Paste the text below the “End Sub” line at the bottom of the module.
      4. Rename the macro from “Zoom_Chart” to “Zoom_Chart_Small”.
      5. Change the zoom amounts in the “Zoom_Chart_Small” macro to the zoom amount for the small charts.
      6. Go back to your workbook and assign the “Zoom_Chart_Small” macro to the zoom buttons in the small charts.
      7. Repeats steps 2-6 above for the other chart sizes.

      An alternative method would be to modify the single procedure (macro) with a select statement that sets the zoom amount based on the button name. You would then change the button name based on the size of the charts (small, medium, large). This would require you to modify the code and possibly debug it, where the 1st suggestion above is basically copy/paste and rename.

      Let me know if you have any questions on implementing this.

      Thanks again,
      Jon

      • Darren says:

        Good morning Jon!

        Yes it does work, and I’ve managed to change it based on your instructions.

        Once again, I’m sure the good things which worked for people and helped them from the time you’ve invested to share these cool stuffs will come back to you many times greater!

        Have a great day Jon :)

        Darren

        • Jon says:

          Comments like that keep me fueled to continue learning and sharing my knowledge. Thanks again Darren! I’m glad it worked for you. Hope you have a good one too. :)

  8. Sonu says:

    hi Jon,

    thanks for such a good trick.

    I followed all the steps you described in the above video still i am not able to zoom my chart.while debuging i can facing yellow color on this line

    strButtonName = Application.Caller

    please advise.

    Thank you

    Regards
    Sonu Monga
    Chartered Accountant

    • Jon says:

      Hi Sonu,

      This error typically means that the top left corner of the chart and the button are NOT intersecting in the same cell. This is a critical step for this macro to work properly, and I should probably add some detail to the written instructions in the article above.

      The best way to check is to select the cell behind the top left corners of the chart and button. In the image below, if I select cell B5, I can not see the left edge of the bounding box around cell B5. This means that the chart is actually intersecting cell A5 in its top left corner. So you have to move the chart and the button until you can see the bounding box in the selected cell behind the top left corner of both objects.

      Move Chart and Zoom Button - Top Left Corner Intersects Same Cell

      Let me know if you have any additional questions.

      Thanks,
      Jon

  9. Michael Pennington says:

    Jon,
    This is great stuff. Thanks so much for taking the time to put this together and share it. I stumbled on the link for your site from Mike Alexander’s website and I am looking forward to exploring it.
    Michael

    • Jon says:

      Thanks Michael! I’m really glad you are enjoying it. Sign up for the free newsletter to receive emails for new blog posts.

      Thanks again,
      Jon

  10. Diana says:

    Jon,
    I downloaded your sample file, copied the module to my dashboard file, and after 2-3 modifications to your code, voila! I was able to zoom on a textbox in a new dashboard I’m building. I put help information in the textbox and now users can click the +/- to zoom in/out on the ‘help’ box. Thanks so much!
    Diana

    • Jon says:

      Hi Diana,

      Awesome! That’s a great adaptation for this code. I’m curious to know what happens to the text when the help box is zoomed out. Does it show less text, then when you click the zoom button the entire text is displayed?

      Thanks,
      Jon

      • Diana says:

        The way I have it, the box is so small to start that I can see about 4 words (“HELP (next line) Select a client” and then when you zoom, it’s about 700% larger and you can see all of the notes I’ve entered. The font is the size I’ve selected for the box but zooming makes it large enough to read all of the text in the box.

        I uploaded a graphic so you can see what it looks like. http://sdrv.ms/1cHouEz

        Thanks again!

        • Jon says:

          I really like that! I’ve tend to use shapes or cell comments for help menus. When I use shapes I will typically have a toggle button that shows/hides the shapes when pressed. Your solution gives it more of a “wow” factor, and also allows you to easily expand one shape at a time. Thanks for making the graphic and sharing it!!!

  11. ezra says:

    Hi JOn,

    Excellent work! it helps me a lot. the video is really clear for understanding.

    However, the double right click for copying icon seems like doesnt work for me.

    I notice the second right click in your video showing the “edit text” & “Edit point”. But I dont have that with me.

    Can you tell me how to call that out?

    Thanks
    Ezra

    • Jon says:

      Hi Ezra,

      This step is a bit tricky. You actually need to right click on the edge of the zoom button shape. This can be difficult to do because the shape is very small.

      An alternative to this is to hold down the Ctrl key and Right-click on the shape. That will show you the properties to edit the shape, instead of the text inside it.

      Let me know if this helps.

      Thank you for the comments!

      Jon

  12. Matt says:

    Hey Jon,

    This is an awesome instruction video! I really want these boxes in which you can enter your zoom value though. I kinda suck at programming. Is there an easy way to implement these boxes in my own worksheet?

    Thank you sir

  13. ezra says:

    Hi Jon,

    I still have some minor problem with the button. It doesn’t work accordingly. Can you have a look with the file and tell me why it doesn’t work in some of the chart?

    https://www.dropbox.com/s/l6fw4kc1an1pgcn/Test%20Button.xlsx

    Thanks
    Ezra

    • Jon says:

      Hi Ezra,

      Thank you for supplying a sample file, this makes it much easier to find the problem.

      The problem is that each chart in the worksheet must have a unique name. Four of the charts on the worksheet currently have the same name (Bitumen Northern, Cement Usage Northern, Logistic Northern, Agg Process). Each chart name must be changed to a unique name, just like the zoom button.

      I was not aware of this issue, and I am glad you found it!

      Thanks again and let me know if you have any questions.

      Jon

  14. Brad Edgar says:

    Hey Jon,

    This is awesome! I think this is especially useful when you have a ton of different charts built into a dashboard. Often times I want to include multiple charts in one dashboard but can’t seem to fit everything within one screen.

    This should help. Thanks for taking the time to share. Next time I need this code I’ll pop by the website to pick it up.

    Cheers,

    Brad

    • Jon says:

      Thanks Brad! I have the same issue with trying to cram everything into one screen sometimes. Especially with the use of sparkline or micro charts.

      Let me know if you think of any ways it could be enhanced.

      Jon

  15. Late to the party, but:

    TopRightCell = Intersect(TopLeftCell.EntireRow, BottomRightCell.EntireColumn)

    I’m thinking you could avoid the issues with finding cells and charts with the same name by putting the shape into the chart. However, the shape doesn’t seem to know which chart it is embedded in. It thinks its parent is the worksheet.

  16. Of course, you could use the mouse_down event, detect if the shape is clicked on. Then if the shape’s text is “+”, make the chart larger and change “+” to “-”; if the text is “-”, shrink the chart and change “-” back to “+”.

    The shape doesn’t seem to respond to the mouse_up event, at least not in 2013.

    • Jon says:

      Hi Jon,

      Great suggestion on the Intersect method to find the top right cell! That will come in handy.

      The mouse_down is an interesting alternative, and has got me thinking about other things you could do with it.

      Thanks for the suggestions!

  17. mark anderson says:

    I have been trying to find something similar to this but for it to be used upon images, could this type of macro be converted to work with images, and if so what would be required?

    • Jon says:

      Hi Mark,

      Great question! I updated the code to work with all shapes including images. You can download the latest version above.

      For those that want to know more details, I simply replaced the word “ChartObjects” with the word “Shapes” in the code. Charts actually reside inside a shape, so the code will now work with other shapes besides just charts.

      Please let me know if you have any questions.

      • Hmm, that reminds me, since a chart is encapsulated within a shape, you can assign the resizing macro to the chart object. You can place a shape in the corner of the chart so the user knows they can click the chart to resize it, and you can toggle the shape between “+” and “-”. Since Application.Caller passes the name of the shape (chart object), not the shape itself, you need to make sure that there are no shapes with duplicate names.

        And one problem with using a shape in the chart to trigger the resizing, even if you work out the mouse events, the events do not fire until the chart is active. This would require two clicks, one more than a good design would need. (And that’s a lesson that Microsoft totally forgot while designing Excel 2007.)

  18. Rod Eatwell says:

    Two charts worked fine, but when I added a third chart if I clicked on first Chart or 3rd chart they were both affected by the one zoom button. If I clicked to zoom in on 1 it worked but when I clicked on it to zoom out Chart 3 got smaller than Normal and chart one stayed expanded. click one again and the both returned to normal, and the same thing happened in reverse with 3 & 1 charts

    • Jon says:

      Hi Rod,

      That can happen when the zoom buttons share the same name. Each zoom button must be given a unique name. Checkout step 9 (8:30) in the video and please let me know if you are still having any issues. Thanks

  19. Rod Eatwell says:

    I had Copied your Button & Pasted it and changed the Name and that is what happened.I have Created My own Button and it is working ok. Thanks for your Quick reply

  20. NARAYAN says:

    Hi Jon ,

    Thanks for sharing the concept and the code.

  21. Colin Allen says:

    Really useful. I was having problems selecting the button and with renaming them consistently. I found going to Find & Select…Selection Pane… made it much easier to select the button, just highlight in the list then Ctrl+D to duplicate and also made naming mistakes easy to spot.
    I also had problems aligning the button and chart, I switched on Snap to Grid thinking it would make things easy but the button never worked using this method.

Please leave a comment