Original subtitles

1

In this lecture I'm going to add the data bars to my scrolling table.

2

What I want to see is right in front of previous year. I want to see bars and the bars are related

3

to the value, that variance from actual to previous year.

4

The bigger the bar is the further away I am from reaching previous year numbers.

5

This can be a good thing because I can be further away in terms of I am higher this year than previous

6

year. But it can also be a bad thing because I might be far lower than my previous year values.

7

So this is something that I want to visually show in the bars.

8

It's going to look like a chart but it's not going to be a chart.

9

It's actually going to be conditional formatting.

10

I want to use these in here. Now, to use those these data bars

11

I need to have values in my cells. Because the cell values tell Excel how big should the bar be. And what

12

it does is it's kind of like an array.

13

It compares the size of the bar to the other values that are in this list.

14

The values here are basically these values.

15

I'm just going to reference the cells right beside it, and then I'm going to go to conditional formatting.

16

And add the data bars directly from here. I have a few choices.

17

I'll go with this one. You can see this is the same blue that I specified as accent 1 in my setup

18

for the theme, for the colors here under page layout.

19

That's the same color that I get as my positive values.

20

And actually I'm fine with that. But I want to change this a bit because I don't want to see those values

21

again.

22

I already have the values here and I don't like the red color here, it's too bright.

23

It really is too distracting,

24

I find in this report. And I also don't want to see those dashes for the axis. To update it I'm going to

25

go to conditional formatting. And I can go to manage rows and here I see my row.

26

I see that it's applied to the whole thing and I can go and edit it. In the editing,

27

I have a few options like show bar only.

28

That's what I want.

29

Do not show me the numbers.

30

And here I can control the fill that I see.

31

So that's the positive values.

32

And then here I can go to negative value and axis and I have some additional options. For the fill color

33

for negative,

34

I want it to be a gray. I think I'll go with this one.

35

And for the axis color, I don't want the black and I'm going to go with white. Let's see if we can apply

36

it.

37

That looks good.

38

Now, I'm going to scroll through this.

39

Did you notice something right there?

40

Did you see what happened?

41

Keep your eye on number 12.

42

OK.

43

Keep your eye on the size of the bar here.

44

Ok I'm going to scroll down.

45

Did you see what happened?

46

You see, it gets bigger.

47

Why does it get bigger?

48

Because the conditional formatting is always comparing the values here, to the other ones here.

49

It's completely ignoring all the other ones that are behind this.

50

Because when I'm scrolling I'm actually changing these values here.

51

It's comparing it in respect to that,

52

and then the sizes are changing because the maximum in this range is changing. The minimum in this range is

53

changing. And I don't want that because I don't want people to think, that looks really big

54

and then they scroll down and then it's smaller. Or smaller and then it gets really big.

55

I want to have a consistent size while I'm scrolling through this list and there is an option for

56

that.

57

Let's go back to manage rules and find that option.

58

Ok, it's right here.

59

Minimum, I have it on automatic.

60

That's the default one, and maximum as well.

61

We could give it a minimum and a maximum.

62

Let's see our options here.

63

We can give it a number. We can give it a percent.

64

But I don't want the percent because that's going to be the percent of my percent.

65

I don't want that.

66

My actual percent is my number.

67

I want the number but I need to give it a value, and I need to reference it somewhere in my cell.

68

I don't have the maximum calculated anywhere yet.

69

That's what we want.

70

We want to have a part in the calculation sheet.

71

A section that has the maximum of not just these what it sees in the dashboard but of the entire app range.

72

Let me click cancel and we can just leave this. Let's close.

73

Let's see where to put it.

74

These are my percentages. They're going to be related to basically these three KPIs because I

75

want the same bars for the profit, and cash as well.

76

So I'm going to put it down here.

77

Let me be good and put headers as well here.

78

This is calculations for data bars in scrolling table.

79

Now I need a Min for revenue percentage and a Max for revenue percentage.

80

This is the entire set.

81

Right?

82

And this is dynamic because every time someone changes this here, these are going to change.

83

I can use this entire range and reference that for my Min and Max. My formula is the Min of my revenue

84

range and that's this one.

85

And for the Max it's basically the same thing.

86

It's just the Max of my revenue range.

87

Now I'm going to go to the dashboard. I'm going to go to conditional formatting, and manage rules.

88

And notice I'm not highlighting the whole area.

89

Excel figures it out because it still applies whatever I change to this whole range here.

90

And I'm going to edit my rule. And now for the minimum I'm going to say it's a number and that number

91

is on this sheet.

92

It's this one. And for the maximum,

93

I also want a number, and that's this number.

94

I'm going to apply this now.

95

See, the size of the bars changed.

96

Now let's go back and do some scrolling.

97

Let's start from the top and just keep an eye on the size of the bars, and see if they change.

98

Now let's scroll down. This is my Max, right?

99

Get this Max out of view. You see this one

100

didn't change.

101

The size is the same as before.

102

That makes everything consistent.

103

Now all I have to do for the other ones is to just to repeat the same thing. And with conditional formatting

104

it's pretty easy.

105

You can copy this section in and paste it here because the formulas behind this are relative.

106

They are going to update and the conditional formatting is going to come with it.

107

But the only thing I do need to update are the references to my Min and Max because I don't want my

108

profit to reference my revenue.

109

I need it to be consistent.

110

So I need to update that as well.

111

I'm just going to go ahead and quickly add the Mins and the Maxs for the profit and cash, and update

112

my data bars for those as well.

113

Now that I have these,

114

let's go to the dashboard and let's copy this conditional formatting section. And I'm going to paste

115

it right here and then I'm going to paste it right here as well.

116

Let's go to the first one,

117

and manage rules. Let's just make sure it's this section and I'm going to edit it.

118

Now this was based on the first one.

119

That's the 39 and that's 40.

120

Actually I want this to be 41

121

and this is going to be 42.

122

Ok.

123

I'm going to apply this, good. Now, the same for here.

124

Edit.

125

This is going to be this one, which is going to be 43.

126

And this one is going to be 44, this one.

127

So that looks good.

128

They're all consistent, their size is not going to change and it's a nice effect to quickly bring

129

attention to the biggest deviations.

130

Right now, when I see this. The first thing I see is wow.

131

.

132

Right App is actually doing great compared to previous year. And they're actually doing great in terms of cash

133

compared to previous year, and not bad in terms of their profit.

134

It's a real fast way of looking at the ones that have the biggest deviations.

135

If you take a look at Halotot, it's not doing so well in terms of profit.

136

I chose gray here but it's really a matter of preference.

137

I just didn't want the red to overrule this whole dashboard, and be too distractive in terms of looking

138

at the other data that's also important here.

139

This is dynamic, when I switch between productivity apps to game apps, all the bars are going to update,

140

all the values are going to update. And I can scroll through and I notice here that Jellyfish is actually

141

a really good app in terms of revenue,

142

and in terms of profit when I compare it to previous year data. In the next lecture I'm going to

143

do a different type of formatting for the budget where we're going to use arrows, up and down arrows,

144

to highlight where we have positive differences and negative differences.

145

But we're also going to add a threshold to this because we don't just want to populate this whole dashboard

146

with up and down arrows.

147

Okay.

148

Stay tuned for that in the next lecture.