Original subtitles

So I pivot table is really coming along now.

And what I want to show you in this lesson is just a couple of little tricks that you can use for managing

error values and empty cells.

Now, before we get onto that, there's a couple of little admin things I want to do here.

Now, notice because we've rearranged the data that we're looking at, we're now displaying it by years.

I just have this label at the top here that says column labels.

Again, not very explanatory.

So let's double click and change the name.

So I'm just going to change this two years.

Another thing I'm going to do at this stage is I'm going to give my pivot table a name.

Now, notice on the pivot table analyze ribbon in the Pivot Table Group, where it says Pivot Table

name.

We just have the rather generic pivot table for in there.

And once you start creating lots of pivot tables and maybe start performing calculations or creating

charts, it's going to be helpful to you to be able to quickly identify each pivot table.

So it's always good to give it a meaningful name.

So what is this pivot table representing?

Well, it's showing me the gross sales by year effectively.

Now, when I'm naming things like pivot tables, I always like to use a standard naming convention.

So I like to use a prefix of p v t for pivot.

Now, remember when you're naming items, whether it's a pivot table, a pivot char or even just a regular

Excel table, you can't have any spaces in the name, so make sure you separate ones with an underscore

or make them all one word.

So I'm going to call this gross sales by year and hit enter.

Let's also be consistent and rename the top of the bottom from sheet two to the same thing.

Gross sales by year and Hansa.

OK, now we're a little bit more organized.

Let's take a look at how we can format era values and empty sales.

Now it might be that you have errors in your source data.

Now, I don't actually have any errors in mind.

But if you have a data set that maybe contains calculations, maybe some calculations, things like

that, you might find that you've got errors in some of these cells.

And when you put that data into a pivot table, those errors are going to carry through.

Now, it's really not a very good idea to send out pivot tables that have errors throughout them.

So you have a couple of options.

You can go into the original source data and fix the error.

Alternatively, you can handle the error in the pivot table.

So what do I mean by that?

Well, if we go up to pivot table analyze in the Pivot Table Group, we have a little options button.

It's also worth noting that you can get your pivot table options by right clicking and selecting it

from the menu.

Now this is going to open up all of your options for your pivot table, and I will say it's definitely

worth going through some of these different tabs and checking your different settings.

Now we want to focus on the Layout and format tab because in this format group at the bottom, can you

see here?

It says for error value show.

So I could choose to turn this on and then type in what I wanted to show in the cell.

If there is an error there.

So it might just be that I wanted to show a zero or something else.

Now I don't have any errors.

I'm just going to turn that off for the time being because something else we can do in here is select

what we want to show in the cell if the cell is empty.

So let's look at a practical example of that.

I'm going to click on Cancel.

Now, if I look at my data as it's currently laid out and I scroll through, I don't have any blank

cells.

So let's rearrange our fields a little bit here.

I'm going to remove the date and I'm going to grab month name and drag that into columns.

Now notice here I have a couple of blank cells I can see here.

There's no data for July four Kensington in the U.K. I have another blank cell here.

There's another one down here.

So on and so forth.

So we have a few empty cells where we don't have any data now.

That might be fine.

You can just leave them as blank.

But if you're going to create something like a pivot chart, having blank cells can sometimes make your

pivot chart just look a little bit strange.

It can also be quite hard to tell looking at a dataset like this which sells a blank.

So what I might want to do here is fill all of the blank cells just with a zero instead, so we can

do that from pivot table options as well.

If I right click my mouse and go into pivot table options.

All I need to do here is select for empty cells, show a zero and click on OK.

And you can now see that those have been populated with a zero.