In today's video I'm going
to show you a neat trick that you can use to replace
your conditional formatting with Excel's custom formatting. Well first off, let's define our phrases. Conditional formatting is this option and custom formatting is this option, it's the one you use to show percentages, dates, etc for your values. So now let's do a fun quiz, what do you think this
formatting is based on? Custom or conditional formatting? That's right, it's custom formatting. What about this one? That's conditional formatting and this one? Did you say conditional formatting? Well I have a surprise for you, it's actually custom formatting. Ill show you how to do that in a second, but why would you want
to use custom formatting instead of conditional formatting? Well I tested the two different methods using 40000 lines of data and
the custom formatting method was three times faster than
conditional formatting. Not only that, but I actually
find custom formatting faster to use, even if you
want to build in thresholds, like this one. I'm going to show you four
different versions that you can use custom formatting instead
of conditional formatting. In today's video, I'm going
to show you the number one and number two versions
and in the next video I'm going to show you how
you can use any symbol of your choice for deviations
and how to add thresholds to your deviations, so you don't show arrows
for every single case, but only if it's above
or below the threshold that you define and
each of these four cases uses custom formatting,
by custom formatting I mean when you right mouse
click and you go to format cells custom and you use one of these, right? It's just using this, it's not using conditional
formatting at all and now take a look at this, so this one has thresholds, right? So when I click on the
formula, look how simple it is. Now I have the version with
conditional formatting here, so in this example I'm showing
the same thing as I'm showing here, except that I'm using
conditional formatting, so I'm using this one
instead of custom formatting. Now take a look at the
formula, it's more complex and on top, I have to go
here to add a new rule, actually add two different
rules to say how up arrows should be formatted and how
down arrows should be formatted. Custom formatting is not just faster in terms of implementation,
but it's also faster in terms of Excel's performance. The only thing you need to
know is the logic behind custom formatting and once that clicks, you'll see all the different
opportunities that you have where you can improve the
presentation of your reports. One thing you need to know though, is the rules that go
behind custom formatting and I'm going to be honest with you, when I first came across
custom formatting, I was trying to hide zeros in my report and I came here and I
saw these stuff here, these symbols and I got scared and then I googled it
and then found some code somewhere that I copied and pasted here, but once I took the time
to actually understand the logic behind this, I
realized it's very simple and it's really powerful. Now, my friend Michael is
working on the ultimate custom formatting workbook
that basically covers every single aspect to custom formatting and he's trying to present this
in a very user friendly way. So this whole custom formatting
is really a big topic and it's not something I
can cover in a short video, but what I'm going to cover
with you is just the basics, the building blocks of custom formatting to help you understand how
we can get these effects. So the main logic is this, first argument in custom formatting, like
you see here and here, they always have this separator
and the first argument is how positive numbers
should be formatted, then it's how negative
numbers should be formatted, so that's where the code for it comes here and then it's how zeros
should be formatted and last is how text should be formatted. Now in this case, we're
not going to bother with text formatting, so we
only need these three arguments and this you can see here, there's the hashtag
sign and the zero sign, they're basically just
placeholders for numbers, the only difference is
that the hashtag sign here, it's a variable placeholder and
zero is a fixed placeholder. Okay, but for now just think
of them as placeholders for number, any number. As an example, let's say
that we want to have any plus numbers, any positive number
that anyone inputs here, we want to show it as
the word plus in there, not please but plus and we want to then put the number, okay? So I'm just going to put
that with the hashtag and I'll add a space here, okay? So we want that number to
say plus and then the number. Then, now this argument
was how negative values should be formatted, so I'm
just going to put neg space and for my number, I want
to put it in brackets, okay? So if it's negative, it
should show it in brackets. What was this argument here? That was how zero values
should be formatted and I want to type the
text hide in there, okay? So I'm going to leave the last
argument, which was for text, empty and we're going to give
this formatting to this cell, which is F9, OK. So I have nothing in the cell, now I'm going to put a positive number, I get plus two, so it
shows it to me as plus two even though the number inside is just two. Now I put minus two, it shows it as neg and then the bracket two, even though the value in
the cell is minus two. Now if I put zero, I get hide, right? But the value in the cell is zero, okay? So even though this looks like text, I could actually say this plus two, okay? So you took over the formatting of this and it shows it to me as plus two, so if I take it away
and go back to general, it's a two, right? So it doesn't recognize it as text, even though it looks like text, but it's, the number in there is actually a zero. Okay, if I wanted to
hide really the zeros, what do I have to do? I'm going to press Ctrl one, that's the shortcut key to come here, I'm just going to delete this part, right? I'm just going to keep
that argument here empty, not put anything in there. So even though the cell value is zero, it's not showing me anything, okay? So that's how you can hide zeros. That was the basics of custom formatting, then the other thing is that
you can have more arguments in there, so here I put text in there, but you can add color,
you can add other things that makes the numbers
or the values not stick to the cell and so on, but right now what we want to work with, is work with the colors and you can see they always have these square brackets. That's just the basics, let
me switch to the draft version where I have nothing in, so
these are just empty cells and we are going to start
with our custom formatting. First thing we need is,
remember I have the up and down arrows in this case, right? I need those arrows. I can get them from insert symbol, I have them right here
'cause I use them a lot, but you can find them under ariel here and they're geometric shapes and just click on the ones
that you want and press insert, okay, that puts it on your spreadsheet. So I'm going to use this one
and I also want this one, I'm just going to insert
them somewhere in any cell. Let me just put them out of
the way and paste them here, I'm going to use them later
for my custom formatting. First thing I need to be able
to use custom formatting, is that I need to have numbers, right? Just like in the example
that we did, I put a two, I put a minus two, I put a
zero, they're all numbers and then I can disguise these numbers to look like whatever I want and that's why custom
formatting works great for when you want to show
deviations, like in this case. The first step is to do the calculations, I'm going to do actual
minus previous year, highlight this, press F2, Ctrl, enter. So now what I'm going to do is
I'm going to need these symbols, so I'm going to come here and
press Ctrl c, just to copy them now I'm going to highlight
these where I want the custom formatting to
apply and press Ctrl one. Now go to custom and here, under general, we're going to start to do
the rule that we saw before, so first argument was what? It was how positive numbers
should be formatted, right? So I could say, well put
the positive numbers, just press paste here, but I could say put positive
numbers as this value, right? And if it's a negative number, I'm just going to press paste
again to paste the other one. Show negative numbers
as this value, right? And for zero, show nothing, okay? So if I do this and say
OK, I get my symbols and how do I add color to this? I can use the format that we saw before, so color is always in square brackets, so you have to find your square
brackets on your keyboard and for positive, let's
say we wanted green and Excel has this built in colors, so these are going to be
language sensitive, okay? So you have to translate green
to green in your language and that's probably going to work and here, red for negative. Okay and I say OK and I get my conditional formatting. Now one thing I don't like
about the green in Excel is the color is just really too bright. To get the full list
of the different colors that are available, I put the link here. Okay, I'm going to show
you where that takes you. It takes you to the Microsoft library that shows you the color indexes for the different colors
available in custom formatting. A nicer green that I usually use is 10 and sometimes if I want
it be brighter, I use 43 and 50 is a nice one too, so you can see which one you like most. So let's use 10 instead of the green and the code for it is you have to write,
if you're in English, you have to write color 10, okay? So that code you see there
needs to be followed by color, but this is language sensitive, so you have to change this to
the language of your Excel. Now if you're not sure what it is, you can download the spreadsheet here and when you open it in
your language of Excel and you go to custom formatting, it will have translated it for you so then you know what
you need to use, okay? So I do color 10 and I say OK and now I get this darker
green version here. Okay so that was that,
that was very simple. Now we're going to do another
one, we're going to do percentages and we're going to show
the deviation like this. So first off to get the percentages, I'm going to do actual divided
by previous year minus one. Okay, we want to format
this as a percentage, right? But that's something that we can do then in our custom formatting ourselves, but for now we're
calculating the deviation. Now what we want to do is to, not just control the color of this, but to also add the symbol in there. So I'm going to copy these again, now highlight this, press Ctrl one to go back to custom formatting. Because I changed it to percentage, I come to custom, it shows this one which is great because I don't
have to type it in anymore, I can already use that. First argument was how positive
values need to be formatted and here we want the positive
values to have a green color, so I'm going to go with
this color 10 again. I want the percentage there and I also want that arrow in there, so I'm going to press
Ctrl v and for this one, because it's positive, right? I want to use this arrow, okay? Now, how negative numbers
need to be formatted, I'm fine with Excel's default red color, I'm going to put minus zero percent then I want the arrow again,
so I'm going to press Ctrl v and that's the one that we
need and delete this one and zeros I don't want to show at
all, so I'm going to skip that. Let's see how that looks. Okay, that's not bad. So if you want to tweak this, to give it more breathing space, so you want to move the numbers
to one side of your cell and the arrows to the other side, what you can do is to use this code. If you put a space, it puts
a manual space between them, so let me just put two spaces. You can see it expands it a little bit, but if you want it that
no matter how big or wide your cell is, it always
puts the numbers on one side and the arrows on the other side, what you can do is to use this syntax, I'm just going to get
rid of these spaces here. The syntax is to put the star sign here. So the star sign is to
repeat the next value and if for the next value you put a space, it means it repeats the space until the size of the cell is reached and then it puts this. Okay, so I'm just going to show you, it's difficult to explain this,
it's easier when you see it. So right after the number,
I'm going to put the star sign and put a space in there and then press OK and now you see what I mean. So if this is smaller they come closer, if this is wider they are further apart. That was how you can get
number one and number two done and in the next video,
I'm going to show you how to set up number
three and number four. Thank you for watching and
see you in the next video. If you like this video, don't
forget to leave a thumbs up and for more of these videos, why not subscribe to
my channel for updates when new videos like this one come out.
