hello and welcome to this video tutorial
from computer Garga comm and in this
video we are going to look at how to
insert the tick symbol into an Excel
spreadsheet
I was asked the other day on one of my
training sessions how to do this and my
initial response was why are you doing
it what is the scenario that you're
using because there are so many
different ways that we can get this tick
symbol to appear and we are going to
look at five ways of doing that in this
video
now let's begin with this table I have
in the top left corner where I have a
huge list of tasks and the status in
column B as to whether T it is completed
yet whether it's still in progress or
whether it has not yet started and maybe
we want to have a tick to resemble
though its complete in column C now the
simplest way of doing this is to insert
the symbol so if I'm on cell c2 and I
come to my insert tab over to the symbol
button that will open up this window
with all of the symbols I maybe when you
do that you will see a tick symbol if
you don't you want to navigate to the
wing-dings font which is what I'm on at
the moment if you're not you can just
select your wing-dings font and then
it's a case of finding it now to get to
it quickly rather than scrolling through
this list I'm going to come to this
character code box company saying 32 and
type 2 5 2 because that is the character
code for the tick and I can see the
cross symbol literally next to it if I
insert that and close this box down the
tick is entered into cell c2 I can then
happily enter that and copy and paste it
into any other box that I may want the
tick to appear so that's nice and simple
especially when you know the 2 5 to
shortcut to save scrolling through a
list but it is completely manual entry
to do that and we're going to look at
some automated ways really soon but
let's just take what we just did one
step further so I'm just going to select
these cells and clear them where am I
going home clear those
because here is the next scenario we can
use a keyboard shortcut
now for this one I'm going to start by
selecting the cells in question and I'm
going to apply the font of wingdings to
for this keyboard shortcut to work so
wingdings - and we can apply this in
advance depending on your scenario if
it's a set group of cells or we can just
select columns say wingdings - is
automatically applied or already apply
it is what I mean now in cell c2 I can
simply do shift P now that's not really
a keyboard shortcut because all I'm
doing is entering an uppercase P but in
the wingdings - font the uppercase P is
the tick symbol and if you want it
across the uppercase o a shift o is your
cross symbol so I could very quickly
enter this data into here and say right
this is a completed one this is another
completed one by using simply typing
shift P or shift
oh so it's still manual but those cells
formatted advanced is very quick and
simple
now let's start looking at some
automated ways and let's use the if
function to test the status of a sale
and then to apply the tick or maybe the
cross icon so in cell c2 let me zoom in
on that sale and let's use the if
function and for logical test I'm going
to test if cell b2 equals complete comma
if it does I want the tick symbol and
it's a few different approaches to put
that in there
but I'm going to use the character code
function and I'm going to request
character coat to five to which we spoke
about in the first example where we
inserted it as a symbol but now we're
getting the if function to do it so that
we don't have to do it yourself close
bracket comma do I want a value if false
yes we could do anything at this point
but let's use the character code symbol
to put two five one in now you may not
want to show across you may just want
the ticks like I demonstrated in the
first example and it's fine you could
show words come out to sell blank you
could even leave it out but if I press
ENTER on this and copy it down it's
going to show these may be strange icons
to Pennell you've seen before but what
we can do is change this to the
wingdings to font we need it in the
wingdings to font or sorry the
wing-dings font in this case so that it
shows they're ticking across to repeat
that was the wing-dings font from the
first example so there we have attics
and crosses now if I click on one of
those sales up in the formula bar you
can still see that formula and it can go
back and edit it at a later point by
using the if function or other logical
functions we can make your testing much
more strenuous than that you know we
could have brought in n functions and
all functions so it can be very powerful
way of automating putting ticks and
crosses and coming away from kind of
human error
now for a next example we want to look
at conditional formatting what a
wonderful tool that is and we're going
to use this on this invoice list to the
right of the table we've been working on
this time we have invoices and we have
an amount paid and the mountain that was
due so column Jew sorry kanji has got
the Jew amount and column H has the paid
amount and we want ticks and crosses in
column I now for conditional formatting
there needs to be a value in the cell
that would put in an icon set into so
I'm going to begin by clicking in cell
by two and just write in a really simple
formula that's just going to subtract
the paid amount from the amount that's
jus so G 2 minus H 2 so when I press
ENTER and copy that down we're going to
get a 0 if it's been paid and we're
going to get some kind of number if it
has not been paid and then we can use a
conditional formatting rule on that
so with those cells selected conditional
formatting now I do want to use an icon
set these ticks and crosses but I'm not
going to use them through there I'm
going into new rule and then the bottom
of the window icon sets and I'm going to
set this up myself just move this to the
right of what we're working with
and not have them put an example in that
I need to change now the icon style is
going to be ticks and crosses there they
are
and it comes in with the tick first and
we have to set the criteria now it's
defaulted to a type of percent and I'm
changing those two types to number
because I'm checking the number in the
cell that I've currently got selected
and you see the options they give you in
a drop-down are just greater than a
recourse or greater them and remember
the information we've got here if it's a
zero or a number bigger than that then
there's still an amount to pay I'm sorry
if it said a number bigger than zero
then it's amount to pay even zero or a
number less then then you've paid it off
no you pay too much but it's paid off so
I'm going to change this first logical
symbol to greater than zero because that
indicates that it's not done yet and
although they start with a tick we can
use this drop-down arrow and change it
to a cross we've got other types of
ticks and crosses here but let's go for
this typical Red Cross then I'm going to
say don't worry about the last one and
I'm going to change this one to a green
tick so in conclusion if it's a number
bigger than zero then it's going to be
that there's still money paid Red Cross
if it's a number that's kind of zero or
less then it's going to be the green
tick so when I click OK it is applied
now the number is still there at the
moment and maybe you want that because
it shows you how much left you've got to
pay and it might be useful to you and of
interest in this kind of scenario but if
you didn't because really we're here to
learn about putting a tick symbol in
conditional formatting manage rules edit
the rule and show icon only click OK ok
again now we only have the ticks and
crosses so that is a conditional
formatting approach it can be limited in
the way that you write conditions and we
kind of saw that there having to think
about how am I going to get the ones
that are paid and ones are not to get
done and we're able to achieve it
now in this final example we're going to
look at formulas and conditional
formatting being used together
and I want to take us back to the first
table I'm going to clear whatever I've
got in those cells and let's put in an
if function because I maybe want the
ticks and the crosses from the
conditional formatting rules but we've
already seen how we create that raw that
had to be like great and recalls and if
I remind us what it looks like if I just
select these cells and go back into
conditional formatting new row icon sets
from this drop-down on the right we had
options for number we had percents we
had formula but in column B of this data
I have words complete not started in
progress so these conditional formatting
rules are quite limited let me cancel
them so we're going to use an if
function to you know give them a nice
simple set up for it in cell c2 I'm just
simply going to write an if function
that will test cell b2 to see if it is
equal to complete that'll be nice and if
it is I'm going to get it to show number
one comma if it's not I'm going to get
it to show number two close bracket once
again please remember that by using
formulas we can create much more intense
conditional logic than that if we needed
to and the fact that we know conditional
formatting works on this kind of tiered
process love is bigger than this one or
bigger than this one or bigger than this
one then we can just create some kind of
numeric process like that like I've done
with a 1 and a 0 but you could have
better free than a 2 than 1 and a 0 if
you wanted a bit more now I'm gonna
press ENTER copy down we've got a 1
we've also got some zeros not
particularly impressive at a moment but
we're gearing this up for conditional
form
Matan so if I come out of there my cells
are selected conditional format in new
row icon sets ticking across change the
last one same percent two number so we
can look at the numbers we got in the
cell and we want to know a tick if it is
greater than a recall one then I'll
change this one to an X and tell the
other one that I'm not interested and if
it's a zero or less then it's going to
be across show icon only click OK now
we've got ticks four complete crosses
four ones that aren't and I know that if
I use the drop-down list I have in for
cell task free to say it's now complete
that will automatically turn into a tick
so we've got different ways of
automating this conditional formatting
have some built-in ticks and crosses but
the logic is not extremely flexible but
using formulas we have extreme
flexibility may not be a straightforward
though and we've seen how we can use
character codes but now more recently
kind of setting up a simpler conditional
formatting rule
you