Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

Video Transcription

00:01
in this module, I'm going to trying to explain to you how to work with map charts and maps charts. Here's relatively new thing in Excel. Its uses bing maps that are available on Internet
00:13
and can connect data from Excel to the big maps.
00:19
This is going to be relatively short module. Just one lesson, and let's skip with the introduction
00:26
and just go to the example how the map charts are used.
00:32
So,
00:33
um, in this Excel sheet, we have, ah, sales data half her country. So this is nothing special. Nothing in detail. We just have country listed, and we have the numbers that represents sales in millions of dollars
00:50
so
00:51
we can select these
00:54
countries. We can just click on the insert tab. And then there is a big maps
01:00
and you get the simple map, which gives you your location, which is in this case, my location, which is Belgrade.
01:07
And then you click on show locations
01:11
and then you get the wider view. You can resize this map.
01:15
You can zoom in so you get just the region you want,
01:21
and it's there
01:23
and their countries, and
01:26
so it gives you
01:29
the information off what do you have there?
01:32
So if we click on this
01:36
and
01:37
put in the shipping as well,
01:41
we'll get the sails and millions of dollars on pavement so Excel will recognize this is a heather of the table or heading and included there. So if you click here, we get nothing. If you get in, Kazakhstan
01:53
begins much bigger sales because this is much bigger market
01:57
and so on and so on. And we can expend this map a little bit further. We want
02:06
eso like this'll ce
02:09
No, this is pretty much as far as it goes. We can still zoom in a little
02:15
and then we can look att, the data for individual countries.
02:21
So especially here in
02:23
welcome area where they're very close.
02:28
So you see, it's saying Slovenia and this is not Slovenia. So I actually have to zoom in a little
02:34
to get specific data or let's close this. Yeah, you see, we have to zoom in a little bit more. So this is slightly to the imprecision off being maps. So this is giving me data for Croatia, which is here,
02:50
and I want data for Serbia and they get data for Bosnia Herzegovina and let zoom a little bit more.
02:59
And now I'm getting late for Serbia. So this is how it works.
03:02
There's also one thing that you should notice is that
03:07
you see this area here
03:12
weirdly shaped area is Croatia.
03:15
And what is Ah, being maps doing is putting this area in a rectangle that fits snugly to the area you're looking at. And then it puts your data your your circle
03:31
at the center of that rectangle.
03:37
So what happens is that because Croatia is like this,
03:40
the data for Croatia is not set the somewhere where the capital is, which should be here.
03:47
I know it's here. Where is it
03:50
use? You see the geometric center off the region that Croatia is occupying. So this is how it works.
03:59
It's slightly imprecise, but you have to take care off
04:04
zooming in all the time. If you want to get this data also, you see the size off the circles represent the size off sales. So here we go here, you see that the largest sales is in Kazakhstan and it's the biggest circle. So this is
04:24
this is how it works.
04:26
Also,
04:27
the level of detail you can go is the other story. So, for example, let's say that we want to split Europe in four parts and we say Western
04:38
Europe.
04:42
And then we say northern
04:46
Europe.
04:47
And then we see
04:51
so there
04:55
Rupe
04:56
and then received
04:58
Eastern
05:01
Europe.
05:02
And then we put some numbers here. It doesn't matter. So 78 65
05:11
56 44 then we select these
05:18
and we just want to pin them there
05:23
and we get something that we didn't expect.
05:26
So
05:27
Eastern Europe, Ukraine. You get this number for all of Eastern Europe
05:31
because Ukraine, in being databases, has the designation that is in Eastern Europe.
05:40
But here we get just Europe and we actually actually got the numbers for Western Europe,
05:46
which is
05:48
not precise. So if you click here,
05:54
we get data for Southern Europe here, so it doesn't work.
05:59
But for example, if this would be Iberia,
06:04
then suddenly it works because Iberian peninsula
06:09
is geographic
06:11
term.
06:12
And if you click, Iberia bingo or Excel will connect this to the geographic term called Iberian Peninsula, and then this will work. Also, if he instead of Southern Europe or Eastern Europe, for example, we put here Balkans.
06:32
It will work because Balkans, Let's close this
06:40
because North Macedonia, the country of north Macedonia, is said to be in Balkans and it will set it here, which is pretty much correct because this region here is concerned to be Balkans. So
06:54
it kind of works. Ah, so you have to be really careful that you put in this
07:04
going
07:05
data that actually exists in big maps out
07:11
you can go to the 7 11 look detail, but what you can't do
07:15
is, for example, if you want some other data as well.
07:18
So if we copy this and being this,
07:23
we can do this. But if we want, for example, too,
07:28
put some cities within each country. So we have to,
07:31
um,
07:33
major eso, for example, this can be
07:38
profit or whatever.
07:40
Um,
07:41
it cannot be because it's bigger than sales. But just for the
07:46
let's say this is one, and this is I don't know, 1.1 and sorry
07:55
and 1.1 in here rips but
08:01
like, uh,
08:03
0.5
08:05
and here we have I don't know
08:09
0.7.
08:11
And then you get all of these numbers so you get both the blue and the red circles or red circles for profit.
08:20
Blue circle is for sales. So revenue. Um,
08:28
but if we want to put here
08:31
another Kahlan and say, for example,
08:35
uh, city off, Your Honor, which is capital of Slovenia,
08:41
and then we select these. So we say city,
08:46
and then we select all of this
08:50
and pin it. We get the message that it is. The data is in. Really? Because you cannot
08:56
put
08:58
to description columns, you can put more. Number comes, but you can't put two description codes so it doesn't work.
09:07
And we're going to continue about the story about details in the next video where we will continue this lesson.

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