Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:05,570 --> 00:00:08,510
So I pivot table is really coming along now.
2
00:00:09,230 --> 00:00:14,750
And what I want to show you in this lesson is just a couple of little tricks that you can use for managing
3
00:00:14,750 --> 00:00:17,030
error values and empty cells.
4
00:00:17,810 --> 00:00:21,810
Now, before we get onto that, there's a couple of little admin things I want to do here.
5
00:00:22,490 --> 00:00:28,640
Now, notice because we've rearranged the data that we're looking at, we're now displaying it by years.
6
00:00:29,000 --> 00:00:32,390
I just have this label at the top here that says column labels.
7
00:00:32,390 --> 00:00:34,430
Again, not very explanatory.
8
00:00:35,030 --> 00:00:37,850
So let's double click and change the name.
9
00:00:38,000 --> 00:00:40,430
So I'm just going to change this two years.
10
00:00:40,850 --> 00:00:45,350
Another thing I'm going to do at this stage is I'm going to give my pivot table a name.
11
00:00:46,040 --> 00:00:51,110
Now, notice on the pivot table analyze ribbon in the Pivot Table Group, where it says Pivot Table
12
00:00:51,110 --> 00:00:51,650
name.
13
00:00:51,920 --> 00:00:55,100
We just have the rather generic pivot table for in there.
14
00:00:55,760 --> 00:01:00,950
And once you start creating lots of pivot tables and maybe start performing calculations or creating
15
00:01:00,950 --> 00:01:06,770
charts, it's going to be helpful to you to be able to quickly identify each pivot table.
16
00:01:07,310 --> 00:01:09,770
So it's always good to give it a meaningful name.
17
00:01:10,640 --> 00:01:13,610
So what is this pivot table representing?
18
00:01:14,030 --> 00:01:17,930
Well, it's showing me the gross sales by year effectively.
19
00:01:18,590 --> 00:01:23,270
Now, when I'm naming things like pivot tables, I always like to use a standard naming convention.
20
00:01:23,720 --> 00:01:27,290
So I like to use a prefix of p v t for pivot.
21
00:01:27,980 --> 00:01:32,960
Now, remember when you're naming items, whether it's a pivot table, a pivot char or even just a regular
22
00:01:32,960 --> 00:01:39,260
Excel table, you can't have any spaces in the name, so make sure you separate ones with an underscore
23
00:01:39,260 --> 00:01:41,120
or make them all one word.
24
00:01:42,140 --> 00:01:48,440
So I'm going to call this gross sales by year and hit enter.
25
00:01:49,910 --> 00:01:57,110
Let's also be consistent and rename the top of the bottom from sheet two to the same thing.
26
00:01:57,140 --> 00:02:00,650
Gross sales by year and Hansa.
27
00:02:00,920 --> 00:02:03,830
OK, now we're a little bit more organized.
28
00:02:04,100 --> 00:02:07,730
Let's take a look at how we can format era values and empty sales.
29
00:02:08,000 --> 00:02:11,090
Now it might be that you have errors in your source data.
30
00:02:11,180 --> 00:02:13,850
Now, I don't actually have any errors in mind.
31
00:02:14,180 --> 00:02:18,980
But if you have a data set that maybe contains calculations, maybe some calculations, things like
32
00:02:18,980 --> 00:02:22,850
that, you might find that you've got errors in some of these cells.
33
00:02:23,030 --> 00:02:27,410
And when you put that data into a pivot table, those errors are going to carry through.
34
00:02:28,100 --> 00:02:33,410
Now, it's really not a very good idea to send out pivot tables that have errors throughout them.
35
00:02:33,530 --> 00:02:34,910
So you have a couple of options.
36
00:02:35,420 --> 00:02:38,990
You can go into the original source data and fix the error.
37
00:02:39,260 --> 00:02:42,920
Alternatively, you can handle the error in the pivot table.
38
00:02:43,610 --> 00:02:44,780
So what do I mean by that?
39
00:02:44,960 --> 00:02:51,140
Well, if we go up to pivot table analyze in the Pivot Table Group, we have a little options button.
40
00:02:51,770 --> 00:02:58,100
It's also worth noting that you can get your pivot table options by right clicking and selecting it
41
00:02:58,100 --> 00:02:59,120
from the menu.
42
00:02:59,540 --> 00:03:03,890
Now this is going to open up all of your options for your pivot table, and I will say it's definitely
43
00:03:03,890 --> 00:03:08,000
worth going through some of these different tabs and checking your different settings.
44
00:03:08,840 --> 00:03:15,500
Now we want to focus on the Layout and format tab because in this format group at the bottom, can you
45
00:03:15,500 --> 00:03:16,040
see here?
46
00:03:16,040 --> 00:03:18,140
It says for error value show.
47
00:03:18,740 --> 00:03:24,230
So I could choose to turn this on and then type in what I wanted to show in the cell.
48
00:03:24,470 --> 00:03:25,970
If there is an error there.
49
00:03:26,600 --> 00:03:30,680
So it might just be that I wanted to show a zero or something else.
50
00:03:31,010 --> 00:03:32,280
Now I don't have any errors.
51
00:03:32,300 --> 00:03:37,850
I'm just going to turn that off for the time being because something else we can do in here is select
52
00:03:37,850 --> 00:03:41,240
what we want to show in the cell if the cell is empty.
53
00:03:41,870 --> 00:03:44,150
So let's look at a practical example of that.
54
00:03:44,600 --> 00:03:45,800
I'm going to click on Cancel.
55
00:03:46,040 --> 00:03:51,140
Now, if I look at my data as it's currently laid out and I scroll through, I don't have any blank
56
00:03:51,140 --> 00:03:51,680
cells.
57
00:03:52,400 --> 00:03:54,530
So let's rearrange our fields a little bit here.
58
00:03:55,370 --> 00:04:03,680
I'm going to remove the date and I'm going to grab month name and drag that into columns.
59
00:04:04,670 --> 00:04:08,290
Now notice here I have a couple of blank cells I can see here.
60
00:04:08,300 --> 00:04:14,150
There's no data for July four Kensington in the U.K. I have another blank cell here.
61
00:04:14,450 --> 00:04:16,200
There's another one down here.
62
00:04:16,220 --> 00:04:17,540
So on and so forth.
63
00:04:18,080 --> 00:04:23,180
So we have a few empty cells where we don't have any data now.
64
00:04:23,180 --> 00:04:24,140
That might be fine.
65
00:04:24,150 --> 00:04:25,970
You can just leave them as blank.
66
00:04:26,270 --> 00:04:31,250
But if you're going to create something like a pivot chart, having blank cells can sometimes make your
67
00:04:31,250 --> 00:04:33,050
pivot chart just look a little bit strange.
68
00:04:33,290 --> 00:04:38,390
It can also be quite hard to tell looking at a dataset like this which sells a blank.
69
00:04:38,990 --> 00:04:44,960
So what I might want to do here is fill all of the blank cells just with a zero instead, so we can
70
00:04:44,960 --> 00:04:47,090
do that from pivot table options as well.
71
00:04:47,840 --> 00:04:51,380
If I right click my mouse and go into pivot table options.
72
00:04:52,070 --> 00:04:57,920
All I need to do here is select for empty cells, show a zero and click on OK.
73
00:04:58,550 --> 00:05:02,300
And you can now see that those have been populated with a zero.
7340
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.