All language subtitles for 3. Scrolling Data Table in Excel

af Afrikaans
ak Akan
sq Albanian
am Amharic
ar Arabic Download
hy Armenian
az Azerbaijani
eu Basque
be Belarusian
bem Bemba
bn Bengali
bh Bihari
bs Bosnian
br Breton
bg Bulgarian
km Cambodian
ca Catalan
ceb Cebuano
chr Cherokee
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
ee Ewe
fo Faroese
tl Filipino
fi Finnish
fr French
fy Frisian
gaa Ga
gl Galician
ka Georgian
de German
el Greek
gn Guarani
gu Gujarati
ht Haitian Creole
ha Hausa
haw Hawaiian
iw Hebrew
hi Hindi
hmn Hmong
hu Hungarian
is Icelandic
ig Igbo
id Indonesian
ia Interlingua
ga Irish
it Italian
ja Japanese
jw Javanese
kn Kannada
kk Kazakh
rw Kinyarwanda
rn Kirundi
kg Kongo
ko Korean
kri Krio (Sierra Leone)
ku Kurdish
ckb Kurdish (Soranî)
ky Kyrgyz
lo Laothian
la Latin
lv Latvian
ln Lingala
lt Lithuanian
loz Lozi
lg Luganda
ach Luo
lb Luxembourgish
mk Macedonian
mg Malagasy
ms Malay
ml Malayalam
mt Maltese
mi Maori
mr Marathi
mfe Mauritian Creole
mo Moldavian
mn Mongolian
my Myanmar (Burmese)
sr-ME Montenegrin
ne Nepali
pcm Nigerian Pidgin
nso Northern Sotho
no Norwegian
nn Norwegian (Nynorsk)
oc Occitan
or Oriya
om Oromo
ps Pashto
fa Persian
pl Polish
pt-BR Portuguese (Brazil)
pt Portuguese (Portugal)
pa Punjabi
qu Quechua
ro Romanian
rm Romansh
nyn Runyakitara
ru Russian
sm Samoan
gd Scots Gaelic
sr Serbian
sh Serbo-Croatian
st Sesotho
tn Setswana
crs Seychellois Creole
sn Shona
sd Sindhi
si Sinhalese
sk Slovak
sl Slovenian
so Somali
es Spanish
es-419 Spanish (Latin American)
su Sundanese
sw Swahili
sv Swedish
tg Tajik
ta Tamil
tt Tatar
te Telugu
th Thai
ti Tigrinya
to Tonga
lua Tshiluba
tum Tumbuka
tr Turkish
tk Turkmen
tw Twi
ug Uighur
uk Ukrainian
ur Urdu
uz Uzbek
vi Vietnamese
cy Welsh
wo Wolof
xh Xhosa
yi Yiddish
yo Yoruba
zu Zulu
Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:07,080 --> 00:00:13,140 In this lecture I'm going to show you how to create a scrolling table. And that scrolling table doesn't 2 00:00:13,200 --> 00:00:20,520 only scroll through the apps. It also changes depending on the division that the user has selected. 3 00:00:20,520 --> 00:00:26,340 If they select productivity apps, inside that scrolling table they're only going to see the apps that 4 00:00:26,340 --> 00:00:32,100 belong to productivity. And they're going to see 10 of them at the beginning and then they can scroll 5 00:00:32,100 --> 00:00:33,670 to see more. 6 00:00:33,720 --> 00:00:37,200 Let's start by creating the backbone of this table. 7 00:00:38,310 --> 00:00:42,030 The first KPI that we want is the revenue KPI. 8 00:00:42,030 --> 00:00:44,770 Don't worry about the formatting right now. 9 00:00:44,820 --> 00:00:50,940 We need to worry about the formulas and then we can take a look at the formatting and update this as 10 00:00:50,940 --> 00:00:52,600 we see fit. 11 00:00:52,620 --> 00:01:03,070 The first KPI is the actual revenue and then I normally leave a space and go to the next KPI. 12 00:01:03,070 --> 00:01:08,970 If I don't need it later on I can delete it but normally I do need the spaces. 13 00:01:09,160 --> 00:01:12,240 That's percentage change to previous year 14 00:01:12,270 --> 00:01:14,880 but actually I want to have that Delta sign. 15 00:01:14,890 --> 00:01:21,390 I'm going to go to insert symbol and I'm going to take this sign and insert it there 16 00:01:24,870 --> 00:01:25,250 Ok. 17 00:01:25,250 --> 00:01:33,300 That's previous year and here I'm going to show some conditional formatting. I want to have that 18 00:01:33,300 --> 00:01:39,690 bar that visually shows how much I'm above previous year or how much I'm below previous year. 19 00:01:39,690 --> 00:01:45,060 Then I want to have a space and then I want to have the percentage change to budget here. 20 00:01:46,990 --> 00:01:49,960 Ok and then that's the conditional formatting I need for budget, 21 00:01:50,390 --> 00:01:55,680 and then it's the space, and then I want to have the actual of the next one. 22 00:01:56,000 --> 00:02:03,900 The same stack is going to be repeated three times: one for revenue, one for profit, and one for cash. 23 00:02:03,900 --> 00:02:09,690 Let's just do the first stack and once we've done that and we've done the formulas, it's easy to copy it over 24 00:02:09,990 --> 00:02:13,140 to the other stacks. 25 00:02:13,220 --> 00:02:19,580 Let me leave a blank here because that's what I normally use as my borders. 26 00:02:19,580 --> 00:02:28,320 I basically make it a lot smaller, like this one. And then I color this the way I see fit and I kind 27 00:02:28,320 --> 00:02:30,130 of use that as a border. 28 00:02:30,280 --> 00:02:35,790 And because we have the three different series I'm going to use the colors that we originally defined 29 00:02:35,790 --> 00:02:43,190 for actual, previous year, and for budget here. Let me not worry about that right now. Let's worry about 30 00:02:43,280 --> 00:02:46,190 the setup of this table. 31 00:02:46,190 --> 00:02:48,420 What I want here is the index. 32 00:02:48,440 --> 00:02:56,920 I want to have the 1,2 all the way up to 10. That's going to be the first view of the table and then 33 00:02:56,920 --> 00:03:01,510 I can scroll through and see the other 20. 34 00:03:01,540 --> 00:03:08,350 I think we kept space for 20. 15 was the most that we had and we kept five extra in case we have more 35 00:03:08,350 --> 00:03:10,290 apps. 36 00:03:10,310 --> 00:03:15,970 And here I want to see the app names. Obviously, these are going to be formulas. 37 00:03:15,980 --> 00:03:17,830 I don't want to fix them. 38 00:03:17,930 --> 00:03:19,880 Let's start with the formula. 39 00:03:19,940 --> 00:03:25,260 How can I get the one here? 40 00:03:25,270 --> 00:03:27,850 What are they controlled by? 41 00:03:30,660 --> 00:03:32,930 They're controlled by the scroll bar, right? 42 00:03:32,940 --> 00:03:41,370 So I actually need to have a scroll bar and I need to put the scroll bar selection somewhere up here. 43 00:03:41,760 --> 00:03:48,240 And then I can actually write the formulas. Because what it's going to show here is really going to depend 44 00:03:48,330 --> 00:03:50,640 on the position of the scroll bar. 45 00:03:51,150 --> 00:03:58,390 Do I need to worry about this? I don't need to worry about it because I've already worried about 46 00:03:58,390 --> 00:04:05,680 it in the first steps. Because what it's showing here is already the result of this selection of the 47 00:04:05,680 --> 00:04:06,280 division. 48 00:04:06,790 --> 00:04:14,170 If I only manage to scroll through this table that I've created here, I'm pretty much done. 49 00:04:14,170 --> 00:04:18,010 First thing is that I need to insert a scroll bar. 50 00:04:20,960 --> 00:04:23,110 I'm just gonna put it right here for now. 51 00:04:23,120 --> 00:04:32,040 We can move it to the side later on. I'm going to format it. As the minimum value I'm going to put one because 52 00:04:32,040 --> 00:04:37,560 that's gonna work well with any Index function that I'm going to use or even with Offset functions. 53 00:04:37,560 --> 00:04:39,680 As the maximum value, 54 00:04:39,720 --> 00:04:40,710 What do I put here? 55 00:04:40,710 --> 00:04:43,440 I definitely don't want a hundred. 56 00:04:43,440 --> 00:04:49,590 I want to show 20. Now in the first round, when this is number one I actually see 10 57 00:04:49,920 --> 00:04:53,910 and then when I go to number two I see the 11th and 12th and so on. 58 00:04:54,450 --> 00:04:58,480 If I have in total 20, I need to do 20 minus 10. 59 00:04:58,560 --> 00:05:02,880 What I see at the beginning and I'm going to add 1 because my index starts with one. 60 00:05:03,240 --> 00:05:06,980 In the end I just need to show eleven. 61 00:05:07,210 --> 00:05:08,860 The change, the increment change 62 00:05:08,900 --> 00:05:16,580 I do want it to be 1. But every time I click this and I want my page to shift I want to jump by 5. 63 00:05:16,640 --> 00:05:23,020 The cell link is where my answer is going to be. The answer of where is the status of the scroll bar. 64 00:05:23,630 --> 00:05:25,820 I'm going to put it right here. 65 00:05:28,530 --> 00:05:28,940 Ok. 66 00:05:28,950 --> 00:05:39,860 Let me go here and type in what this number is for and that's for table scroll position. 67 00:05:43,900 --> 00:05:49,290 Let's just see if that changes, it's number six. 68 00:05:52,130 --> 00:05:53,390 It's number eleven. 69 00:05:53,480 --> 00:05:55,700 That's what I specified in the form control. 70 00:05:56,240 --> 00:06:02,770 So now what we want is to have this selection driven by this. 71 00:06:03,110 --> 00:06:10,550 We can use the Index function. I'm going to index in the calculation tab. 72 00:06:10,850 --> 00:06:11,960 This part, 73 00:06:14,490 --> 00:06:21,540 including the empty space, my place holders. And now I'm not going to fix the beginning. 74 00:06:21,540 --> 00:06:28,590 I'm only going to fix the end but only the row for the end because then I could pull it across. 75 00:06:28,620 --> 00:06:35,890 Because next is app names in my calculation sheet and then it's actual. The number of rows that I want 76 00:06:35,890 --> 00:06:36,760 to move down. 77 00:06:36,760 --> 00:06:45,350 What's that? It's actually, let's just go up, right here. 78 00:06:45,550 --> 00:06:49,020 That's how much I want to go down. 79 00:06:49,200 --> 00:06:50,730 Do I need any columns? 80 00:06:50,730 --> 00:06:51,200 No. 81 00:06:51,210 --> 00:06:52,980 Do I need to fix this? 82 00:06:52,980 --> 00:06:56,090 Yes, I do. 83 00:06:56,090 --> 00:07:00,140 Is that going to work? 84 00:07:00,150 --> 00:07:01,330 Let's check here. 85 00:07:01,440 --> 00:07:09,270 I've reduced the index range from M17 to M34. At the beginning it was M15. At every point 86 00:07:09,270 --> 00:07:10,200 in time 87 00:07:10,260 --> 00:07:11,520 I'm just moving down, 88 00:07:11,520 --> 00:07:12,520 how much? 89 00:07:12,610 --> 00:07:14,040 One. 90 00:07:14,040 --> 00:07:16,280 It's always showing me the next one. 91 00:07:16,780 --> 00:07:23,230 And when this changes and becomes actually here 2. I'm always showing the second one. 92 00:07:23,310 --> 00:07:26,790 That's why I have 2, 3, 4, and so on. 93 00:07:26,910 --> 00:07:32,160 Can I just extend this formula across till here? 94 00:07:32,160 --> 00:07:36,870 Let's try it. It looks good. 95 00:07:36,870 --> 00:07:46,640 Let's just pull it down, that looks good. Let's just take a look at the numbers. That's fine and it 96 00:07:46,640 --> 00:07:48,650 stops at Number 10. 97 00:07:48,660 --> 00:07:52,890 "Halotot", and that's this one. 98 00:07:52,920 --> 00:08:01,140 If I scroll down, scrolling across, all the way down here. It looks good. 99 00:08:04,150 --> 00:08:14,270 For the change to previous year, I'm just going to copy this, press escape and paste it here. And I'm 100 00:08:14,270 --> 00:08:20,310 also going to paste it here because the setup of the formula is the same. 101 00:08:20,360 --> 00:08:26,300 The only thing that changes is the cell reference. Which cell reference? The index area cell reference. 102 00:08:26,330 --> 00:08:34,160 Where my answer is in. And let's just take a look at the percentage change to previous year. Where 103 00:08:34,160 --> 00:08:40,560 that is and that's column X, and the actual to budget variance that's column AA. 104 00:08:40,850 --> 00:08:54,300 All I need to do is change the O here to X, and here I need to change to AA. And that's AA, and these are 105 00:08:54,300 --> 00:08:56,040 going to be percentages. 106 00:08:56,050 --> 00:08:59,610 I'm just going to hold down Ctrl. 107 00:08:59,610 --> 00:09:03,400 Let's go to the Home tab and click on percentage. 108 00:09:03,720 --> 00:09:08,310 Let's actually show one decimal place here. 109 00:09:08,310 --> 00:09:13,550 For this one, I'm going to change the formatting. I just did Ctrl+1 right there, number. 110 00:09:13,560 --> 00:09:23,010 I want to use the thousand separator and I don't want to see any decimals here. 111 00:09:23,330 --> 00:09:31,550 Ok, and for the color coding what I mentioned before is for actual I want to see the dark color here. 112 00:09:31,570 --> 00:09:33,670 For previous year, 113 00:09:33,670 --> 00:09:39,070 I want to see the light color and for budget 114 00:09:39,070 --> 00:09:47,700 I want to see that other blue type of color here. I'm highlighting the next one too because this is 115 00:09:47,700 --> 00:09:53,280 where I'm planning to put the conditional formatting result. And doing the border in this way shows that 116 00:09:53,280 --> 00:09:55,410 they belong together. 117 00:09:55,410 --> 00:09:59,220 and then this is obviously going to move right there. 118 00:09:59,220 --> 00:10:06,000 So you can see I have a bit of formatting to do here and a bit of updating the formulas for the profit, 119 00:10:06,060 --> 00:10:14,760 and the cash KPIs as well. But I'm just going to put this in fast motion and I'm not going to walk you 120 00:10:14,760 --> 00:10:20,130 through it because it can get quite boring if you're just watching me updating the formatting. I think 121 00:10:20,130 --> 00:10:24,300 you get the idea by now. If you don't and you really do want to follow, 122 00:10:24,390 --> 00:10:26,640 feel free to put this in slow motion. 123 00:11:47,800 --> 00:11:52,300 That's the basic formatting that I'm going to apply to this table until now. 124 00:11:52,300 --> 00:11:57,300 Now, we are going to work on this formatting later on and make it look more dashboard like. 125 00:11:57,340 --> 00:12:04,620 But before we do that I'm just going to add the other elements that I want to this table. But two things 126 00:12:04,620 --> 00:12:09,380 you should do on a continuous basis while we're building this dashboard. 127 00:12:09,450 --> 00:12:16,950 One is, always doublecheck your formulas because it's really easy to just forget to drag down a formula. 128 00:12:17,180 --> 00:12:21,160 It's really easy to just make mistakes with the cell references. 129 00:12:21,210 --> 00:12:26,850 So pick a few random ones, check that each of the numbers is correct and is pulling through correctly 130 00:12:26,850 --> 00:12:28,440 from the calculation tab. 131 00:12:28,440 --> 00:12:33,810 Change your division to another division and test with different apps. 132 00:12:33,810 --> 00:12:37,620 The other thing that you should keep an eye on is your file size. 133 00:12:37,650 --> 00:12:45,150 Occasionally, go to file, go to properties and take a look at this number and keep track of it. Because 134 00:12:45,150 --> 00:12:52,490 it could be that you do something weird and all of a sudden your file size is going to explode. 135 00:12:52,490 --> 00:12:53,180 In the next lecture, 136 00:12:53,180 --> 00:12:59,600 I'm going to show you how to create a bar chart that's conditionally formatted and is based on these 137 00:12:59,600 --> 00:13:00,960 numbers here. 138 00:13:01,070 --> 00:13:09,570 The size of the bars is going to indicate how far away each of these app is from the previous year. 139 00:13:09,630 --> 00:13:15,020 And because we're going to conditionally formatted it's going to be easy to see which apps are really 140 00:13:15,020 --> 00:13:23,300 far from reaching the previous year numbers, and which apps are far over from the previous year's numbers. 141 00:13:23,300 --> 00:13:25,070 That's what we're going to see next. 142 00:13:25,070 --> 00:13:26,270 Stay tuned for that one. 14847

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