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.