Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
Okay, um, in this module,
00:05
I'm going to talk to you how to use map charts and excel.
00:09
Now, there are no
00:12
our points lies. You are going to look, we're just going to go directly to examples,
00:18
and there is not much of them. But essentially, the map chart in excel
00:26
allows you to
00:28
joined geographical data with numbers or geographical locations, been numbers.
00:37
And for United States,
00:39
you can go down to the level off county zip code etcetera.
00:45
Um,
00:46
for worldwide situation, you're limited just to the country level. So we have here some country levels of this is some mock up data
00:58
off sales annual sales from the countries I have bean working with
01:03
ah in the past and we want to create the map chart. So we go to insert,
01:10
and here we have the option called bing maps. So you click there,
01:15
you get Ah,
01:17
the location you're in. You don't want that. Do you want a toe pin?
01:23
Basically, to show the locations that you have selected here. And you have to be very careful, because if you something type something wrong here,
01:34
bing maps won't be able to connect your data with
01:42
uh, with geographical location. So, for example, if you if you type the name of the country in local language, it won't work to have to be in English, you can expand the map, you can zoom, and this map is interactive. So
01:59
if you click here,
02:00
you get cousin Stan. You get sales
02:04
and you can close this.
02:07
You can also use filters. So, for example, we just won
02:13
three countries
02:15
and, uh, sorry, I didn't say apply.
02:21
And now I get this region zoomed.
02:23
And when I click here, get Slovenia. When I click here, I get Slovenia. This is this is something that
02:31
is slightly an issue. So you have to zoom a little bit and then it picks the right one.
02:38
So when I took here, get Serbia.
02:43
The problem is that, for example, here with like countries like Croatia,
02:49
so Croatia is this year
02:53
for you. They don't know it,
02:55
and it will pick
02:59
geometrical center off the country, which sometimes can be on the border with the other country of the country's shapes.
03:09
Unusual, like Croatia is. So, uh, so that's it.
03:15
I could add more countries here. I could, uh, put Bosnia Herzegovina which is in the region here on the filter as well. Sorry, you have to do the apply every time. So you click here, you see, you get it slightly
03:30
different than it should be. But if you zoom enough
03:36
and click here, it will pick the right one. So here it will be Croatia and so on and so on. And this is interactive. So you can leave it together with your data. Or you can put it in
03:53
somewhere else like
03:57
here.
04:00
And you can have it just
04:03
on this sheet on this worksheet. So nothing else except this maps. It's very nice representing
04:19
sometimes won't do some things,
04:23
but here it is. So we do the filter years, we select all the apply
04:30
and now we get
04:33
something else and we can no
04:36
zoom in. We can scroll.
04:40
And so So this is example. It has very little data, but you have it for all the countries or
04:46
for some cities as well.
04:49
Um, it will work nicely. What is also important is that you can at this point, have just one level of data her
04:59
a row, so you cannot have more than that.
05:02
So let's raise this one because we have it on the other sheet.
05:08
S o You just call this, for example, too?
05:13
The
05:15
man.
05:16
And let's now add another sheet and we'll call it
05:23
three d
05:26
map and I'll explain Thio
05:29
very soon. How do we get it? So we have the same data and weekly country the map
05:35
and we have this one. So it's this one
05:39
and I have already prepared it a little.
05:42
And you have here a list. The fields. It can be more than just the one. So it can be sales and it could be something else. Number of units, for example.
05:54
And ah, then ah,
05:58
you see way have this region So the highlighted country's air those in the region, the rest is not visible.
06:08
You can change the way it's displayed. So, for example, it can be three d bar sticking out of the country. You have here the layer to which you can Sorry, which you can rename and call it.
06:24
No sales data.
06:30
Get it here.
06:30
You can do it like this is Well, that looks pretty much the same.
06:34
And, uh, yeah, you can do it with the bubble, which is nice.
06:42
Ah, and, uh
06:45
Ah,
06:46
and this is it. And now you can do the capture screen.
06:51
And if you
06:54
switch back to excel
06:58
and go to three D map and press paste,
07:01
you go that three d map, you can resize it,
07:05
but it's not interactive anymore,
07:09
but it's useful
07:11
if you want to present something and show some geographical stuff. So this is, uh, this is working very nice. Ah, and, uh,
07:23
this is how it's done. What you also can do with three D maps
07:29
is that you can play tour
07:32
on. You can create video
07:35
off it. So you removed this, you remove this
07:41
and then you say play tour
07:47
and whatever you did, it will be recorded here. So if you have
07:55
click here,
07:57
So
07:58
this
08:00
or this
08:01
or this
08:05
Andi, then
08:09
you go back
08:11
and you can create video which we are not going to do now. But you see how it looks and you can have multiple scenes. You can have teams.
08:20
You can pretty much customize this if you have geographically based data.
08:26
And this
08:28
very nice
08:30
one thing that you have to remember is that
08:35
ah
08:39
bing maps can be confused.
08:41
with multiple entries with same name. So, for example, if you're having two towns that are called the same, So, for example, I'm living in a town called Belgrade
08:56
and drop, I think, three or four Belgrade's in the United States. So if you're having worldwide data per cities, it can create a mess, and
09:09
you won't be able to do things automatically. However, these maps can really help in your presentations and really help with the visualize ing your late, especially its of kinds of sales data or some population data. You can put it on a map easily,
09:30
and that's pretty much it regarding this model. I won't be having a recap here because it's Ah, it's a very short story. It's a very simple thing. Along goal. Other map.
09:45
The Charts and Maps charts in Excel This son is relatively new. It has
09:50
being available since XL 2016
09:54
and it will probably improve in the future. But so far this is it. And thank you for your time

Up Next

Intermediate Excel

Being an intermediate level user of Excel can help save a user hours of work time and take full advantage of the Excel application. Taking an intermediate Excel training will help students advance their skills and pass an intermediate Excel exam.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor