00:00Alrighty, recently I shared an Excel tips and tricks video demonstrating how to create
00:05a dynamic bubble chart in Excel that looks like the one as shown here.
00:10For today, someone had posted a question inquiring about dynamically altering the bubble size
00:15in response to the changing values in your dataset.
00:19Well, this is how you do it.
00:20The first thing I'm going to do is to create a helper column on column E here and basically
00:25the helper column will calculate the percentage of this value against the whole value.
00:30The formula for that will be equal to this cell here, D3 divided by sum of the entire dataset
00:39here.
00:39I'm going to press F4 to make a fixed cell reference and then close parenthesis and you're going
00:46to multiply that by 100 and essentially you will see a value like this here.
00:51So we're going to apply the same formula to the rest of the row and that's how I calculate
00:55the percentage.
00:56I'm going to resize this so that my chart stays at the center.
00:59So once that's done, the next thing I have to do is to take a note of where your things
01:05are.
01:05As we have already established, our helper column is column E and I think you have to note
01:11is that the name of the chart, you can see my chart name is called Chart3.
01:17So with those details, you're going to right click on the sheet name here and from this context
01:23menu, you're going to select view code.
01:25This is going to open a VBA editor here.
01:28After that, you're going to paste this VBA script here.
01:30If you'd like a copy of this VBA script, please leave a request on the comments section of
01:35this video.
01:35So a quick overview of this VBA script.
01:39This first sections are constant and these are the things that you can change.
01:42The first one will be your sheet name, minus 352, right here like this.
01:48And then this is the chart name, Chart3, which we already found out, minus Chart3.
01:52If you have forgotten what your chart name is, you're going to have to select your chart and
01:57look at the name of the chart here.
01:58And then after that, this is the helper range, E3 to E8 coincides with E3 to E8 here.
02:06So once that's done, let's have a quick look at the VBA here.
02:11This line here creates an object for the chart.
02:14It's like a handler so that you can make changes to the marker size.
02:19And this one here creates an object to the range of the marker size in percentage, which
02:25is your E3 to E8 here.
02:28It's basically a handle or object itself.
02:30So once you create these two objects, the chart object and the cell range object here,
02:36you're going to go through the two for loops here.
02:40The first for loop is in an event.
02:43Let's say for example, if you have multiple of such a line chart, this will help you take
02:49care of it.
02:50For my example, I've only got one line chart.
02:52So this for loop will only go through the loop once.
02:57And then the second for loop, the nested loop here, goes through every marker on your line
03:03chart here.
03:04Let's say for example, in this VBA, when they execute, you'll detect this very first line
03:10chart and you'll go through every marker like this one, this one, this one, this one, this
03:16one, this one.
03:17And the reason for that is that it will go ahead and resize it according to whatever I
03:23specified on my helper column, which is a cell E3 to E8.
03:29So once that's done, I'm going to go ahead and control S to save it and close my VBA editor.
03:36Now anytime when I make changes to any of this value, you can see, let's say if I change this
03:40to 25, you can see that the bubble size changes accordingly.
03:45If I make it this one, uh, astronomically large, let's say for example, 60, you can see that
03:50this comes in par with this.
03:52And let's say, let's say make, let's make it a bit bigger here, maybe 90.
03:56You can see the changes, the difference and all that stuff.
03:59And if I were to change this to say, make it smaller.
04:02So it looks a bit obvious here.
04:03You can see that the biggest, uh, number will have a largest, uh, size of the bubble here.
04:09And you can see they changed the size accordingly to that.
04:11They don't look overly big because we are working with percentage.
04:15And for some reason, let's say, for example, if you find that your bubbles are too small,
04:20what you can do is that you can actually, uh, go to your helper column here and multiply
04:25that by two so that, uh, it looks a bit larger than, and it looks a bit more obvious, right?
04:32So at least it's not so small and undersized.
Comments