Lessons

Lesson Info

Cell Formatting

So let's talk a little bit about self formatting in order to do that I'm gonna create a new spread sheet here as I mentioned before at the beginning of the class there are lots of different formats that we could choose from we've got dollars we have percent we even has some that are unique to numbers like check boxes and sliders and steppers so the way that we get to that is when we have a cell or a siri's of cells selected so let's go ahead and select groups will select a block of cells there because we want to change the format for all of those cells we go over to the formatting button so we had the formatting pain selected we go to the cell tab and then we change the data format the default format is automatic we can change that to any number of these that are up on the screen now so number if we're looking for a number, the difference between automatic and number by for example is if you do automatic and you do a number like one, two, three, four, five it's just going to be one, tw...

o, three, four, five what number allows you to do is place that that thousands separator comma in there so that's one of the differences there with that you've got currency you've got percentage you've got fraction numeral system scientific their text formats dayton time duration in lots of different formats the ones that I thought I think are a little more exciting are the ones at the bottom where you can add a check box a star rating slider stepper and pop up menu and there's a really good solid reasons why you'd want to choose some of these we'll look at a couple of those and then one of my favorites is the create custom format option so I'll show you how to create one of those because it allows you to create your own format for yourself an example of that would be a phone number you know phone number typically has the parentheses around the area code and then a dash between the first three in the last four numbers so we can create our own format for ourselves to make things look just the way they should so in this case let's go ahead and create a couple so let's change this road to a check box so now we have a check box and that's all there is to it we just change the format to a text box at this point we can simply check those off we're not and that's how check boxes work pretty straightforward typically what I do is I like to shrink the width of the column to match you know so it looks good with the check box with the steppers this so the steppers and the sliders work pretty much the same. They just they just act a little bit differently. So the way that the slighter works is we'll go ahead and choose this lighter option and then over on the format pain, we have three numbers we need to populate this with. So we need to know a minimum number on the scale, the maximum number on the scale because it is a finite scale, and then the increment amount between steps. So for sliding from one to one hundred, for example, and maybe we just want every ten number so it's either ten, twenty, thirty and so forth, we can set the increment to a value of ten. So when we do that, as we slide up on that slider is going to go zero, ten, twenty, thirty, etcetera, or we could leave it at one, and it slides up incrementally by one number where we could do by twenty five, you know, whatever, whatever we want. So let's, leave it a tense, you see can see what that looks like. So we'll go ahead and and d select that and they will select that cell again. You'll notice when we select that cell, we now have a slider option. This is all graphical, by the way, we could always manually enter the value of fifty but this is just a visual element to this so at this point what we do is we take the slider and we slide up and down okay now I mentioned before that was going to go up from zero to ten to twenty to thirty but in this case is actually eleven twenty one thirty one, forty one etcetera the reason for that is we told it to start and a minimum value of one okay? And I'm bringing this up because if you're doing something like this and you get stuck thinking why is it why they're not doing that check that minimum value so I'm gonna add a zero there and we'll come back out so now if we do it it's going to go up by ten twenty thirty etcetera tell makes sense the minimum is zero like we defined and the maximum is going to be going to be one hundred likely to find out what is that like what would that be useful for um so a good application for that would be you could use it for ranking um if you're going to send out a survey maybe send out a survey with some questions you know how do I how do I rank on my customer service from one to five and you know they have a slider again you could have them manually enter five or three or whatever, but this adds a nice little visual touch to it so you know something like that or we're maybe gosh, I don't know I mean that's oh, well here's an idea so one of the templates in numbers is a retirement calculator and I know that in the retirement calculator they actually use the slider and what's really need about that is the sliders is used on a on a percentage of return on investment and so there's a lot of data with regards to retirement in there and use the slider to slide up and down on that percentage and in that case, it's going up by a tenth of a percent every time so five point one five point two five point three percent things like that and as you slide your data based on calculations related to that slider well, actually just for you, yeah, so if if this is great for what if scenarios you know, people use spreadsheets a lot for what if scenarios this is great for that because you you can use that to slide up and down through different scenarios on dh watch your data move and change as you slide that up. So great question the step or I won't bother to go into that one but it's the same thing instead of a slider, you have an up arrow and down arrow, so you just simply click up to increment and down too dick roman I think is the word is that the word okay good that was shot in the dark okay, so let's look at another format here so let's look at er star ratings are pretty fun it just gives you this right here and you you simply just select the rate the ranking that you want to select one thing I will show you here is if I select thes and we've already started with the star ratings how does that actually what does that look like in the background? What does it look like in a non star rating spreadsheet for example, if I were to take this file and I send it to someone using xl xl doesn't have the same feature of the star rating right? So when they get that it's not going to look this way so just to give you an idea of what that looks like behind the scenes if I change the format back to number so that's what's really going on behind the scenes so a three star rating out of five is the number three a four star is the number for the reason I show you that is a because I want you to see what that would look like if you did send it to an excel user and b if you wanted to run calculations off of this right here or you're doing some filtering off of a star rating you know what to look for. So show me everyone with a value of three in this field in other words a rating of three so that's what that looks like in the background any questions about that so far? Okay, good. Ok, so let's talk about custom self formatting so I'm going to take the example of a phone number because I think that's one that everybody can relate to one of my favorites is I actually quite often will use a custom format for I p addresses for networks because I work that's my life by working networks and um but the same thing applies for anything so social security numbers, phone numbers you know, whatever that has a set specific format so in this case will do phone numbers so what I will do is come down here and choose create custom format again, we have these highlighted so this this cell here just below that selection will not be uh self formatted in the same way. So we'll create a custom format and we have our window here. This looks a little confusing at first, but let me walk through it so the name is pretty straight forward we will call this phone number and the type will just leave is a number because technically it's based on a series of numbers and in this right here is considered this area here sort of the template for what this number is going to look like. So again, if we think about what a phone number consist of, we start with the left parentheses and we have three numbers, at least in this country, and then we have a right parentheses and then a space three numbers dash, four numbers, every phone numbers that way. So what I'm going to do is actually delete everything in there, and I'm going to start with a hard coded left parentheses and down here we have different option options or objects that we can drag up into this area. We're going to keep this relatively simple for now, but we do want to add an inter jer or a number, so we'll drag this up and drop that there because we want the next siri's of of numbers or things to be numbers. Now this when we can see it's going to be expecting four numbers and it's going to put the thousand separator in there as well, we actually want to get rid of that rice we want to decrease by one as well as removed that comma. So to do that, you'll notice on the right hand side that there is a drop down arrow if we click that we can hide the separator first of all and we can also either at a digit remove a digit or we can change the number of digits this way so if we if we know it's going to be five digits, we could just say five digits if we know it's going to be three like it is in this case we'll choose three and now we can see that it's reduced down to three ok, the next part of the format is going to be a right parentheses, so we'll do that and then a space let's go in and drag another one up there and we'll do the same thing will remove a digit and then the next element is a dash. We'll drag another imager up and this is kind of going by quickly now but you can see what that's going to look like now we've got the left parentheses e the area code right parentheses e phone number dash but never so go ahead and say ok, and by the way it does give us a preview if you look right here is going to give us a preview of what that's going to look like based on the data that's in the cell already because we don't have anything in the cell it's not going to show us anything so let's going zoom out and hit ok so again, we've applied that, but we can't see anything yet so let's go in here the way that we use this now it already knows about the parentheses there, so we just need to enter the numbers. So for example, I'm in new york, so area code two, one, two, one, two, three, four, five, six, seven and if somebody actually has that phone number, I apologize might get some phone calls, so we'll do that and, well, I enter and you can see that once we diesel like that sell, it has formatted the number in the right way. So if we wanted to change that format, maybe, you know, we're required to put a plus one at the beginning of that for international dialing purposes, so this is good for people who work with international phone numbers quite often edit custom format, and so here we have what we saw before if we simply want to add a plus one to the beginning, we don't have to add the one in the actual sell we can actually hard code the plus one, so to do that, we just simply come to the left plus one space say, ok, and now we've adjusted our sell format to show the plus one of the beginning, so use your creativity to think of all kinds of sells custom self formats again I use I p addressing as a common one which is a serious of numbers with some god's kind of intermixed in there some other ones you might use might be I don't know phone numbers social security numbers product numbers you know I know part numbers or job numbers typically people have a specific numbering format for that so if you have a column for project number for a for a series of shoots for example then you could have number number number dash number number dash you know, whatever but you could set your your own custom format in here. Any questions about that? Um custom format live is it permanently on there now? Yes it actually it lives within the cell. So if you if I were a copy of this cell and pace that cell somewhere else not only in my copy in the data within the cell but I'm also copying the self format the cell, the background color, everything related to that cell and again, if I if I copy verify highlight a siri's of cells and I apply the format to that it lives in each one of those cells individually. Ok? So if I go down to the last one here, it is not formatted in the same way but let's say you want to apply that format to a different series of cells once we've created at once you can see it's an option here we can select it again for this file so it doesn't it doesn't live so you can't create a new numbers file and have it automatically show up in that one either so it's a perfect ill but the format itself lives within the cell lt's so if you move the cells around our copier paste it would be a cop in pace with that cell that makes sense okay let's talk about phil types for a second phil types are first of all static so if we if we added a specific phil type it's going to be static to that cell in a little bit we're going to be talking about conditional formatting which will actually change the color of a cell or the text in the cell where the number in the cell to a specific color depending on other conditions so tio adjust the's cell fills weii highlight the text we want tio effect and we go to the same cell portion of this pain here the editing pain and we go down to the phil section and we can either fill that cell with a specific solid color grady and color so for example, if we wanted it to be, you know, light blue to dark blue for example, if you go through the airport one thing you'll see is they have sign it all over the place that has something similar to this that makes it really easy to differentiate from cell to cell or wrote to row makes it easier to read, so so you could do that as well. You could also do image fills. So if you wanted to fill yourselves with a specific image, one example of that might be for a series of products you have going back to the question you had earlier. If you had a serious of product, you could actually change the height of these cells, so that actually makes sense to put an image in there and then actually drop in image right into this cell here. So maybe if we're talking about an inventory list, you could have a small thumbnail size picture of rings and bracelets, and, you know, whatever products you have and drop them right into that cell. So it's, a nice little visual indicator for what that road contain means. Another way of doing that, by the way, is if you take an image and drag and drop it right into the cell, that does the same thing. So it's kind of a quicker way of doing it, ok, so that is self hills. Any questions with that? Okay, are you are you feeling comfortable with the borders with with how to get through the borders and manipulate that? Ok, perfect all right, so let's talk about conditional highlighting I promise we're going to talk about that and you know, when I think about numbers and the power that numbers has that's honestly one of the things that comes to the forefront of my mind grafts are great and the filtering and sorting that's all really fun and good and useful stuff, but one of things for me that really makes numbers useful is conditional formatting so and what I mean by that is here's a file for example that has a serious of test scores, so what I want to do is I want to highlight everybody in my class that has a grade lower than seventy five percent okay, I want that to draw my attention so I can go to that student and say, look, bob, what can I help you with your test scores aren't reflecting what I thought, what I thought you should be learning and we want to address that. So to do that what I'm going to do is highlight the range of cells because we want this conditional format to apply to all of the cells within this range in this case it's easy because it's the entire table essentially so what I'm going to do with that is go to the cell format pain where we spent a lot of her time already and they were going to click on conditional highlighting case will click on that by default there are no rules applied which makes sense it's kind of an empty canvas we're going to add a new rule and so we have a couple of options here that we can choose from in this particular case we're going to look at it every grade that is below seventy five percent so here we're going to choose the less than actually we're going to choose you always do less than so less than seventy five percent so we'll choose less than and it will say point seven five and the reason we're doing point seven five is because seventy five percent equals point seven five on a scale so we need to make sure that we match and then in that case we wanted to go ahead and fill with the red phil and then we'll show you what that looks like with other options as well so we'll go ahead and say done to that one and um actually wow I've got some smart students in my class so that's below seventy five right what if we want to look at all the students that are between seventy five and eighty five okay now that I know we've only got one test score below seventy five the next step is to kind of attack that the children not physically poor choice of words but but look at or determine the children who may be I want to get from eighty two to ninety two percent right we just this is our way of of analyzing our data and just bringing the whole class up so I'm gonna leave the entire selection selected again we're going to go back into show highlighting rules and we're going to add a new rules and we got rule one which is below seventy five we're going to add a new rule that is let's dio let's do between so we want to do between point seven five and between what I say eighty five, eighty five percent and we want to change this one to a yellow so we're going to change this and we're going to do a yellow phil on this one by the way, I haven't committed this change yet, but it's still showing me what is going to look like once I hit the done button and again that's pretty cool because if I feel like, well, maybe that's too broad a range while I'm in here. Still, aiken said, well, it's two point eight because that's sort of low hanging fruit and then we can kind of hone in on exactly what is that would work best for this scenario. We leave it back on eighty five right now so let's go ahead and say done and now you can see very quickly and very visually that these air the students that are doing pretty well but we could also help them to do better now the last one we want to do is I want to see every student has a grade over ninety five I want to really because I want to celebrate their accomplishments so we're going to go back in show highlighting rules will add a third rule it will say greater than point ninety five and that's going to be a green phil and I don't have anybody to celebrate okay, well let's change it that's fine. What about ninety three ok, so we've got some ninety three's so now visually I can see who are the ones that actually call out looks like mary and tim and samantha are all doing really well on the test scores maybe I'll treat him tio uh you know, I don't know uh, candy bar yeah, something. So anyway, I love conditional formatting because it really get it really breezier data toe life you've got a lot of data here that you're trying to analyze and if you're just looking at a serious of numbers, you know, like rust was saying earlier numbers or numbers they're kind of boring, but this really helps your numbers helps your analysis to come to life and do something with it and after all, that's why we work with spreadsheets because we want to do something with that we want to take action on that okay, yeah, is this live updating? So if you go in and you type in one of the fields and change it to something that it either let's say you change one of the eighty eights too in eighty four well, then suddenly become yellow. Great question, and I'm glad you brought that up. Yes, it is it israel time. So I actually in this case I went back to megan and to talk to her about her sixty six percent and she said no teacher look here's my test score, you gave me an eighty two, okay? My bad. So I'll come in here and I'll put eighty two and it changes automatically. Yeah, so and you can have I don't know what the technical limit is. I've had a cz money is fifteen or twenty rules on a on a block of cells before, so you can really really haven't do whatever you want and you can change not just the phil color, but you can change the font style you can change the font color you can change a lot of different things about about this. One of the more interesting ones that I did with conditional formatting is where I had a scale of numbers from from zero to one hundred, and the closer the numbers got to one hundred, the more red it became this is years ago I don't remember the context of the file but read one hundred was bad and so I wanted to highlight as I was approaching one hundred those numbers that were bad so the ones that were near one hundred worm or red and vibrant and kind of in your face where's the ones that were around twenty or thirty or more of a subdued read it was like ok, well that's interesting to see but it's not it's not getting my attention right now. Um sort of like a heat map a heat map is it draws your attention to certain pieces of data so that one I think I think I set that one up in with ten different rules so one rule for ninety to one hundred next ten down next time down and so every rule had a different shade of red and it was it was really helpful toe visual visualize that data are you able to take this same um all the same information and put it into a different kind of graft or chart? Yeah and we'll talk about charts in a little bit in this class but we can take this right here and turn it into an actual graph we could do a pie graph bars heart any sort of graph you want as long as it makes sense what I find funny is when p people make charts and the chart doesn't reflect its not a telling chart for the data if I make sense so like something like this it wouldn't make sense to have that machar in a pie chart but yeah, we'll talk about how to convert this into a into a chart in a little bit one more thing I wanted to show you with tables is um is I wanted to show you alternating row colors so in this example here let's go ahead and take off the ok so if you want to remove the conditional formatting, we'll go ahead and just come back into the conditional highlighting section and just click the trash can and as we delete these off you can see this reflecting in in the spreadsheet so that's how we remove those rules after the fact so in this case what I want to do is I wanted this is a simple one so it's easy to read but I want to create a scenario where every other row is a certain color and what that does it helps me to read the data as you're going horizontally from left to right side, so to do that what I will do is highlight thie the table and I will go into table so this is a section that we haven't really spent a lot of time and yet and near the bottom there's an option to add alternating road colors okay, so we will turn that on and by default already you can see why it's a little bit faint but already you can see that there's a nice grey phil on every other line so what I'm going to do is I'm going to go ahead and change that's something a little bit darker than you you can see it's a little too dark here we go so it makes it a little easier to read and notice it doesn't affect the the side row here the header row or that sort of the header column or the header row ok, so they're not subject to this specific format what is subject to that is if we added more rose to our spreadsheet or table it would actually automatically add that in the way that I used just contrast the way we're going to hear the way I used to do that and excel and excel is change so it's got this feature in there now but the way I used to do that is I would actually highlight every other road just like this and we'll see I miss one already and change the phil color ok why is that bad time consuming it's a lot of work first of all and what if I added a new row in there now I've got to redo the whole thing so it's a very manual process so this is really nice because it's automatic and it keeps everything nice and tidy. If I, if I changed the sequence here if I moved meghan up in the list, maybe to the top, noticed that automatically adjust with that. So it's, literally. Every other row is a different color, and you can change it to whatever color you want. I like a nice, faded blue color, says blue, is my favorite color.