All language subtitles for 11. Exercise 06

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:05,750 --> 00:00:11,690 It's time now for us to do exercise six, where we're going to practice the skills that we've learned 2 00:00:11,690 --> 00:00:14,990 in this section relating to pivot tables. 3 00:00:15,230 --> 00:00:24,530 So currently we have our clean data set in an Excel table and that Excel table is called stool underscore 4 00:00:24,590 --> 00:00:25,190 sales. 5 00:00:25,460 --> 00:00:30,920 So now what I'd like you to do is to create a pivot table using this data. 6 00:00:31,100 --> 00:00:39,110 Once you've created the pivot table, I'd like you to add the category to columns, the town to rows 7 00:00:39,110 --> 00:00:46,730 and the sales to values, and I'd like you to rename the headings on the pivot table to make them meaningful. 8 00:00:46,970 --> 00:00:54,350 I'd like you to turn off grand totals and apply currency number formatting to all cells that contain 9 00:00:54,350 --> 00:00:57,260 values with zero decimal places. 10 00:00:57,500 --> 00:01:02,600 I then like you to apply a top five filter by sum of sales. 11 00:01:02,840 --> 00:01:08,960 And then finally, I'd like you to name the pivot table sales by town cats. 12 00:01:09,260 --> 00:01:14,420 So quite a bit of work to do here now is the time to pause the video and give it a go. 13 00:01:14,840 --> 00:01:19,100 If you get stuck or you won't see my answer, then please keep watching. 14 00:01:19,520 --> 00:01:22,820 So the first thing we're going to do here is we're going to create a pivot table. 15 00:01:23,420 --> 00:01:29,480 So as I'm already on the table design ribbon, I'm going to click on Summarize with Pivot Table. 16 00:01:29,870 --> 00:01:36,200 It's picked up my store sales range, and I want to add the pivot table to a new worksheet. 17 00:01:36,830 --> 00:01:37,610 Let's click on. 18 00:01:37,970 --> 00:01:45,830 OK, so now I get my pivot table fields over on the right hand side, and I asked you to add the category 19 00:01:46,340 --> 00:01:54,740 into the columns, the town into the rows and the sales into the values. 20 00:01:55,040 --> 00:02:00,410 The next thing we need to do here is we need to rename our pivot table labels just to make them a little 21 00:02:00,410 --> 00:02:01,430 bit more meaningful. 22 00:02:02,000 --> 00:02:08,990 So where we have row labels, I'm going to double click and I'm going to change this to towns where 23 00:02:08,990 --> 00:02:10,520 we have column labels. 24 00:02:10,520 --> 00:02:13,550 We're going to change this two categories. 25 00:02:13,880 --> 00:02:17,720 The next thing I asked you to do was to turn off grand totals. 26 00:02:18,200 --> 00:02:25,190 So I have a grand totals column and also if I go all the way down to the bottom are grand totals Ryan. 27 00:02:25,640 --> 00:02:28,430 And I want to turn these off for both of these. 28 00:02:29,210 --> 00:02:37,310 So up to the pivot table design ribbon into the layout group and we want to turn grand totals off four 29 00:02:37,310 --> 00:02:38,720 rows and columns. 30 00:02:39,050 --> 00:02:45,950 I then asked you to apply currency number formatting with zero decimal places to all of the values in 31 00:02:45,950 --> 00:02:46,790 the pivot table. 32 00:02:47,510 --> 00:02:49,640 Now you could do this column by column. 33 00:02:49,910 --> 00:02:57,380 I find it easier to select a cross section across all of the columns, right click and go into value 34 00:02:57,380 --> 00:02:58,310 field settings. 35 00:02:59,210 --> 00:03:00,530 No format. 36 00:03:00,650 --> 00:03:02,630 And we're going to choose currency. 37 00:03:03,080 --> 00:03:04,820 Take this down to zero. 38 00:03:05,240 --> 00:03:07,610 Click on OK and OK again. 39 00:03:07,850 --> 00:03:09,710 And it's going to change them all. 40 00:03:10,100 --> 00:03:16,550 Now, currently with the pivot table in this state, we have a lot of data and we want to create some 41 00:03:16,550 --> 00:03:21,170 pivot charts in the next session, and this is way too much data to put in a chart. 42 00:03:21,860 --> 00:03:27,530 So the next thing I asked you to do was to create a top five filter by sum of sales. 43 00:03:28,340 --> 00:03:31,760 So for this, we can right click and go to filter. 44 00:03:32,450 --> 00:03:39,710 I'm going to use the top 10 filter and then we're going to modify that to show the top five items by 45 00:03:39,710 --> 00:03:41,030 the sum of sales. 46 00:03:41,780 --> 00:03:43,070 Click on OK. 47 00:03:43,340 --> 00:03:47,690 And now I'm just seeing the highest selling categories and towns. 48 00:03:47,900 --> 00:03:51,560 And then the final thing I asked you to do was just to give this pivot table a name. 49 00:03:52,160 --> 00:03:54,820 So let's jump across to pivot table analyze. 50 00:03:54,830 --> 00:04:02,720 And in the pivot table name group, we're going to call this sales by town and cat. 51 00:04:03,740 --> 00:04:04,730 And hit enter. 5132

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