Original subtitles

1

In this lecture I'm going to show you how to create a scrolling table. And that scrolling table doesn't

2

only scroll through the apps. It also changes depending on the division that the user has selected.

3

If they select productivity apps, inside that scrolling table they're only going to see the apps that

4

belong to productivity. And they're going to see 10 of them at the beginning and then they can scroll

5

to see more.

6

Let's start by creating the backbone of this table.

7

The first KPI that we want is the revenue KPI.

8

Don't worry about the formatting right now.

9

We need to worry about the formulas and then we can take a look at the formatting and update this as

10

we see fit.

11

The first KPI is the actual revenue and then I normally leave a space and go to the next KPI.

12

If I don't need it later on I can delete it but normally I do need the spaces.

13

That's percentage change to previous year

14

but actually I want to have that Delta sign.

15

I'm going to go to insert symbol and I'm going to take this sign and insert it there

16

Ok.

17

That's previous year and here I'm going to show some conditional formatting. I want to have that

18

bar that visually shows how much I'm above previous year or how much I'm below previous year.

19

Then I want to have a space and then I want to have the percentage change to budget here.

20

Ok and then that's the conditional formatting I need for budget,

21

and then it's the space, and then I want to have the actual of the next one.

22

The same stack is going to be repeated three times: one for revenue, one for profit, and one for cash.

23

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

to the other stacks.

25

Let me leave a blank here because that's what I normally use as my borders.

26

I basically make it a lot smaller, like this one. And then I color this the way I see fit and I kind

27

of use that as a border.

28

And because we have the three different series I'm going to use the colors that we originally defined

29

for actual, previous year, and for budget here. Let me not worry about that right now. Let's worry about

30

the setup of this table.

31

What I want here is the index.

32

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

I can scroll through and see the other 20.

34

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

apps.

36

And here I want to see the app names. Obviously, these are going to be formulas.

37

I don't want to fix them.

38

Let's start with the formula.

39

How can I get the one here?

40

What are they controlled by?

41

They're controlled by the scroll bar, right?

42

So I actually need to have a scroll bar and I need to put the scroll bar selection somewhere up here.

43

And then I can actually write the formulas. Because what it's going to show here is really going to depend

44

on the position of the scroll bar.

45

Do I need to worry about this? I don't need to worry about it because I've already worried about

46

it in the first steps. Because what it's showing here is already the result of this selection of the

47

division.

48

If I only manage to scroll through this table that I've created here, I'm pretty much done.

49

First thing is that I need to insert a scroll bar.

50

I'm just gonna put it right here for now.

51

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

that's gonna work well with any Index function that I'm going to use or even with Offset functions.

53

As the maximum value,

54

What do I put here?

55

I definitely don't want a hundred.

56

I want to show 20. Now in the first round, when this is number one I actually see 10

57

and then when I go to number two I see the 11th and 12th and so on.

58

If I have in total 20, I need to do 20 minus 10.

59

What I see at the beginning and I'm going to add 1 because my index starts with one.

60

In the end I just need to show eleven.

61

The change, the increment change

62

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

The cell link is where my answer is going to be. The answer of where is the status of the scroll bar.

64

I'm going to put it right here.

65

Ok.

66

Let me go here and type in what this number is for and that's for table scroll position.

67

Let's just see if that changes, it's number six.

68

It's number eleven.

69

That's what I specified in the form control.

70

So now what we want is to have this selection driven by this.

71

We can use the Index function. I'm going to index in the calculation tab.

72

This part,

73

including the empty space, my place holders. And now I'm not going to fix the beginning.

74

I'm only going to fix the end but only the row for the end because then I could pull it across.

75

Because next is app names in my calculation sheet and then it's actual. The number of rows that I want

76

to move down.

77

What's that? It's actually, let's just go up, right here.

78

That's how much I want to go down.

79

Do I need any columns?

80

No.

81

Do I need to fix this?

82

Yes, I do.

83

Is that going to work?

84

Let's check here.

85

I've reduced the index range from M17 to M34. At the beginning it was M15. At every point

86

in time

87

I'm just moving down,

88

how much?

89

One.

90

It's always showing me the next one.

91

And when this changes and becomes actually here 2. I'm always showing the second one.

92

That's why I have 2, 3, 4, and so on.

93

Can I just extend this formula across till here?

94

Let's try it. It looks good.

95

Let's just pull it down, that looks good. Let's just take a look at the numbers. That's fine and it

96

stops at Number 10.

97

"Halotot", and that's this one.

98

If I scroll down, scrolling across, all the way down here. It looks good.

99

For the change to previous year, I'm just going to copy this, press escape and paste it here. And I'm

100

also going to paste it here because the setup of the formula is the same.

101

The only thing that changes is the cell reference. Which cell reference? The index area cell reference.

102

Where my answer is in. And let's just take a look at the percentage change to previous year. Where

103

that is and that's column X, and the actual to budget variance that's column AA.

104

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

going to be percentages.

106

I'm just going to hold down Ctrl.

107

Let's go to the Home tab and click on percentage.

108

Let's actually show one decimal place here.

109

For this one, I'm going to change the formatting. I just did Ctrl+1 right there, number.

110

I want to use the thousand separator and I don't want to see any decimals here.

111

Ok, and for the color coding what I mentioned before is for actual I want to see the dark color here.

112

For previous year,

113

I want to see the light color and for budget

114

I want to see that other blue type of color here. I'm highlighting the next one too because this is

115

where I'm planning to put the conditional formatting result. And doing the border in this way shows that

116

they belong together.

117

and then this is obviously going to move right there.

118

So you can see I have a bit of formatting to do here and a bit of updating the formulas for the profit,

119

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

through it because it can get quite boring if you're just watching me updating the formatting. I think

121

you get the idea by now. If you don't and you really do want to follow,

122

feel free to put this in slow motion.

123

That's the basic formatting that I'm going to apply to this table until now.

124

Now, we are going to work on this formatting later on and make it look more dashboard like.

125

But before we do that I'm just going to add the other elements that I want to this table. But two things

126

you should do on a continuous basis while we're building this dashboard.

127

One is, always doublecheck your formulas because it's really easy to just forget to drag down a formula.

128

It's really easy to just make mistakes with the cell references.

129

So pick a few random ones, check that each of the numbers is correct and is pulling through correctly

130

from the calculation tab.

131

Change your division to another division and test with different apps.

132

The other thing that you should keep an eye on is your file size.

133

Occasionally, go to file, go to properties and take a look at this number and keep track of it. Because

134

it could be that you do something weird and all of a sudden your file size is going to explode.

135

In the next lecture,

136

I'm going to show you how to create a bar chart that's conditionally formatted and is based on these

137

numbers here.

138

The size of the bars is going to indicate how far away each of these app is from the previous year.

139

And because we're going to conditionally formatted it's going to be easy to see which apps are really

140

far from reaching the previous year numbers, and which apps are far over from the previous year's numbers.

141

That's what we're going to see next.

142

Stay tuned for that one.