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

af Afrikaans
sq Albanian
am Amharic
ar Arabic
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bn Bengali
bs Bosnian
bg Bulgarian
ca Catalan
ceb Cebuano
ny Chichewa
zh-CN Chinese (Simplified)
zh-TW Chinese (Traditional)
co Corsican
hr Croatian
cs Czech
da Danish
nl Dutch
en English
eo Esperanto
et Estonian
tl Filipino
fi Finnish
fr French Download
fy Frisian
gl Galician
ka Georgian
de German
el Greek
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
km Khmer
ko Korean
ku Kurdish (Kurmanji)
ky Kyrgyz
lo Lao
la Latin
lv Latvian
lt Lithuanian
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mn Mongolian
my Myanmar (Burmese)
ne Nepali
no Norwegian
ps Pashto
fa Persian
pl Polish
pt Portuguese
pa Punjabi
ro Romanian
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
st Sesotho
sn Shona
sd Sindhi
si Sinhala
sk Slovak
sl Slovenian
so Somali
es Spanish
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
te Telugu
th Thai
tr Turkish
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu
or Odia (Oriya)
rw Kinyarwanda
tk Turkmen
tt Tatar
ug Uyghur
Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:04,910 --> 00:00:10,970 In the previous lesson, we started talking about pivot charts and how you can pick the appropriate 2 00:00:10,970 --> 00:00:12,290 pivot chart for your data. 3 00:00:12,530 --> 00:00:15,890 And in this lesson, we're going to create another pivot charts. 4 00:00:16,190 --> 00:00:20,810 And then I'm going to show you some of the techniques you can use to really format that pivot chart 5 00:00:20,810 --> 00:00:21,920 and make it look good. 6 00:00:22,160 --> 00:00:25,520 Now, once again, we are going to rearrange our dataset. 7 00:00:26,200 --> 00:00:31,010 So I'm going to remove month name and we're going to put country back down into rows. 8 00:00:31,310 --> 00:00:37,430 And that's pretty much all I want for my first pivot chart, just the country and the total gross sales. 9 00:00:38,180 --> 00:00:40,310 Notice that now I've changed the layout. 10 00:00:40,340 --> 00:00:42,710 This label doesn't really make sense. 11 00:00:42,710 --> 00:00:44,390 It says country product. 12 00:00:44,840 --> 00:00:48,470 So let's double click and let's just change that to country. 13 00:00:48,800 --> 00:00:52,610 Now, another thing I can do here is I can sort my data. 14 00:00:53,000 --> 00:00:57,200 So I want the highest sales at the top going all the way down to the lowest sales. 15 00:00:57,830 --> 00:01:04,040 So let's right click go to salt and I'm going to say salt, largest to smallest to put those in order. 16 00:01:04,340 --> 00:01:07,010 Now I'm ready to create my first pivot chart. 17 00:01:07,730 --> 00:01:09,950 So let's jump up to pivot table analyze. 18 00:01:10,160 --> 00:01:11,960 Click on our pivot chart button. 19 00:01:12,290 --> 00:01:14,990 And now I can go in and choose my pivot charts. 20 00:01:15,830 --> 00:01:18,830 So we're going to start out with just a basic column chart. 21 00:01:18,860 --> 00:01:24,080 Remember, we do have other iterations of the same type of charts up here. 22 00:01:24,980 --> 00:01:28,310 Now I'm just going to go for a plain old cluster column and let's click on. 23 00:01:28,310 --> 00:01:32,760 OK, now with any of these charts, you can drag them out to resize them. 24 00:01:33,050 --> 00:01:37,700 And what you'll notice is that they will come with these little gray filter buttons. 25 00:01:37,910 --> 00:01:43,850 So if I actually want to apply filtering to the chart itself, I can click the country button and then 26 00:01:43,850 --> 00:01:49,340 I can go in and maybe just choose to filter by Japan and United Kingdom, and that chart is going to 27 00:01:49,340 --> 00:01:49,820 update. 28 00:01:50,630 --> 00:01:58,970 Now, let's just clear that filter because I prefer to apply any filters to the actual pivot table instead, 29 00:01:59,300 --> 00:02:01,550 which will in turn update the pivot charts. 30 00:02:01,910 --> 00:02:06,920 So let me show you what I mean if I click the filter dropdown in the pivot table, I can do exactly 31 00:02:06,920 --> 00:02:13,010 the same thing here, and that will not only update the pivot table, but also the pivot chart, because 32 00:02:13,010 --> 00:02:15,210 I don't tend to filter on my charts. 33 00:02:15,230 --> 00:02:21,260 I like to remove these gray buttons, and I would say that when you are designing or thinking about 34 00:02:21,260 --> 00:02:27,440 designing your pivot chart, you want to make them as clean looking as possible, so remove any unwanted 35 00:02:27,440 --> 00:02:29,120 or unnecessary items. 36 00:02:29,930 --> 00:02:36,410 So to get rid of these gray buttons, we just need to right click and go to hide all field buttons on 37 00:02:36,410 --> 00:02:38,360 charts to get rid of those. 38 00:02:38,660 --> 00:02:41,480 Now what else on this chart is pretty unnecessary. 39 00:02:42,260 --> 00:02:46,880 Well, if we take a look at my legend, I just have total in there now. 40 00:02:46,880 --> 00:02:53,360 I don't really need the total because the bars themselves are showing me the total, so I'm going to 41 00:02:53,360 --> 00:02:55,280 remove the legend as well. 42 00:02:56,090 --> 00:03:00,650 Now, when it comes to formatting your chart, there are many different ways that you can do this. 43 00:03:01,310 --> 00:03:06,890 Once you've clicked on your chart notice that you have three additional contextual ribbons pivot to 44 00:03:07,190 --> 00:03:11,270 analyze, design and also format. 45 00:03:12,650 --> 00:03:19,430 You also have a little plus button next to your pivot chart, which allows you to customize each element 46 00:03:19,430 --> 00:03:20,150 of your chart. 47 00:03:20,450 --> 00:03:26,040 And there are even more options for formatting your charts in the right click menu as well. 48 00:03:26,690 --> 00:03:30,320 So lots of different places to go to apply formatting. 49 00:03:30,650 --> 00:03:38,510 So if I want to remove this legend, I can click on the plus to show my chart elements and I can d select 50 00:03:38,660 --> 00:03:40,580 legend and that will get rid of it. 51 00:03:41,240 --> 00:03:46,450 Alternatively, if we just undo for a moment there, this is simply just a text box. 52 00:03:46,460 --> 00:03:48,890 I could just choose to delete that sound. 53 00:03:49,220 --> 00:03:53,540 So what else might I want to do to this chart to make it look a little bit more interesting? 54 00:03:54,110 --> 00:03:57,260 Well, I'm probably going to want to add a chart title. 55 00:03:57,950 --> 00:04:02,060 Now again, this is just simply a text box we can double click to edit it. 56 00:04:02,720 --> 00:04:04,490 So what is this chart displaying? 57 00:04:04,520 --> 00:04:10,970 Well, it's the total gross sales by country. 58 00:04:12,340 --> 00:04:16,030 And you can apply whatever formatting you like to this title. 59 00:04:16,600 --> 00:04:18,310 So if I go to the home ribbon. 60 00:04:18,610 --> 00:04:21,280 Let's let's make this bold. 61 00:04:21,610 --> 00:04:23,200 And I'm going to change the color. 62 00:04:23,200 --> 00:04:25,150 Let's make it a dark blue color. 63 00:04:25,570 --> 00:04:27,460 Like so, and maybe even slightly bigger. 64 00:04:27,970 --> 00:04:28,750 There we go. 65 00:04:29,530 --> 00:04:31,760 It might even be that I don't want it in the middle. 66 00:04:31,780 --> 00:04:33,460 I want to move it over to the side. 67 00:04:34,030 --> 00:04:38,860 And if I hold down shift as I drag this, it ensures that it stays in line. 68 00:04:39,190 --> 00:04:40,750 Now what are the formatting minds? 69 00:04:40,750 --> 00:04:42,100 I want to apply here? 70 00:04:42,370 --> 00:04:45,620 Well, maybe I want to change the color of these bars. 71 00:04:45,640 --> 00:04:50,320 Remember, this is just being taken from the theme that's in use in Excel. 72 00:04:50,500 --> 00:04:53,680 Now there are a couple of different ways I can change the colors. 73 00:04:53,890 --> 00:04:58,450 I could go up to the Design tab and then I have a change colors dropdown just here. 74 00:04:59,140 --> 00:05:04,000 Now, this is only going to show me different palettes based on my theme template. 75 00:05:04,240 --> 00:05:09,880 So everything you see here is still within that same color range that my theme uses. 76 00:05:10,180 --> 00:05:15,610 If I want to apply a completely different color, I can simply select the bars and notice. 77 00:05:15,610 --> 00:05:21,190 When I click on one, it's going to select them all, go up to the format tab and then I can manually 78 00:05:21,190 --> 00:05:22,870 choose what color I want to use. 79 00:05:23,440 --> 00:05:29,200 And if it's not part of my theme colors, I can choose more colors and then really choose a color that's 80 00:05:29,200 --> 00:05:30,640 suitable for what I'm doing. 81 00:05:31,240 --> 00:05:33,820 So I'm going to go for this sort of teal green color. 82 00:05:34,090 --> 00:05:34,810 Let's click on. 83 00:05:35,050 --> 00:05:37,870 OK, what else can I add to my chart? 84 00:05:38,770 --> 00:05:40,110 Well, let's click the plus. 85 00:05:40,120 --> 00:05:42,070 I'll take a look at some of the other elements. 86 00:05:42,880 --> 00:05:46,300 What I might choose to do is add something like data labels. 87 00:05:46,660 --> 00:05:50,140 Notice as I hover over, I get a preview of what that looks like. 88 00:05:50,740 --> 00:05:55,810 And this is going to actually show the value from the pivot table at the top of the bar. 89 00:05:55,990 --> 00:06:02,890 Now, sometimes I like to do this because it means that I can effectively remove that y axes and make 90 00:06:02,890 --> 00:06:04,600 my chart look a lot cleaner. 91 00:06:05,440 --> 00:06:07,270 So let's go for data labels. 92 00:06:07,840 --> 00:06:11,620 If we click the arrow, I have a choice of where I want those positioned. 93 00:06:12,250 --> 00:06:15,910 So I'm going to have these on the inside. 94 00:06:15,910 --> 00:06:21,130 And now I know that looks a bit weird at the moment, but we are going to make a change to these bars, 95 00:06:21,250 --> 00:06:23,320 which is going to make this look a lot nicer. 96 00:06:23,950 --> 00:06:26,470 So let's choose inside and for the time being. 97 00:06:27,460 --> 00:06:34,180 So now that I have those values there, I don't really need to have this axes showing the values. 98 00:06:34,360 --> 00:06:39,730 So the easiest way for me to get rid of this is just to select the vertical axes and press delete. 99 00:06:40,090 --> 00:06:46,450 Now, because we've added these data labels on the inside and I'm going to widen out the actual bars 100 00:06:46,450 --> 00:06:47,110 themselves. 101 00:06:47,530 --> 00:06:49,150 So let's click on the bars. 102 00:06:49,660 --> 00:06:53,770 This time, I'm going to jump into format data series. 103 00:06:54,790 --> 00:06:59,830 And what you'll notice here in the pain on the right hand side is that the option at the bottom is gap 104 00:06:59,830 --> 00:07:00,340 width. 105 00:07:00,910 --> 00:07:04,630 So I can make these bars narrower or I can make them wider. 106 00:07:05,290 --> 00:07:09,250 So I'm going to drag them out so that they accommodate those labels nicely. 107 00:07:09,910 --> 00:07:13,900 Now that I've done that, I think I'm going to select my data labels. 108 00:07:14,140 --> 00:07:18,220 I'm going to make them bold, and I'm also going to change the color of them to white. 109 00:07:18,970 --> 00:07:21,520 So for this, we just need to go up to the home tab. 110 00:07:21,700 --> 00:07:23,080 We're going to make them bold. 111 00:07:23,290 --> 00:07:25,990 And then we're going to change the font to whites. 112 00:07:26,380 --> 00:07:30,640 So this is now looking like a really nice, clean looking chart. 113 00:07:30,910 --> 00:07:35,620 Now one important thing when you are working with Charles and particularly when you're formatting them, 114 00:07:36,130 --> 00:07:39,610 it really matters where you're clicked on the chart. 115 00:07:40,270 --> 00:07:44,740 So if I'm clicked on the title, any formatting I apply will apply to the title. 116 00:07:45,550 --> 00:07:51,340 If I'm clicked on the plot area, any formatting I apply will be to this area. 117 00:07:51,970 --> 00:07:57,160 Same with the bars and the data labels and the axes, so on and so forth. 118 00:07:58,000 --> 00:08:03,190 And I will say that for some of these elements that quite hard to select by clicking on the chart. 119 00:08:03,370 --> 00:08:10,630 But if we go up to the format ribbon in this first group here, we can make this a lot easier on ourselves 120 00:08:10,630 --> 00:08:13,090 by using this selection menu. 121 00:08:13,930 --> 00:08:20,620 So if I know that I want to format the plot area of my chart and I know I'm editing the correct area, 122 00:08:21,460 --> 00:08:27,220 what I might want to do now is go to shape, fill and maybe fill that plot area with a light gray background 123 00:08:27,220 --> 00:08:27,580 fill. 124 00:08:28,210 --> 00:08:31,270 So don't forget about this little selection dropdown. 125 00:08:31,480 --> 00:08:36,460 It can come in really useful, particularly if you're struggling to select an object on your child's. 12643

Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.