Original subtitles

1

In this lecture I'm going to use icon sets, up and down arrows, to visually indicate the difference

2

from actual to budget.

3

The icon sets are basically driven by this number but I don't want to add the arrows everywhere.

4

I want to add a threshold to this to only emphasize the bigger differences.

5

I'm going to do this in two different ways.

6

One way, is to use the conditional formatting

7

the icon sets that you see here. And a second way, which is my preferred way is to use my own icon sets.

8

Now, the good or the advantage of using the conditional formatting icon sets is that it's already defined.

9

The color is defined, the shape is defined.

10

Just click on it and it's easy and simple to use.

11

The disadvantage of using them is that you're restricted to these icons and I'm going to be honest with you.

12

I'm not a big fan of these icons.

13

If I have to use them.

14

The one that I normally would use, is this one.

15

And the reason I'm not a big fan is I find them to be a bit too bulky.

16

That's why I prefer to use my own icon sets.

17

But in the first example that I do, I'm going to use these. And in the second example for for this one, for

18

the profit variance to budget,

19

I'm going to use my own icon sets. And then you can pick the method that you like best.

20

To do the first way, using the conditional formatting icon sets, the steps that we did for the

21

data bars is basically identical to the steps that we need to do now.

22

And that means we need to have numbers in the cells that we're going to add

23

icon sets to.

24

That's my number.

25

That's the difference to budget.

26

I'm going to go to conditional formatting, and I can either do it directly from here.

27

Let's just see what happens.

28

The good thing is that you get an immediate preview of how the icons work with this.

29

But you see this one and this one, I just find it like information overload.

30

It's just so many things and you don't really know where to look or what to concentrate on.

31

I'm going to go with this one but I don't want to show the parts where I'm not that far from reaching

32

budget.

33

I don't want to bring attention to those.

34

I'll go to more rules and let's do this from scratch together.

35

You can see here the last one is the icon sets. Now, the icon set that we were looking for is this one.

36

Again you have the option to show icon only. That's what we want.

37

And now you can decide if you want a threshold or not.

38

Here there is a threshold that says if it's greater than or equal to 67 percent then put this. Otherwise

39

put this,

40

and if it's less than 33 then show it as red.

41

One option you do have to minimize the icons that you use.

42

You can click on this and say don't show an icon for if it's within the threshold.

43

Another thing you can do is define your own threshold.

44

In my case let's say I want to bring attention to anything above 5 percent.

45

I have to change the type to number.

46

And now I can say if it's greater than, let's say 5 percent.

47

And here again change it to a number because the moment you change this it resets what you enter in the value.

48

So change it first before you enter your number.

49

And this should be minus 5 percent.

50

Let's take a look, and say okay and that looks good.

51

I'm not putting anything on the values that are within this plus and minus 5 percent.

52

I could make it less bulky by changing the font size and making it smaller, like this.

53

But you don't really have much influence on anything else.

54

The other option that I mentioned is to use symbols in here that you choose yourself.

55

You can pick your own symbols.

56

If I go to insert symbol, you see a lot of useful symbols under Arial, under shapes. And you can see

57

my recent ones are these.

58

.

59

So you can just, you just need to click on them.

60

Let me bring this down so we can see it and insert.

61

Then click on the next one and insert and bring it into your sheet.

62

So what I want to do is to use a formula that's going to dynamically assign the right symbol to these

63

numbers.

64

What I'm going to do is to just highlight this. I'm going to Press Ctrl+C+C and just add it to my clipboard.

65

And I'm going to do Ctrl+C+C on this so that I have both of them available. So that I could easily use

66

it in my formula.

67

I'm going to do if this value is greater or equal to 0.05, then use quotation

68

marks because a symbol is a text.

69

So if it's greater, I want this symbol.

70

Otherwise, if this value is lower than -0.05 then quotation mark, this symbol.

71

Otherwise, I want nothing.

72

I'm going to close bracket.

73

I have to close bracket again.

74

Let's just double check.

75

OK, that looks good.

76

I'm just going to add another If for If this is empty.

77

Because when I scroll down and I don't have any apps here, and it's going to be empty. I want it to show

78

nothing.

79

So if this is empty then empty. Otherwise,

80

do the other if thing, and now I need another bracket.

81

OK, so looks good.

82

Let me close this for now and let's scroll.

83

That looks good.

84

There's nothing written here.

85

Now the next step is to conditionally format this.

86

I'm going to highlight this.

87

Go to Home, conditional formatting, and add a new rule.

88

This time I need a slightly different rule.

89

I want to format only cells that contain. And I can say if the cell value is equal to my symbol.

90

I just pasted this from the clipboard that I had, then

91

what color do I want to have?

92

And the color is not the fill.

93

It's actually the font because the symbol is just like a font.

94

I want it to be a red but not such a bright red.

95

Let's go to a custom and make it lighter.

96

OK.

97

Let's say OK.

98

That looks good.

99

Now I can go back to my conditional formatting and let's add a new rule.

100

But before I do that I'm going to close this because I can't copy from the clipboard directly to this.

101

So I'm just going to close this and copy this symbol here.

102

Let's go back to conditional formatting, manage rules.

103

I'm going to add a new rule. Format only cells that contain.

104

And this time my cell values should be equal to the up arrow, and my color

105

I'm going to go with this green.

106

That looks good and I can make it smaller.

107

Now I can copy the formatting that I like.

108

I'm going to copy this one and paste it here, and also paste it here.

109

The only thing is always when you're copying and pasting conditional formatting.

110

Do check your rules and do check if you are bringing over just conditional formatting multiple times

111

to your cells or not. Because it's so easy to really expand this whole conditional formatting rule.

112

This looks good and this should be fine. Let's just double check these as well.

113

It's the full range and my rules are good. OK so that's how you can use conditional formatting either

114

with your own symbols or with the conditional formatting

115

icon sets in the dashboard reports.