### Intermediate Excel

Course
Time
8 hours 33 minutes
Difficulty
Intermediate
CEU/CPE
9

### Video Transcription

00:00
Okay, so let's go
00:04
in
00:05
more detail. Off data you can put on the map.
00:10
So we have a list
00:12
off restaurants in area. And let's show let me first show you the area. So this is the area, and we have some restaurants here, and I have already started entering data. So, for example, I ma'am, I'm a sales person, and I'm selling toe all these restaurants in this area.
00:28
Um, fancy bottled water. And, uh,
00:32
I want to put these data on the maps. I see. How
00:36
does it
00:38
00:40
So let's go back to excel.
00:46
Uh, so I'm here and I have some of these restaurants and I want to add some more.
00:52
And then you can use data from the so, for example, it's Ah, it's ah
00:58
yet this one. So if I click on this one,
01:02
I get ah,
01:03
01:07
and I can switch to
01:10
so
01:11
and I can put it here.
01:15
And then again, this is the name of the restaurant
01:22
on. I can put it here
01:25
and then I copy the coordinates of this one.
01:30
So let me close this,
01:32
and I can use this as well. So these air copied
01:37
I switch and I based it here. And then I look at my numbers and I see that I sell exceptionally well
01:45
in this restaurant. So I sold 139 big packs Walter
01:49
in last week.
01:52
So I have all these data here
01:55
and now How do I put it on a map? So I we already spoke about this. If I still like this and they say big naps
02:04
and I click here, I get the information that the data is invalid.
02:08
So let's try just by name. And this is where being Max being maps fail.
02:15
02:19
Let me close this. Let me just do this. I get something absolutely wrong because it will look for some of these
02:27
location's somewhere,
02:29
you know,
02:31
in the world,
02:32
because there is probably too many galaxy carfare restaurants worldwide.
02:38
So what I can do
02:40
is I can pick the address,
02:46
and
02:47
you see, I'm I'm getting
02:51
an issue here
02:53
because, uh,
02:55
this is this is something that's not what I wanted.
03:00
So the one thing that is 100% sure
03:06
is let me just cut this and then insert here. So if I organize my data like this
03:15
and I click this
03:17
this is 100% sure. So I'm getting the same map like you have seen
03:24
when I was
03:25
Yeah, here it is. I can zoom in with same region,
03:30
and I have I have these dots, but what I don't get
03:36
is I don't get the name of the place.
03:39
I just get the location. Why? Because I have targeted this location using latitude and longitude.
03:46
So there is no way that you can add to this the name of the restaurant as well.
03:53
It simply doesn't work.
03:54
But it gives you a nice picture on how to do these things geographically.
04:02
And
04:06
so this is about to de maps. Ah, regarding on how much data you have, you can get very, very dense distribution. You can see that the bigger circles up, for example, in this area and smaller runs and there and then then you can
04:24
make a decision that o R conclusion that it's probably some other brands attacking your territory and they're moving slowly from here now on on a street map of the city, it really doesn't
04:38
work like that but on a bigger scale. If your country why distribution, you can get some
04:45
pretty good picture about it. So this is how you can put a date on a man abusing being maps.
04:51
Now, the other option you have
04:55
is to select your data. Let's just move this a little bit
05:00
that just delete this one from the previous.
05:03
So this is what we have here
05:06
and now we can select this
05:13
and we can click on treaty map
05:21
and it takes some time,
05:26
05:34
And what you get
05:38
is a separate
05:43
program will separate window Sorry,
05:46
in which you get all these things.
05:51
And then what you
05:55
get here is you can do other things. So let's just zoom in.
06:01
Yeah, and we get,
06:03
uh,
06:05
data. We can represent it like this or like this,
06:12
and the size is
06:15
decided by average monthly sales.
06:18
And you see that it's ah little bit
06:27
to beg so we can do it like this.
06:30
And then when zoom out, you see these numbers here represented in the size of this
06:40
dealers
06:42
and this is how you used his things so you can
06:46
move it around. You could see this on such a small area like this.
06:54
It is, uh,
06:57
so you can reduce this so that it fits
07:00
07:03
and, uh,
07:05
you can make it
07:09
slightly transparent,
07:12
And, uh, you can also
07:16
customized the fields. So, for example, if you would have here
07:20
category, restaurant and the store,
07:25
we could go into more detail here
07:28
and here We have the address
07:30
and we have street,
07:32
or it can be latitude and longitude, but then they have to be in a separate
07:39
bones, but what we can.
07:42
But here is a store, a cz Well,
07:45
and then, uh,
07:49
for dress, this is an error. So this thing story doesn't recognize like we had on to map. I'm just showing you what you can do with these fields.
08:00
So if we go further in
08:03
and then just zoom in in this part of Belgrade
08:09
and reduce the height even more so that we can
08:13
look at the streets and we can also reduce the thickness so it doesn't cover too much of a map.
08:22
And so we used the dress and street and become this. We get the address and we get the average monthly sales.
08:33
So
08:35
in category, for example, we can, uh,
08:39
with the restaurant name. So it's a different restaurant in here. We get the name of the restaurant so we can customize the three d map
08:50
100% to our liking.
08:52
Eso
08:54
every restaurant has its own color. You can assign different colors, two different restaurants
09:01
and this is how you make,
09:03
uh, the whole three D map off the area.
09:07
You can also choose it to be flat map so you can move it slightly
09:13
easy. It's likely easier you can add. Ah,
09:18
you can find some locations. You can define custom regions like Western Europe, northern Europe and so on and so on.
09:26
And you can play with it a lot. I'm not going to in to go into all the details. But what you see here is that you have this layer with information about what's in it.
09:39
And if you zoom in, you can see the street names as well.
09:45
No, Sorry, you don't. But you do. You see the street names when you click on the
09:52
Ah.
09:54
Okay, so this is by restaurant. You can you can get all the information you want on this
10:03
toothy,
10:05
uh, image,
10:07
and you can then capture screen.
10:09
And then if you moving to excel
10:13
here and just do it here and we just press control, we we can get this
10:20
image
10:22
10:24
and we can have it as a separate stab in the worksheet. Or we can put it in the old worksheet so we can illustrate our work sheet with
10:31
with very nice screen. JJ dumps off
10:37
what we have
10:37
designed in treaty maps,
10:41
and this is pretty much everything we have about
10:46
being maps, and they're using Excel.

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

Milan Cetic
IT Security Consultant
Instructor