64

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 (82.9 KB)

Zoom on Excel Charts - Bottom Right.xls (84.5 KB)

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.

Please share this post:
facebooktwittergoogle_pluslinkedinmail
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 64 comments
P.Manibalan - August 23, 2015

Hi John,

Thank you for sharing this amazing Idea***…… You are the Excel Rock Star**.

Reply
Kat Linden - January 7, 2015

Thank you for your help, John. I added the line for the x-axis to the code like this:
‘You can make changes in the following section for zoom button text and zoom amount.
‘———————————————————————————–
‘The button is a toggle and text in the shape changes after it is clicked.
‘The text in the shape can be changed below.
strZoomInText = “+” ‘<-THIS CAN BE CHANGED
strZoomOutText = "-" '<-THIS CAN BE CHANGED

'ZOOM AMOUNT
'Change the Zoom IN width and height below. Amounts are multipliers,
'for example ZoomInWidth of 5 = 500% increase in size, 0.5 = 50% increase
dZoomInWidth = 2.5 '<-THIS CAN BE CHANGED
dZoomInHeight = 1.5 '<-THIS CAN BE CHANGED
dOutWidth = 1 / dZoomInWidth 'zooms back to original size
dOutHeight = 1 / dZoomInHeight
ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 20
'———————————————————————————–

but I'm getting an error message saying "Run-time error '91': Object variable or With block variable not set.

What am I doing wrong?

Reply
Kat Linden - January 6, 2015

Hi John,

I’m using your macro with a couple of line graphs and it works great. The only thing I’d like to add is for the axis labels to get bigger as well as the charts. Is there a way to do that?

Thank you!

Reply
Cameron - January 4, 2015

Hi John,
Thank you for the quick reply! It seems that the macro sometimes changes the TopLeftCell value of the chart if the chart was aligned with snap to grid.

Troubleshooting example:
?ActiveSheet.ChartObjects(“Chart 15”).TopLeftCell.Address
$I$5 (Before macro)
$I$4 (After macro)

However, one workaround is to:
1) Align all charts with snap to grid on
2) Turn snap to grid off
3) Move all charts down one increment with the arrow key.

Reply
Cameron - January 3, 2015

Hi John,
Thank you for sharing this amazing trick! It works great unless snap to grid is on. Do you know what code change is needed to account for using snap to grid?

Thanks

Reply
    Jon Acampora - January 4, 2015

    Hi Cameron,

    It should work with snap to grid on, but it might be that the chart and zoom button are not aligning in the same cell because of snap to grid. You can temporarily turn snap to grid off while aligning the chart and button, then turn it back on. Let me know if that helps. Thanks for the question!
    Jon

    Reply
Riyas - October 27, 2014

Hi John,

Thanks for your reply. Awaiting your valuable inputs.

Thank you

Reply
Riyas - October 27, 2014

Hai John.

Great Work. It’s working perfectly. i can manage Lot of Chart in a single sheet. i requesting your help to locate the Zoom button in Top Right, Left Bottom, Top Centre and Bottom Centre. Could you please suggest where i need to change the programs.

Awaiting you reply.

Reply
    Jon Acampora - October 27, 2014

    Hi Riyas,
    I’m glad to hear it’s working for you. That’s a great question and I’m working on a way to make the code more flexible so you can put the zoom button anywhere in the chart. I will let you know when I have a solution.
    Thanks!

    Reply
Kathy Smith - October 22, 2014

I know you are mostly an excel Guru, but is there a comparible VBA code that will let this work with PowerPoint that you know of?

Reply
    Jon Acampora - October 22, 2014

    Hi Kathy,
    That is a great question. This code can be modified for PowerPoint. I will convert it when I get a chance. Maybe someone out there will take on the challenge and beat me to it. :-)

    The other option that might be easier to implement in PowerPoint is to use hyperlinks. You could add slides to the end of your presentation that contain the enlarged charts. Then create a “zoom button” and place it on the small chart in the presentation. Create a hyperlink on that button that links to the slide containing the enlarged chart. On that slide create a “zoom out” button that contains a hyperlink back to the slide that contains the small charts.

    It might not be as elegant, but it would be a lot easier to implement and not require any macros.

    Let me know if that makes sense. Thanks for the question!

    Reply
      Kathy Smith - October 23, 2014

      Thanks Jon,

      I will try that and see what happens. Thanks for the quick response.

      Reply
vikram - October 14, 2014

hi jon,

thanks for sharing the idea, I just loved the idea. can you post the link, on how to implement the changes to vba module for new zoom button.

thanks in advance

Reply
    Jon Acampora - October 22, 2014

    Hi Vikram,
    You shouldn’t have to change anything in the code for a new zoom button. Just duplicate the button and make sure it has a unique name, then assign the Zoom_Chart macro to it. The following line of code will determine which button was pressed, and zoom on the intersecting chart.

    strButtonName = Application.Caller

    Please let me know if you have any other questions.
    Thanks!

    Reply
Colin Allen - July 22, 2014

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.

Reply
Namrata - June 3, 2014

Hi Jon, i tried this code of yours and everything works well except that there is error below:

Set rngZoom = Range(ActiveSheet.Shapes(strButtonName).TopLeftCell.Address)

Pl. see whats wrong with it.

Thanks

Reply
    Jon Acampora - October 22, 2014

    Hi Namrata,

    The button needs to be placed outside of the chart. If you select the button and the chart border is also selected, this means the button is residing inside the chart. Cut the button and paste it outside of the chart. Then move the button over the top of the chart.

    Hopefully this helps. Please let me know if you have any other questions. Thanks!

    Reply
NARAYAN - April 10, 2014

Hi Jon ,

Thanks for sharing the concept and the code.

Reply
Rod Eatwell - March 22, 2014

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

Reply
Rod Eatwell - March 22, 2014

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

Reply
    Jon Acampora - March 22, 2014

    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

    Reply
mark anderson - February 21, 2014

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?

Reply
    Jon Acampora - February 21, 2014

    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.

    Reply
      Jon Peltier - February 21, 2014

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

      Reply
Jon Peltier - February 19, 2014

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.

Reply
    Jon Acampora - February 19, 2014

    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!

    Reply
Jon Peltier - February 19, 2014

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.

Reply
Brad Edgar - October 19, 2013

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

Reply
    Jon Acampora - October 20, 2013

    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

    Reply
ezra - October 6, 2013

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

Reply
    Jon Acampora - October 9, 2013

    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

    Reply
Matt - September 18, 2013

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

Reply
    Matt - September 18, 2013

    I already found it Jon, never mind. Excellent work! Thank you.

    Cheers,

    Matt

    Reply
ezra - August 20, 2013

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

Reply
    Jon Acampora - August 20, 2013

    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

    Reply
Diana - August 5, 2013

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

Reply
    Jon Acampora - August 5, 2013

    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

    Reply
      Diana - August 6, 2013

      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!

      Reply
        Jon Acampora - August 6, 2013

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

        Reply
Michael Pennington - July 30, 2013

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

Reply
    Jon Acampora - July 30, 2013

    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

    Reply
Sonu - July 29, 2013

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

Reply
    Jon Acampora - July 30, 2013

    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

    Reply
Darren - July 1, 2013

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

Reply
    Jon Acampora - July 2, 2013

    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

    Reply
      Darren - July 2, 2013

      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

      Reply
        Jon Acampora - July 3, 2013

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

        Reply
sriram vangara - June 19, 2013

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

Reply
    Jon Acampora - June 19, 2013

    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

    Reply
      shubham - July 10, 2015

      hey Jon,
      thanks for this awesome macro.

      can u plz help me with right zoom.
      can you give the syntax for right zoom

      Reply
Jon Acampora - May 24, 2013

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

Reply
Raghu - May 12, 2013

Excellent. I love this feature.

Thank you.

Reply
winston snyder - May 4, 2013

Jon,

Well done!

Reply
yuhanna - April 14, 2013

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

Reply
    Jon - April 14, 2013

    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

    Reply
yuhanna - April 11, 2013

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

Reply
    Jon - April 11, 2013

    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

    Reply

Leave a Reply: