All language subtitles for 6. Show Values As and Summarize Values By

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,930 --> 00:00:12,770 In business, we're going to take a look at the show values as and the summarize values by options. 2 00:00:13,010 --> 00:00:17,840 Now, before we get going, I'm actually going to remove one of these columns from my pivot table. 3 00:00:18,440 --> 00:00:22,240 So I'm going to remove the total profit column simply by dragging it out. 4 00:00:22,250 --> 00:00:24,740 So I just have the total gross sales in that. 5 00:00:24,980 --> 00:00:29,930 Now we've already seen when we were looking at no formatting, if we right click our mouse, we're getting 6 00:00:29,930 --> 00:00:34,400 that contextual menu with options that are relevant to where we currently collect. 7 00:00:35,030 --> 00:00:36,920 And you'll notice that we have a little group here. 8 00:00:36,920 --> 00:00:40,880 Summarize values by and also show values as. 9 00:00:41,060 --> 00:00:42,690 So let's take a look at this one. 10 00:00:42,710 --> 00:00:45,140 First of all, now notice in the menu. 11 00:00:45,140 --> 00:00:52,190 Currently, I have no calculations selected for what I could do is to show the values in this column 12 00:00:52,190 --> 00:00:53,240 in a different way. 13 00:00:53,420 --> 00:00:58,370 So, for example, I could show them as a percentage of the grand total. 14 00:00:58,520 --> 00:01:05,420 So if I click this option, it changes my figures to percentages so I can now see that for Japan, the 15 00:01:05,420 --> 00:01:12,650 Burlington product made up 2.7 one percent of all sales, and I can see this broken down in the subtitles 16 00:01:12,650 --> 00:01:13,160 as well. 17 00:01:13,730 --> 00:01:19,010 So another useful thing I could do would be to use my collapsible and expandable menus just here to 18 00:01:19,010 --> 00:01:27,590 collapse up all of the detail and then just see the percentage of gross sales for each of the products. 19 00:01:28,130 --> 00:01:33,440 So this makes it very easy to tell which product is the most successful I can see here. 20 00:01:33,450 --> 00:01:39,020 Twenty eight point two nine percent of all sales were for the Vermont product. 21 00:01:39,350 --> 00:01:44,720 Now, with these collapsible and expandable items just here, if you don't want to go through and click 22 00:01:44,720 --> 00:01:46,580 the Plus to expand them all again. 23 00:01:46,940 --> 00:01:50,420 Notice that on the pivot table, analyze ribbon in the show group. 24 00:01:50,420 --> 00:01:53,240 At the end, we have a plus and minus button. 25 00:01:53,810 --> 00:01:59,360 So if I click this, it's going to remove them, or I can click again to add back in and I can right 26 00:01:59,360 --> 00:02:05,870 click go down to expand collapse, and I can choose to expand the entire field just to put all those 27 00:02:05,870 --> 00:02:06,410 back again. 28 00:02:06,590 --> 00:02:13,190 Now, if we go back to our right click menu and into show values as we have a lot of different options 29 00:02:13,190 --> 00:02:13,730 in here. 30 00:02:14,180 --> 00:02:16,760 So it really depends how you want to display these figures. 31 00:02:16,970 --> 00:02:22,670 We're currently displaying as a percentage of the grand total, but I could choose to display the percentage 32 00:02:22,670 --> 00:02:23,300 difference. 33 00:02:23,300 --> 00:02:29,420 So if I had, maybe I was comparing years of gross sales, I could see the percentage difference from 34 00:02:29,420 --> 00:02:30,410 the previous year. 35 00:02:30,650 --> 00:02:33,410 I could also display a running total as well. 36 00:02:33,830 --> 00:02:35,990 So let's do running total in. 37 00:02:36,650 --> 00:02:39,050 I'm going to base this on the country field. 38 00:02:39,800 --> 00:02:46,190 So if we click on OK, I can now see what that running total is and a running total is just adding the 39 00:02:46,190 --> 00:02:48,800 previous total to the current total. 40 00:02:49,190 --> 00:02:52,290 So you can see here for United Kingdom, we have 13 million. 41 00:02:52,310 --> 00:02:55,130 The one before was roughly five million. 42 00:02:55,400 --> 00:02:59,020 So the one for Brazil is these two added together. 43 00:02:59,480 --> 00:03:03,740 So we get a nice running total going on there by that country field. 44 00:03:04,490 --> 00:03:08,450 So definitely worth having a little explore and experimenting with some of these. 45 00:03:09,200 --> 00:03:14,630 Now I'm going to put this back to no calculation, so the values are as they were originally. 46 00:03:14,840 --> 00:03:20,090 Now, if we right click once again, we also have a summarize values by option just here. 47 00:03:20,660 --> 00:03:22,520 And this is what I was talking about earlier. 48 00:03:23,090 --> 00:03:26,250 When you drag a field down to that values area. 49 00:03:26,270 --> 00:03:30,290 Excel is going to do its best to work out how you want to summarize your data. 50 00:03:30,860 --> 00:03:35,840 So for the type of values that I'm using in this case, gross sales, it's worked out that I'm probably 51 00:03:35,840 --> 00:03:37,190 going to want to do a sum. 52 00:03:37,190 --> 00:03:40,130 That's how I'm going to want to aggregate this information. 53 00:03:40,850 --> 00:03:46,160 But it might be for some pieces of data you want to do a count or an average or a max. 54 00:03:46,430 --> 00:03:53,450 So for example, if we just remove total gross sales from here and let's replace it with, I'm going 55 00:03:53,450 --> 00:03:56,090 to put product down into values. 56 00:03:56,480 --> 00:04:00,290 Now notice that it's automatically doing a count instead here. 57 00:04:00,800 --> 00:04:06,620 So it's telling me how many have been sold for each country and each product instead of doing a sum 58 00:04:06,620 --> 00:04:07,820 of the gross sales. 59 00:04:08,120 --> 00:04:10,100 So this can be super useful. 60 00:04:10,340 --> 00:04:15,200 Maybe I want to do a count of the product, but I also want to see the gross sales. 61 00:04:15,920 --> 00:04:20,090 So let's grab our gross sales field, drag that back down into values. 62 00:04:20,510 --> 00:04:24,050 Notice here it will put the title back to some of gross sales. 63 00:04:24,050 --> 00:04:27,230 If you remember, we did change that, so let's double click. 64 00:04:27,950 --> 00:04:31,010 Total gross sales. 65 00:04:31,340 --> 00:04:35,270 And once again, notice that the number formatting has also reset. 66 00:04:35,280 --> 00:04:39,110 So you're going to need to go back in and reapply your number formatting. 67 00:04:39,650 --> 00:04:41,450 So let's take those down to zero. 68 00:04:41,930 --> 00:04:45,410 Let's set our negative numbers, and there we go. 69 00:04:45,710 --> 00:04:47,720 So now I have two pieces of information. 70 00:04:47,720 --> 00:04:51,590 I can see a count of the product and I can see the total gross sales. 71 00:04:51,860 --> 00:04:56,480 What about if I wanted to see the average of sales across each of these countries? 72 00:04:56,690 --> 00:05:04,250 Well, what I can do is grab another gross sales field and drag it down to values so you can drag and 73 00:05:04,250 --> 00:05:05,210 use the same. 74 00:05:05,330 --> 00:05:08,600 Fired multiple times in each of these areas. 75 00:05:08,840 --> 00:05:14,120 Now notice once again, it's just given me basically a duplicate of what we've got in the previous column, 76 00:05:14,660 --> 00:05:20,390 but this is where I can right click and go to summarize values by and choose how I want to summarize 77 00:05:20,390 --> 00:05:21,050 that data. 78 00:05:21,740 --> 00:05:26,690 So if I want to see an average as well as the sum, I could change this column to average. 79 00:05:27,170 --> 00:05:29,690 And now I'm seeing my average of gross sales. 80 00:05:30,440 --> 00:05:34,670 Once again, I need to right click and I need to apply my number formatting. 81 00:05:35,330 --> 00:05:36,440 Let's go to currency. 82 00:05:36,950 --> 00:05:40,280 Take that down to zero and set our negative numbers. 83 00:05:42,050 --> 00:05:48,020 And whilst I'm here, I'm actually going to change the custom name and just remove the word of to keep 84 00:05:48,020 --> 00:05:49,730 it consistent and click on. 85 00:05:49,880 --> 00:05:50,420 OK. 8586

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