Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
okay for a labs off model five. Now, you will be given one very big file
00:08
with quite a lot of sales data.
00:13
And you're supposed to analyze this data.
00:18
Let's go just to the file so you can see what you have to do. So they're quite a lot. There are 60 entries in this file. Let's zoom.
00:27
And just for a reminder, if you wantto un's oum, you press control and then you scroll, you're
00:34
wheel on your mouth if you have one.
00:38
I mean, if you're using mouse
00:40
and I highly recommend that you use mouse been working in Excel because working with anything else in Excel is pretty much a nightmare. So we have this file
00:50
and we have invoice numbers.
00:54
And then, uh, we have the date of these invoices. We have a customer name.
01:00
Then we'll have a list of product codes on that invoice
01:03
quantities
01:06
price
01:07
and total amount for each
01:11
item. So let's zoom it in
01:15
S o. You can see what's there,
01:18
and we want to analyze this data so that first we
01:26
I want to see who's our best customer. So to which customer are we selling the most?
01:33
Then we want to see which product code is being sold in largest quantity.
01:41
And then again, we want to see which product code is generating the highest revenue.
01:47
So we need to find three things. So we have this road data,
01:52
and in order to do that,
01:55
we're going to copy this data. So we're going to create
02:00
three sheets for each analysis,
02:02
and we're going to just select these Rose like this. We're going to press control, see?
02:09
And then let's see how just how many of them up to G.
02:14
Jen. We're going to select these, and we're going to press control. We
02:19
Why are we doing this like this? Because you see, when we select these combs and when we copy data
02:24
to the selected range it automatic sleep reformers the target sheet with everything that was in the previous one. So even the comrades are exactly the same.
02:37
So let's go to this one. Select this one as well,
02:42
control we
02:44
and then go to this one.
02:46
So let a G. Jones and then press control week. So we have it copied three times,
02:53
and now we want to see first
02:58
who's our best customer
03:00
in order to do that. We're going to do you use first sort function,
03:05
so we're going to go here,
03:07
may click on data we go to sort
03:10
and then we want to sort by customer name
03:15
A to Z
03:15
and resource and sell values. And my our data has Heather. So this up there is a heather.
03:23
So it's like, OK,
03:23
and we get this.
03:25
The next step is to create subtitles
03:31
And, uh
03:34
so what? We want to be one for every change in customer,
03:38
to some
03:40
total.
03:43
And to create
03:46
How much is this customer
03:49
has bought? Totally so logistically this
03:53
And we have these numbers now for Queen an industrial equipment
03:59
for Atticus, Total tools total and so on and so on.
04:03
So we're going to click on number two, which is going to remove everything except these totals and grand total.
04:11
And now we're going to go in this cone
04:15
and again do some sorting.
04:16
And this is very nice, because if you create
04:18
sub totals and then
04:21
leave just them on the screen and you go to sort
04:27
and we're going to sort by total
04:30
and we won't largest. Smallest
04:32
herewith have Schumann. Manufacturing
04:36
is the best customer we have. They have purchased total of 2,445,167 0.12.
04:46
And so we did the first task.
04:49
The second task would be
04:53
to find
04:54
what
04:55
product is generating the best sales.
04:59
So we're going to repeat these steps in a similar way, but slightly different
05:05
values that we're going to sort. So let's go here to the product code. We go again to sort
05:13
and then we sort by product cold
05:15
and we want
05:17
smallest to largest.
05:19
And it's like here and now we have sort this according to product codes,
05:25
and we're going to go again into sub totals.
05:28
And now, for every change in product codes,
05:31
we're going to
05:33
some the totals.
05:36
So we want to see what is the total sales per product coat.
05:42
We click. Here
05:44
we go again on to be quick. We just get the totals
05:48
and then we go here
05:50
and we go again to sort sorry.
05:55
So we're going to sort by
05:57
total
05:59
and we want largest the smallest
06:02
and get that product cold. 13-258 for 8-9
06:10
has generated the largest
06:13
revenue
06:14
and then again, pretty much the same thing. But now we want to do it with quantity units sold. So we go again here to the product Cold re sort by product code. Well, it doesn't matter if it's most largest or the other way around,
06:31
then we go to sub totals.
06:33
But now we're changing so forever changing brother code,
06:39
we are going to sum the quantity
06:43
and we're going to click, okay?
06:45
And again, we're going to
06:47
do this.
06:48
And then again, we're going to do the sorting by
06:53
quantity
06:55
and we won't largest the smallest and think OK, and we see that this product has generated the largest quantity as well. So if you go to this one sheet, we see that it's the same product
07:09
codes, but it doesn't matter. So we have done all these things using basically sort, sub total and
07:16
again sort. And in this way we have reached
07:20
some very, very simple conclusions, important ones in a very simple matter from a sheet that had 60 rose. Imagine if we had 1000 trolls to do it manually, it would be almost impossible to do it in in some some
07:41
reasonably long time
07:43
So this was done in
07:45
a couple of minutes.
07:47
Uh, now, if you have seen this video, you have seen it. But I urge you to try to do this
07:57
with a little paws. Little break after a couple of hours and do it by yourself without looking at video.

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor