All language subtitles for 2. Formatting a Pivot Chart - Part 1

In the previous lesson, we started talking about pivot charts and how you can pick the appropriate

pivot chart for your data.

And in this lesson, we're going to create another pivot charts.

And then I'm going to show you some of the techniques you can use to really format that pivot chart

and make it look good.

Now, once again, we are going to rearrange our dataset.

So I'm going to remove month name and we're going to put country back down into rows.

And that's pretty much all I want for my first pivot chart, just the country and the total gross sales.

Notice that now I've changed the layout.

This label doesn't really make sense.

It says country product.

So let's double click and let's just change that to country.

Now, another thing I can do here is I can sort my data.

So I want the highest sales at the top going all the way down to the lowest sales.

So let's right click go to salt and I'm going to say salt, largest to smallest to put those in order.

Now I'm ready to create my first pivot chart.

So let's jump up to pivot table analyze.

Click on our pivot chart button.

And now I can go in and choose my pivot charts.

So we're going to start out with just a basic column chart.

Remember, we do have other iterations of the same type of charts up here.

Now I'm just going to go for a plain old cluster column and let's click on.

OK, now with any of these charts, you can drag them out to resize them.

And what you'll notice is that they will come with these little gray filter buttons.

So if I actually want to apply filtering to the chart itself, I can click the country button and then

I can go in and maybe just choose to filter by Japan and United Kingdom, and that chart is going to

update.

Now, let's just clear that filter because I prefer to apply any filters to the actual pivot table instead,

which will in turn update the pivot charts.

So let me show you what I mean if I click the filter dropdown in the pivot table, I can do exactly

the same thing here, and that will not only update the pivot table, but also the pivot chart, because

I don't tend to filter on my charts.

I like to remove these gray buttons, and I would say that when you are designing or thinking about

designing your pivot chart, you want to make them as clean looking as possible, so remove any unwanted

or unnecessary items.

So to get rid of these gray buttons, we just need to right click and go to hide all field buttons on

charts to get rid of those.

Now what else on this chart is pretty unnecessary.

Well, if we take a look at my legend, I just have total in there now.

I don't really need the total because the bars themselves are showing me the total, so I'm going to

remove the legend as well.

Now, when it comes to formatting your chart, there are many different ways that you can do this.

Once you've clicked on your chart notice that you have three additional contextual ribbons pivot to

analyze, design and also format.

You also have a little plus button next to your pivot chart, which allows you to customize each element

of your chart.

And there are even more options for formatting your charts in the right click menu as well.

So lots of different places to go to apply formatting.

So if I want to remove this legend, I can click on the plus to show my chart elements and I can d select

legend and that will get rid of it.

Alternatively, if we just undo for a moment there, this is simply just a text box.

I could just choose to delete that sound.

So what else might I want to do to this chart to make it look a little bit more interesting?

Well, I'm probably going to want to add a chart title.

Now again, this is just simply a text box we can double click to edit it.

So what is this chart displaying?

Well, it's the total gross sales by country.

And you can apply whatever formatting you like to this title.

So if I go to the home ribbon.

Let's let's make this bold.

And I'm going to change the color.

Let's make it a dark blue color.

Like so, and maybe even slightly bigger.

There we go.

It might even be that I don't want it in the middle.

I want to move it over to the side.

And if I hold down shift as I drag this, it ensures that it stays in line.

Now what are the formatting minds?

I want to apply here?

Well, maybe I want to change the color of these bars.

Remember, this is just being taken from the theme that's in use in Excel.

Now there are a couple of different ways I can change the colors.

I could go up to the Design tab and then I have a change colors dropdown just here.

Now, this is only going to show me different palettes based on my theme template.

So everything you see here is still within that same color range that my theme uses.

If I want to apply a completely different color, I can simply select the bars and notice.

When I click on one, it's going to select them all, go up to the format tab and then I can manually

choose what color I want to use.

And if it's not part of my theme colors, I can choose more colors and then really choose a color that's

suitable for what I'm doing.

So I'm going to go for this sort of teal green color.

Let's click on.

OK, what else can I add to my chart?

Well, let's click the plus.

I'll take a look at some of the other elements.

What I might choose to do is add something like data labels.

Notice as I hover over, I get a preview of what that looks like.

And this is going to actually show the value from the pivot table at the top of the bar.

Now, sometimes I like to do this because it means that I can effectively remove that y axes and make

my chart look a lot cleaner.

So let's go for data labels.

If we click the arrow, I have a choice of where I want those positioned.

So I'm going to have these on the inside.

And now I know that looks a bit weird at the moment, but we are going to make a change to these bars,

which is going to make this look a lot nicer.

So let's choose inside and for the time being.

So now that I have those values there, I don't really need to have this axes showing the values.

So the easiest way for me to get rid of this is just to select the vertical axes and press delete.

Now, because we've added these data labels on the inside and I'm going to widen out the actual bars

themselves.

So let's click on the bars.

This time, I'm going to jump into format data series.

And what you'll notice here in the pain on the right hand side is that the option at the bottom is gap

width.

So I can make these bars narrower or I can make them wider.

So I'm going to drag them out so that they accommodate those labels nicely.

Now that I've done that, I think I'm going to select my data labels.

I'm going to make them bold, and I'm also going to change the color of them to white.

So for this, we just need to go up to the home tab.

We're going to make them bold.

And then we're going to change the font to whites.

So this is now looking like a really nice, clean looking chart.

Now one important thing when you are working with Charles and particularly when you're formatting them,

it really matters where you're clicked on the chart.

So if I'm clicked on the title, any formatting I apply will apply to the title.

If I'm clicked on the plot area, any formatting I apply will be to this area.

Same with the bars and the data labels and the axes, so on and so forth.

And I will say that for some of these elements that quite hard to select by clicking on the chart.

But if we go up to the format ribbon in this first group here, we can make this a lot easier on ourselves

by using this selection menu.

So if I know that I want to format the plot area of my chart and I know I'm editing the correct area,

what I might want to do now is go to shape, fill and maybe fill that plot area with a light gray background

fill.

So don't forget about this little selection dropdown.

It can come in really useful, particularly if you're struggling to select an object on your child's.