Showing Trend Arrows and Other Icons In Mac Numbers

By using formulas, you can show special characters inside your Numbers tables to indicate things like increases or decreases in values, amounts and ranks. These simple techniques can make your tables easier to read and understand at a glance.



Check out Showing Trend Arrows and Other Icons In Mac Numbers at YouTube for closed captioning and more options.
Video Transcript: 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. Related Subjects: Numbers (129 videos)
Related Video Tutorials:
How To Create Custom Folder And Drive Icons For Your Mac ― Creating a Form In Mac Numbers Using a Script ― Hide Desktop Icons With an Automator App