Hi, this is Gary with MacMost.com. Today let me show you how to add trending arrows
and other types of information icons to a Numbers spreadsheet.
MacMost is brought to you thanks to a great group of more than 900 supporters.
Go to MacMost.com/patreon. There you can read more about the Patreon Campaign.
Join us and get exclusive content and course discounts.
Recently I was asked about adding trending arrows in Numbers. Excel has a built-in function for
this. Adding little up and down arrows as a number changes as you go through the rows.
Numbers doesn't have a built-in function for this but you can have functions that give you symbols
that show up or down trends. So for instance let's look at this simple table here. We have
dates and we have amounts. These could be scores or inventory numbers or temperatures or anything.
Let's say we want to have arrows showing whether or not the number is trending up or trending down.
So here next to 70 we should see a down arrow as it went down from 92 to 70 and then an up arrow
here as it is going from 70 to 82. So we'll start with this cell here because we can't really do
much with this first cell because we don't have a previous number to compare it with. But we'll put
a function here and take a look at these two cells here and compare them. Do that in an IF statement.
IF the cell next to this one is greater than this one here then we're going to put something like
"Up". Otherwise we'll put something like "Down". Now we can see we get the word
Down there. If I Copy and Paste throughout this you could see I get Up, Down, Up, Down.
Now I want a symbol there instead. I can do that pretty easily just by using a special
character. So I'm going to, instead of typing the word Up use Control Command Space and that
brings up the Emoji & Special Character viewer. I'll search for Arrow and I'll select an arrow
that represents up. So we'll do this one here. Then I'll replace this one with a Down arrow
and now I get an arrow like that. Copy and Paste
and you could see I gets Ups and Downs. Now what would be even better is if the Ups were green
and the Downs were red. Now we can't change the color using a formula like that. But we can
use Conditional Highlighting to do that. So with these cells selected I'm going to go to the right
to Format, Cell, and say Conditional Highlighting. Add a Rule. The rule is going to be If it's text
that is, and we'll look at the Up Arrow there, then we'll make it green text.
We'll add another rule and say, if text is and then we'll use the Down Arrow
like that, and then we'll make it red text. Done. Now we can see red and green arrows.
Now if we really wanted to get specific here we could also look for things that
are Equal. So we can encase this entire thing in another IF statement. So, IF
and then we'll say the amount here is equal to the amount there, then
and then we'll put nothing, a blank. Do a space or maybe a dash so we can see it. Then comma. Then we
have the other IF statement there. We'll close the parentheses there. Be very careful when doing this
to make sure you get all the parentheses right. So now we'll copy this and paste it throughout
and let's make one equal. We can see here when it's equal we get a dash.
There are a lot of other ways we can use special characters to indicate things.
For instance, let's say we wanted to have a little bar graph inside of a table. So not a separate
chart. Something that shows you representation of how big the number is right here. So here we have
different amounts and some names. Let's say this is how many items somebody purchased or something.
We want it to be able to very easily see who purchased more. So we use a formula here if we
get clever. Let's use the Command Left and what Left will do, if we look it up here,
is will return a string value consisting of the specified number of characters from the left
end of a string. So the source string is going to be whatever we include here. So let's go ahead and
put a bunch of things like boxes in here. So I'll do Square, I'll search for square. Then
we have a very large square right here and I'm going to Copy that and Paste that ten times.
Then we're going to take the value here and divide it by 10.
Now we can see right there that 28 divided by 10 rounds down to 2 and we get two squares there.
We can probably do a little bit better if we use the Round Function here surrounding
this. So it will round properly. So 16 will round up to 2 and 14 will round down to 1.
We want to have a second parameter there and round the number of decimal places.
We'll do zero decimal places there. So 28 goes to 3. Let's Copy and Paste this throughout and now we
can see here, if we expand this column a bit, we get a nice little graph. We can change the
format text for all of those cells to something smaller if we wanted to make it smaller and
you have it blend in. I get this nice little graph here. I can easily see that this one is
pretty large and the last one here is pretty large and a couple of small ones.
Now another thing you may want to do is put a certain character there depending upon how
big the number is. So we can do that using a Lookup Table. In sheet 3 here I've got this
list of icons. It has numbers from zero to a hundred on the side. Then an icon for each one.
So I've got from sad face to happy face. Now when I do a Lookup on these it's going to look it up
and round down. So if say it was 35 it would use this one right here. If it was 72 it would use
this one right here. So let's go and do that by using the function Lookup and if we look at Lookup
we can see the parameters are Search For, Search Where, and Result Values.
So what are we going to Search For? We're going to search for this,
then Search Where is going to be here in Column A of this Icons Table.
Then the Return Value is going to be one of these. Now you could see I've got the little face there.
I'm going to Copy and Paste throughout here. So you can see this one is happy because it's high.
This one is a low number so it's sad. We've got all these different ones here. So if I wanted to
change which characters were used here all I have to do is go in and change them in this table and
it would reflect whatever I've got. So I could put a number of stars there or different characters or
words or anything that I want here. That's what I would then see here. You could also Format this to
adjust it so here under Format, Text I could maybe make the faces a little bit bigger. I
could also go to Layout here and there's a text inset which I don't think is needed for that
set of cells so I can have it fit a little more snuggly into the cells there. Maybe I can snap
that down there. You can see it creates this nice little visual for each one of these things.
So one last one I want to show you is maybe you want to use some sort of character to represent
ranking. So let's go and create a Rank here. You can do that using Rank. If you lookup Rank here
we could see the Rank Function returns the rank value within a set. So you've got the value,
the value set, whether or not largest is high or not. So we'll use Rank and the Value is going to
be this amount value here. The Value Set is going to be the entire column and we could do large
is high. So in other words the largest value is going to be the highest one here, or Large is Low
which is what we kind of want. We want the highest number to be ranked first. So I'll put that there.
Copy and Paste throughout. You could see I've got the ranking for everything. Here's the top one for
instance. Now let's go and have something similar to what we did here. But here we'll so a Lookup
and we'll Lookup the Rank Value here. So this number in a different table. We've got this
table here, Ranks. We only have four values here, 1,2,3 for first, second, and third place
and 4 which is blank. So we're going to Lookup in this list and then we'll get the value here
and now, if I Copy and Paste this throughout, you could see I get a little gold medal here for
number one, there's bronze for number three, and there's silver for number two. Note that
here that these are just simple characters. If I did Control Command Space and I press the number
one you could see I see various representations of one including this little first place medal. But a
very important thing to note here is this is not a blank cell. In this cell is a space. If I left
it blank it would be the number zero and I'd see a bunch of zeros rather than a blank space. So I had
to go and Edit this cell and hit a space to put a space there. So now in this sheet here I see
blanks or spaces for each one of these. I could also better the size of this here. Make it bigger.
Maybe go into Layout and reduce the inset there for that so I can see those medals really clearly.
Note that any number here four or higher than default to this last value it will
use the last value that's closest. So anything that's 5, or 16, or 20 would then just default
to 4 and give you the blank one there. That's how that works. So I could include 1,2,3,4,5 and have
five different icons although I don't think there are any emoji medals beyond three. Then I would
have to a have a number 6 that was blank. Then, of course, I could go ahead and Hide this column
to make it seem like magic that these medals are awarded to different rows.
So there are bunch of different ways for you to use Emoji & Special Characters
in your next Numbers spreadsheet.