Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:01
okay, you have in previous lesson. You have been able to learn what the people table is,
00:07
and now we're going to talk about how to create one.
00:10
And it's relatively simple procedure if you know what your drink. And, uh,
00:16
we're just going to jump to excel,
00:19
and we'll go to our original data table with information about Ah, actually, a report about the support service is provided by some technicians in the Cole Center.
00:34
We have this date. I'm not going to explain it again. You have heard it in previous lesson.
00:39
We're going to just go directly into creating people table and we go to insert,
00:45
and the 1st 1
00:47
you see on the far left is pure table. And it's not by accident because people table there used all the time and quite a lot in especially in some business reports.
00:59
So we take on inter pivot table
01:02
and we get, uh,
01:04
the what, Excellencies is the data in which you have
01:11
ah, your original table.
01:14
And what also is important is that you must have this heather row in your table. Otherwise, Excel won't know how to name all these categories that you're going to use.
01:26
You can use an external data source or
01:29
there you can you can use Ah,
01:32
um
01:33
connection. But, uh,
01:37
we're not going to go there, So we're just going to use this data.
01:41
And this paper table of yours can be created the new worksheet. Or you can use the exact existent worksheet in which you have to place your location outside the original data
01:56
location on on your existing worksheet.
01:59
But we're going to go with new worksheet and just take okay.
02:05
And now we got sheet five, and it says original datum, a storage shed fighting it and contains people table, which is not formatted. So nothing is there.
02:17
And then you have
02:20
basically the combs,
02:22
and you want them somehow
02:25
placed in there
02:28
in order to
02:30
get the information from it.
02:32
So you have filters, you have columns, you have rose, and you have some well use.
02:39
So first thing, what we want to see with this paper table. So let's ah, we want to see how many,
02:46
um,
02:47
off each class off error of issue resolutions. Each technician had.
02:53
So how many of those past and how many of them had
02:59
failure? Category of 123 or four.
03:02
So we're going to use failure. Categorias our main
03:07
source of information this con and we put it in Rose.
03:12
And then we have failure category 0123 and four.
03:17
And then what we're going to put in our columns are two questions.
03:23
And now we have Charlie, Jane, Pablo, Richard, Simone, Tamara and we have grand total.
03:30
And now we want to see how many off each of them
03:34
head.
03:36
And in this case, we're going to move again. The technician cone. But now in some values.
03:44
And now we get what we want it.
03:46
So we have
03:50
we have count of technician because we had
03:53
How many times did they resolve
03:57
with zero with one? So this is not the sum. This is count.
04:00
And now we get Charlie had total 25 tickets.
04:06
Out of those, 25 18 were calculated. 18 were resolved
04:13
a cz past. So everything was okay. We had to with failure category 1212 and three with
04:25
four.
04:26
And this is our table. Which tells us
04:29
something about
04:30
our technicians.
04:33
One other thing that we can do is, for example, look at the average time to resolution. So we're going toe really find our table
04:45
and completely. And now we're going to remold these just like this
04:54
and we're going to now put our technicians in rows so we have them here,
05:01
and then we're going to have some values
05:05
time to resolution
05:08
in minutes.
05:10
But we don't want to look at the total time spent. We want the average time. So we're going to select these cells here
05:18
and click here.
05:23
And
05:25
you see this This is what you get when you look at properties of you when you right click on cells in
05:31
a favor table, not regular cell in the table.
05:36
And you get this summarized values by average,
05:41
and I will get a rich
05:44
and, uh,
05:46
we can also show well, yours as percent of grand total of con total wrote Toto.
05:51
We're going to
05:53
just show them as they are. But we're going to form of this cells because this is not looking good. And we wanted to be number
06:03
Sorry, not currency number, and we want to decimal places and no 1000 separator.
06:12
And we also do that here as well.
06:17
And now we get
06:19
Charlie's average time Jane's everytime Pablo's average time and so on and so on.
06:26
And now we want to sort these
06:30
so we can sort So we want the smallest first, because the shortest time to resolution is better.
06:39
And now we get that the Pablo is number one with just 309 minutes and 60 seconds 600.6 minutes. Sorry.
06:48
So, um, this is how you create your people table. So regarding on what you want to see and how you
07:00
put your daytime to these for
07:02
areas over your filters, combs and so on and so on
07:08
you get the different information. So let's ah,
07:11
see what the filters air there for.
07:15
Let's just drag month, two filters. And now we have all
07:19
and we want to see it separately for,
07:27
uh, just November.
07:30
And now it's like this.
07:33
So it's a different timing in November,
07:36
and we've if you go here
07:39
and we unsolicited number 11 and get 12 so we get it now for December
07:45
so we can Ah,
07:47
we can see that in December, Charlie was the best, because
07:51
the time is to 68.2
07:55
and this is how you can
07:58
nicely play
08:00
with your pivot table
08:01
in order to get what you actually want to see.
08:05
So, um,
08:07
this is about making your own people table. And in next lesson, I will be talking about how to create the chart that goes with this people table.

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