馃攳
4 SMART Ways to use Custom Formatting instead of Conditional Formatting in Excel - Part 1 - YouTube
Channel: Leila Gharani
[0]
In today's video I'm going
to show you a neat trick
[3]
that you can use to replace
your conditional formatting
[8]
with Excel's custom formatting.
[10]
Well first off, let's define our phrases.
[13]
Conditional formatting is this option
[16]
and custom formatting is this option,
[20]
it's the one you use to show percentages,
[22]
dates, etc for your values.
[25]
So now let's do a fun quiz,
[27]
what do you think this
formatting is based on?
[31]
Custom or conditional formatting?
[35]
That's right, it's custom formatting.
[37]
What about this one?
[39]
That's conditional formatting
[42]
and this one?
[44]
Did you say conditional formatting?
[46]
Well I have a surprise for you,
[49]
it's actually custom formatting.
[51]
Ill show you how to do that in a second,
[53]
but why would you want
to use custom formatting
[57]
instead of conditional formatting?
[59]
Well I tested the two different methods
[61]
using 40000 lines of data and
the custom formatting method
[66]
was three times faster than
conditional formatting.
[71]
Not only that, but I actually
find custom formatting
[74]
faster to use, even if you
want to build in thresholds,
[78]
like this one.
[79]
I'm going to show you four
different versions that you can use
[83]
custom formatting instead
of conditional formatting.
[87]
In today's video, I'm going
to show you the number one
[89]
and number two versions
and in the next video
[93]
I'm going to show you how
you can use any symbol
[96]
of your choice for deviations
and how to add thresholds
[100]
to your deviations,
[101]
so you don't show arrows
for every single case,
[104]
but only if it's above
or below the threshold
[107]
that you define and
each of these four cases
[111]
uses custom formatting,
by custom formatting
[114]
I mean when you right mouse
click and you go to format cells
[118]
custom and you use one of these, right?
[121]
It's just using this,
[123]
it's not using conditional
formatting at all
[127]
and now take a look at this,
[128]
so this one has thresholds, right?
[131]
So when I click on the
formula, look how simple it is.
[134]
Now I have the version with
conditional formatting here,
[138]
so in this example I'm showing
the same thing as I'm showing
[143]
here, except that I'm using
conditional formatting,
[147]
so I'm using this one
instead of custom formatting.
[151]
Now take a look at the
formula, it's more complex
[154]
and on top, I have to go
here to add a new rule,
[157]
actually add two different
rules to say how up arrows
[161]
should be formatted and how
down arrows should be formatted.
[166]
Custom formatting is not just faster
[169]
in terms of implementation,
but it's also faster
[172]
in terms of Excel's performance.
[176]
The only thing you need to
know is the logic behind
[180]
custom formatting and once that clicks,
[182]
you'll see all the different
opportunities that you have
[185]
where you can improve the
presentation of your reports.
[190]
One thing you need to know though,
[192]
is the rules that go
behind custom formatting
[197]
and I'm going to be honest with you,
[198]
when I first came across
custom formatting,
[203]
I was trying to hide zeros in my report
[205]
and I came here and I
saw these stuff here,
[209]
these symbols and I got scared
[212]
and then I googled it
and then found some code
[215]
somewhere that I copied and pasted here,
[217]
but once I took the time
to actually understand
[221]
the logic behind this, I
realized it's very simple
[225]
and it's really powerful.
[227]
Now, my friend Michael is
working on the ultimate
[232]
custom formatting workbook
that basically covers
[235]
every single aspect to custom formatting
[238]
and he's trying to present this
in a very user friendly way.
[242]
So this whole custom formatting
is really a big topic
[245]
and it's not something I
can cover in a short video,
[249]
but what I'm going to cover
with you is just the basics,
[252]
the building blocks of custom formatting
[254]
to help you understand how
we can get these effects.
[258]
So the main logic is this, first argument
[261]
in custom formatting, like
you see here and here,
[265]
they always have this separator
and the first argument
[268]
is how positive numbers
should be formatted,
[271]
then it's how negative
numbers should be formatted,
[275]
so that's where the code for it comes here
[279]
and then it's how zeros
should be formatted
[282]
and last is how text should be formatted.
[285]
Now in this case, we're
not going to bother
[287]
with text formatting, so we
only need these three arguments
[292]
and this you can see here,
[293]
there's the hashtag
sign and the zero sign,
[296]
they're basically just
placeholders for numbers,
[299]
the only difference is
that the hashtag sign here,
[303]
it's a variable placeholder and
zero is a fixed placeholder.
[309]
Okay, but for now just think
of them as placeholders
[312]
for number, any number.
[314]
As an example, let's say
that we want to have any plus
[319]
numbers, any positive number
that anyone inputs here,
[322]
we want to show it as
the word plus in there,
[327]
not please but plus
[329]
and we want to then put the number, okay?
[334]
So I'm just going to put
that with the hashtag
[337]
and I'll add a space here, okay?
[339]
So we want that number to
say plus and then the number.
[343]
Then, now this argument
was how negative values
[347]
should be formatted, so I'm
just going to put neg space
[350]
and for my number, I want
to put it in brackets, okay?
[356]
So if it's negative, it
should show it in brackets.
[359]
What was this argument here?
[361]
That was how zero values
should be formatted
[365]
and I want to type the
text hide in there, okay?
[370]
So I'm going to leave the last
argument, which was for text,
[373]
empty and we're going to give
this formatting to this cell,
[376]
which is F9, OK.
[378]
So I have nothing in the cell,
[380]
now I'm going to put a positive number,
[382]
I get plus two, so it
shows it to me as plus two
[386]
even though the number inside is just two.
[389]
Now I put minus two, it shows it as neg
[393]
and then the bracket two,
[394]
even though the value in
the cell is minus two.
[398]
Now if I put zero, I get hide, right?
[401]
But the value in the cell is zero, okay?
[405]
So even though this looks like text,
[407]
I could actually say this plus two, okay?
[413]
So you took over the formatting of this
[415]
and it shows it to me as plus two,
[418]
so if I take it away
and go back to general,
[421]
it's a two, right?
[423]
So it doesn't recognize it as text,
[425]
even though it looks like text, but it's,
[428]
the number in there is actually a zero.
[431]
Okay, if I wanted to
hide really the zeros,
[435]
what do I have to do?
[437]
I'm going to press Ctrl one,
[438]
that's the shortcut key to come here,
[441]
I'm just going to delete this part, right?
[444]
I'm just going to keep
that argument here empty,
[447]
not put anything in there.
[450]
So even though the cell value is zero,
[453]
it's not showing me anything, okay?
[455]
So that's how you can hide zeros.
[457]
That was the basics of custom formatting,
[461]
then the other thing is that
you can have more arguments
[465]
in there, so here I put text in there,
[468]
but you can add color,
you can add other things
[472]
that makes the numbers
or the values not stick
[475]
to the cell and so on,
[477]
but right now what we want to work with,
[479]
is work with the colors and you can see
[482]
they always have these square brackets.
[485]
That's just the basics, let
me switch to the draft version
[490]
where I have nothing in, so
these are just empty cells
[494]
and we are going to start
with our custom formatting.
[497]
First thing we need is,
remember I have the up and down
[500]
arrows in this case, right?
[502]
I need those arrows.
[504]
I can get them from insert symbol,
[509]
I have them right here
'cause I use them a lot,
[512]
but you can find them under ariel here
[515]
and they're geometric shapes
[516]
and just click on the ones
that you want and press insert,
[521]
okay, that puts it on your spreadsheet.
[524]
So I'm going to use this one
and I also want this one,
[527]
I'm just going to insert
them somewhere in any cell.
[530]
Let me just put them out of
the way and paste them here,
[536]
I'm going to use them later
for my custom formatting.
[540]
First thing I need to be able
to use custom formatting,
[544]
is that I need to have numbers, right?
[546]
Just like in the example
that we did, I put a two,
[548]
I put a minus two, I put a
zero, they're all numbers
[551]
and then I can disguise these numbers
[554]
to look like whatever I want
[556]
and that's why custom
formatting works great
[559]
for when you want to show
deviations, like in this case.
[563]
The first step is to do the calculations,
[567]
I'm going to do actual
minus previous year,
[571]
highlight this, press F2, Ctrl, enter.
[575]
So now what I'm going to do is
I'm going to need these symbols,
[578]
so I'm going to come here and
press Ctrl c, just to copy them
[583]
now I'm going to highlight
these where I want
[586]
the custom formatting to
apply and press Ctrl one.
[590]
Now go to custom and here, under general,
[594]
we're going to start to do
the rule that we saw before,
[599]
so first argument was what?
[602]
It was how positive numbers
should be formatted, right?
[605]
So I could say, well put
the positive numbers,
[608]
just press paste here,
[611]
but I could say put positive
numbers as this value, right?
[617]
And if it's a negative number,
[618]
I'm just going to press paste
again to paste the other one.
[622]
Show negative numbers
as this value, right?
[626]
And for zero, show nothing, okay?
[629]
So if I do this and say
OK, I get my symbols
[634]
and how do I add color to this?
[636]
I can use the format that we saw before,
[639]
so color is always in square brackets,
[642]
so you have to find your square
brackets on your keyboard
[646]
and for positive, let's
say we wanted green
[648]
and Excel has this built in colors,
[651]
so these are going to be
language sensitive, okay?
[655]
So you have to translate green
to green in your language
[658]
and that's probably going to work
[660]
and here, red for negative.
[665]
Okay and I say OK
[667]
and I get my conditional formatting.
[671]
Now one thing I don't like
about the green in Excel
[674]
is the color is just really too bright.
[678]
To get the full list
of the different colors
[681]
that are available, I put the link here.
[684]
Okay, I'm going to show
you where that takes you.
[689]
It takes you to the Microsoft library
[692]
that shows you the color indexes
[694]
for the different colors
available in custom formatting.
[698]
A nicer green that I usually use is 10
[701]
and sometimes if I want
it be brighter, I use 43
[704]
and 50 is a nice one too,
[706]
so you can see which one you like most.
[709]
So let's use 10
[713]
instead of the green and the code for it
[717]
is you have to write,
if you're in English,
[720]
you have to write color 10, okay?
[724]
So that code you see there
needs to be followed by color,
[727]
but this is language sensitive,
[728]
so you have to change this to
the language of your Excel.
[732]
Now if you're not sure what it is,
[734]
you can download the spreadsheet here
[736]
and when you open it in
your language of Excel
[739]
and you go to custom formatting,
[741]
it will have translated it for you
[743]
so then you know what
you need to use, okay?
[745]
So I do color 10 and I say OK
[748]
and now I get this darker
green version here.
[752]
Okay so that was that,
that was very simple.
[755]
Now we're going to do another
one, we're going to do percentages
[759]
and we're going to show
the deviation like this.
[763]
So first off to get the percentages,
[766]
I'm going to do actual divided
by previous year minus one.
[771]
Okay, we want to format
this as a percentage, right?
[776]
But that's something that we can do then
[778]
in our custom formatting ourselves,
[781]
but for now we're
calculating the deviation.
[784]
Now what we want to do is to,
[787]
not just control the color of this,
[788]
but to also add the symbol in there.
[791]
So I'm going to copy these again,
[793]
now highlight this, press Ctrl one
[797]
to go back to custom formatting.
[800]
Because I changed it to percentage,
[801]
I come to custom, it shows this one
[803]
which is great because I don't
have to type it in anymore,
[807]
I can already use that.
[808]
First argument was how positive
values need to be formatted
[814]
and here we want the positive
values to have a green color,
[820]
so I'm going to go with
this color 10 again.
[823]
I want the percentage there
[825]
and I also want that arrow in there,
[830]
so I'm going to press
Ctrl v and for this one,
[833]
because it's positive, right?
[835]
I want to use this arrow, okay?
[838]
Now, how negative numbers
need to be formatted,
[842]
I'm fine with Excel's default red color,
[846]
I'm going to put minus zero percent
[849]
then I want the arrow again,
so I'm going to press Ctrl v
[854]
and that's the one that we
need and delete this one
[859]
and zeros I don't want to show at
all, so I'm going to skip that.
[864]
Let's see how that looks.
[867]
Okay, that's not bad.
[869]
So if you want to tweak this,
[871]
to give it more breathing space,
[873]
so you want to move the numbers
to one side of your cell
[876]
and the arrows to the other side,
[879]
what you can do is to use this code.
[882]
If you put a space, it puts
a manual space between them,
[887]
so let me just put two spaces.
[890]
You can see it expands it a little bit,
[893]
but if you want it that
no matter how big or wide
[896]
your cell is, it always
puts the numbers on one side
[899]
and the arrows on the other side,
[901]
what you can do is to use this syntax,
[905]
I'm just going to get
rid of these spaces here.
[909]
The syntax is to put the star sign here.
[914]
So the star sign is to
repeat the next value
[917]
and if for the next value you put a space,
[920]
it means it repeats the space
[922]
until the size of the cell is reached
[926]
and then it puts this.
[928]
Okay, so I'm just going to show you,
[929]
it's difficult to explain this,
it's easier when you see it.
[932]
So right after the number,
I'm going to put the star sign
[935]
and put a space in there and then press OK
[940]
and now you see what I mean.
[941]
So if this is smaller they come closer,
[945]
if this is wider they are further apart.
[949]
That was how you can get
number one and number two done
[952]
and in the next video,
I'm going to show you
[954]
how to set up number
three and number four.
[958]
Thank you for watching and
see you in the next video.
[961]
If you like this video, don't
forget to leave a thumbs up
[964]
and for more of these videos,
[966]
why not subscribe to
my channel for updates
[969]
when new videos like this one come out.
Most Recent Videos:
You can go back to the homepage right here: Homepage