(bright music) In this video, I'm going to show you how
to create a dynamic map, which shows any KPI that
you want by geography for a specific company,
division or product. So basically we're going to be adding interactivity to the map. Let's set this up from scratch. This is my small sample data. I have information on
three different divisions, utility, productivity, and game. I'm showing revenue by these regions. I want to visualize this on a map and I want to do it in a dynamic way so that I can switch my view between these three different divisions and then see the revenue
associated with that region. The technique that I'm going to use here is very simple. And you can apply it no matter what type of
regions or countries that you're going to show. So you need to follow
basically the same technique, just change things up a bit so that it matches the
geographical locations that you want to show. Now, that technique is
to use a scatterplot with a background image. And later we're going to
switch that scatterplot to a bubble chart. Because bubble chart is basically a scatterplot
with an additional dimension. So remember, a scatterplot has values on the x and the y-axis. And those are what we're going to use to find out the position of
North America, South America and so on, on the map. Because right now revenue
is not going to help us with the position of where
North America is sitting on this background image. So that's something that
we need to set up once. Now, obviously, depending
on the number of regions that you want to show, the number of countries that you have, you're going to have more work. Because you need to do that set up once at the beginning. Once you do the work, you can actually use
that as your own template and then show any KPI that
you want to show on the map. First off, what I'm going to do is to insert a blank scatterplot. So I'm not going to click
anywhere in this dataset. I'm just going to click
outside the dataset and insert a scatterplot. Now the reason I'm doing
that is because right now I don't have any values
for my x and y-axis. I just have the revenue, which is going to be
the size of my bubble, but I haven't defined
the location of these. And I'd rather first see the type of map that I'm going to be
using, or choose that map, and then decide on the x and y values for each of these regions. To find a map, you can double-click here, activate the fill options, but we don't want a color. We want to use a picture. You could already have
a picture in the company that you can use. So you could click on File and select that picture. You could copy paste
it from the clipboard, or you can look online for it. I'm actually going to test this option and look for something online. For the Bing image search, let's put vector map, and I want a world map and I want it to be blue. So let's see if they have
anything available there. So you can see by default, it looks for creative commons only. This means that you can use it for private and commercial purposes, but it doesn't always
mean that it's fully free. So sometimes you do have
to provide attribution. Now that's something that
you should definitely make sure that you check if you need to provide
attributions or not. So which category in creative
comments it falls under. So let's just see if we see anything. Now that's the one I'm going to choose. And this one belongs to
the public domain pictures. So if I open this, here I can see the
license is public domain. So let's just open this in a new window. And this basically mean
that you do not need to provide attribution, that the person has given up their rights, that the provided this
image to the public domain. So, but always make sure
that you do check this. And if you do need to provide attribution, you do put it somewhere below your image. For now, I'm going to choose this, I'm going to insert. Now, let me just go and
remove my grid lines first and expand this to what I want. Now you can see this is
expandable in any direction, and I don't want to screw this picture. So I'm going to press Control + Z and go to the options and for the size I'm going
to lock the aspect ratio. Also under properties, I'm going to click, don't move or size with cells. Now I'm also going to go
and remove the outline here. And the other thing I want to
do is just stands up too much, I wanted to provide the idea. I mean, everyone can see
that that's the geography, but I want my data set, my
revenue to basically standout and not the image. So I'm going to increase the transparency to something like this. Next step is to plot the points where I have the region
sitting on this map. The way I do that is to fix my axis to a certain value set. You can choose, for example, between zero to one for
the x and the y-axis or zero to 10, zero to a hundred. So any dataset that you'd like
to work with, you can select. In my case, I'm going to
stick to zero to 10 scale. And I do need a data preparation table because I'm adding this additional
information to the chart. So the data that's going
to go in the chart, it's going to be sitting in
the data preparation table and not directly in my raw data. Here's the, this data
is going to feed this, and this is going to feed this. Now I need x and y information basically for each of these regions, give us, bring the header as well. I need to say, what is the
x-axis and what is the y-axis. Okay so, that's x, y,
and plot a point here. Now as a first step, I'm just going to take a guess at where they could be on here. And later once I add them, I'm going to fix the access
and I can correct these points. So first off let's do North America. North America is somewhere here. X could be two here, and y could be eight. So if I'm restricting
mine from zero to 10, that could be an eighth
and two under x-axis. Because this is the upfront work that you need to do once to
basically create your templates. Second is South America. So that's going to be somewhere here. That could be a three and for the y, that could also be a three. But here's, I'm going to do the
same thing for the other ones. Now that I have some sort
of sample data available, I'm going to add this in the chart. So I'm just going to right
mouse click, select data, add it's a series name. That's basically my locations
and my x-axis are these and the y are these. So that's not bad for a starter. And Excel automatically has
defined the x and the y, it's close to what I want, but best is that you fix it. So that they don't shift. So for the axis options, I'm going to fix it to
zero so that it's not auto, but when you see the reset, it means that you've really fixed it. Then you do the same for the y-axis. So now that it's fixed, I'm going to remove the chart elements that I don't want visible in the chart. I'm also going to remove this one, and remove the grid lines. So at this stage I can correct these, if I see that they're
not on the right place. So for example, for North America, I want it to be a little bit over here. So I'm going to change that to 1.5. For this one, South America, let's just change that a
little bit around 2.6 and 3.5. This looks fine. Europe looks fine. This one for Australia. Let's just bring it up a little bit. That looks better. Now that I have my points where my regions are sitting on this map, I can start to add my third
dimension, which is revenue. But my third dimension here is going to be dependent on the division. I want to add a drop-down box for that. So somewhere here, I need the user to select a division and that's going to be a data validation. Now, if you have these
in separate Excel cells, that you maybe also convert to a table, if you're adding new divisions to it, you would just reference those cells. But because let's say in this case, the divisions are not changing, I'm just going to take them in. You just have to make sure that you type the identical
wording of your divisions. That looks fine. Let's just update the formatting slightly. Now, what we want to do is to get the revenue for the division that the user selects
from this drop-down box. Here, let's check how
many criteria we have. One criteria is this division. Another criteria is the region. The formula that I'm going to use here is going to be the SUMIFS formula. And the reason I'm going to use it is because it can handle
different criteria. I'm not summing anything up. I'm basically returning one number because that's unique in this case. And North America, utility is unique. I don't need to sum it up with other ones, but that doesn't matter. The fact that it allows me
to handle different criteria just makes my life much simpler. So the sum range here is this one. I'm going to fix it with the F4 key. One of my criteria is the region, but the second argument
here is criteria range. So it's not the criteria itself, but the range where this is sitting on. And that range is here. So I'm going to press F4 to fix it. Now, where is the criteria? What should it look for? Is this one. In this case, I don't need to fix it, because I'm just dragging it down. The second criteria is
where this one is sitting and that's here. Again F4, now the criteria
itself is this one, and I do want to fix it. For utility that's this number, that looks good. And now let's see if I typed
in the other labels correctly, productivity and game. Australia game that's this number. So that looks good. Now that I have my revenue
in a dynamic we're here and the data preparation
table is basically changing based on the selection. All I need to do is to add this in my chart. But where do I add it? Well, first I have to
change the chart type from a scatterplot to a bubble chart. Because that's going to
give me an extra dimension where I'm going to bring in the revenue. So we're going to go with this one. Now Excel might be a bit
confused about where to put what, because it has added
a new dimension there. So if I go back to select
data and edit this, you can see that it has
removed my x values. So I'm just going to
add them back in here. My y values are fine, and this is the extra dimension and that bubble size is going to be these numbers. I'm also going to change the series name to basically let's call
it revenue by location. So that's fine. No, in the bubble chart, you have different options here. You can have the size. So basically this, to be
either the area or the width, because I'm going to go with area, but you can also scale it. If this seems too big on my graph, I can scale it to a smaller size. So I'm going to go with 80 in this case. What I also want to do here is to add the data labels. But these are not the
labels I want to show. What are these ones? They're my, y values here. So let's go and check
our options for labels. That's the y I don't want to show the y, I want to show the bubble size, because that's my revenue. Now let's put it, in this case
I'm going to put it center and just change the formatting so that we can see them better. It'll be great to have the
thousand separator in there. So that's the number formatting. And by default is linked to source. Basically, if I change
the formatting here, it's going to show in my chart. So I just press Control + 1 to go directly to this formatting. I can go to custom and click this one. You can obviously also go to number and just activate the thousand separator. Yes and that shows up here. So if I switch between game to utility, you can see that the bubbles change and the numbers change. Which is the biggest number here, that's this one for utility. Now for game that's Australia. Keep in mind, this is just sample data. Since these numbers are
quite close together, it will be great if I
highlight the maximum bubble. So it is easier for anyone to tell, okay, who's the best in terms of utility. And who's the best in
terms of productivity, without looking at the numbers
and the size of this bubble. So now the trick is, anything that you want
to conditionally format in your charts, you need
to add a new series. You call it anything you want, I'm just going to call it max revenue. Because that's basically series
that I'm going to add there. That only shows the number, if it is the maximum in this dataset. So that series is going
to have in this case, no number, no number, no
number, number, no number. That means I could use a
combination of the IF function and the MAX formula to get this done. So if this number equals
the max of this dataset, now I have to fix that. Then show this number. Otherwise, show nothing. It's only showing the number where it comes across the
maximum of that dataset. If I switched to utility, now it's this number. Because of this entire
series only has one number, and this is something I'm
going to add to the chart. So, right mouse click, go to select data. Add series name, always
give you a series of name. Now, x is the same, y is the same. The size of the bubble is
now my new series right here. And you can see it already added it in. So now immediately I can see the region that sold the most. So that's how you can create a dynamic map that is controlled by a drop-down. And you can also
conditionally format that map to highlight the country or the region you had the most sales in. If you like these types of videos, don't forget to subscribe to my channel. So you get notifications when new videos like this one, come out.
