# How to Make Your Own Pivot Table

Video Activity

Join over 3 million cybersecurity professionals advancing their career

Sign up with

or

Already have an account? Sign In »

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

Similar Content