A lot of times, you will want to format your Excel worksheets. So in this screencast, I'm going to be talking about cell formatting and something known as conditional formatting. I'll be using this Practice Numbers.xlsx file to kind of show you some of the basics of cell formatting. So by default, you can just type into a cell and the font is Calibri in the size is 11. So if you want to change that, you can go up here. You can select from this drop down the size so you can make it really big if you wanted to. Alternatively, you can type in so you could type in 16. You can also change the font type over here. So you could change it to one of these other ones. You can make it bold. You can make it italicized if you'd like. You can underline and so on. There's also a way to add borders. So if we highlight this entire selection of cells and I go up here in the font group, you can add borders. You could add right borders. So that added a right border to that block of cells. You can't really see it that well, but it's over here on the right. You can add all borders by doing the same thing. You can increase the size so you could make, you can go down here to line color. If you want to change the line color line style. So maybe you want to make that a lot thicker. You can change that and that actually brings up this pencil and then you can kind of draw in where you'd like to put those bold lines. So if you press escape and get back to the kind of this plus sign cursor and let's highlight this again. You can, if you want to remove all the borders, you can select no border. You can highlight cells by doing this drop down. So maybe we want to make them all green. Maybe you just want to make one of them blue and so on. You can change the font color so you can click any cell. And over here, you can drop down from this little menu here, different colors. So let's just go ahead and make that a nice red. You can also change the alignment of your cells. So maybe you want to left justify everything. So you can click over here on align left. You can also align in the middle so you can center and if you want, you can play around with these various formatting command buttons. A lot of times, you'll write something in a cell like this is a very long label and you don't want it to spillover. See how it's spilling over to the left and right. You go ahead and click on that. Right now, this is centered but we can left-center it if you'd like. And you can click up here to this ABC a little command button and you can wrap the text. So what it does, it keeps the column the same width, but what it does is it increases the rows so that you can kind of wrap that label. Another common thing to do is to combine or merge cells. To merge cells, you can select multiple cells. And you go up here to this drop down menu and you have four different options. The most common is just the default one. So if I merge that, this is actually merge and center then I can type in perhaps a label there. And that's we've merged three of those cells and now we have a single cell. So this is actually a single cell. The reference of this is always the upper leftmost cell. So see how in the name box this cell even though it spans B12 D1 is really now known as b 1 because that's the upper leftmost cell. It doesn't have to be a single row. You can merge, let's say those six cells by merging and center. You can type in a label there if you want it to center that label in the middle. So vertically center it, you can always go up here into the alignment and click on middle align and that kind of brings it into the middle. If you want to undo a merged set of cells, you can always go into here and you can do unmerge. Whatever is in that single merged cells will always go to the upper left of the remaining when you unmerge. So that label went up in here to the upper leftmost cell. There are some other options here. For example, if you wanted to merge this set of cells, but you wanted to perform row wise merging, you can always just go merge across so that made two cells. One more thing I'd like to make you all aware of is sometimes you'll put in a label and then you want to put something on the next line. You can go up here to the formula bar or you can double-click in here to edit. But if you do Alt Enter, that actually adds a new line to this cell. And so we could just type in something else down there and what that does it's similar to wrap text, but it just adds a line. If you decide that you want to remove all your formatting, you can highlight the cells of which you'd like to remove formatting. You can go up here on the Home tab in the styles group and you can just select cell styles and you can just select normal and it'll get rid of all the formatting. And then if you want to unmerge,again, these cells are merged right now. We can always go up here and unmerge the cells. Now I'm going to show you how we can conditionally format cells. This is quite useful if you're trying to visualize various data. So I'm going to go here to sheet 2 on this practice numbers file, and I'm just going to highlight these. Up here on the Home tab in the Styles group, we have conditional formatting. You can go ahead and click on that. And if you want, you can experiment around with these, but let's go ahead and do color scales. If you just click on Default over here the leftmost, then what it does is it kind of compares everything that was in that original selection, sort of compares with one another. And the high values, that makes green and the low ones, that makes red. You can always go in here into conditional formatting after you've set a conditional formatting rule. You can go down here and manage rules and then you can edit this rule. So maybe you wanted to adjust this. Right now, it's saying the lowest value in that selection is red and the highest value is green and the 50th percentile which is the midpoint is yellow. So maybe you wanted to change this. You can you want to change it to blue. You could do that and that's how you can change the conditional formatting rules. So there's all sorts of conditional formatting rules. Let me just show you another example of conditional formatting maybe had a couple of people. These are elves actually, or dwarfs and Snow White. Maybe they take some quiz and you want to evaluate graphically or visually their performance. So you can highlight that. You can go up here to conditional formatting. Maybe you want data bars. So this kind of shows little bar charts within the cells that have those numbers and you can kind of kind of use that to visualize things. Let's go ahead and look at some of these others. There's icon sets in here and maybe you want identify the ones that passed maybe an 8 and above is passing. Maybe you want to identify the intermediate values and the failing dwarves, so you could select these indicators. You can always go back conditional formatting and we can manage the rules and if you wanted to edit this, you could change so maybe instead of a percent. Right now, it's showing you're going to be a green check mark if the value is 67 percent of essentially the range between minimum and maximum. But maybe you want to change this instead of a percent to a number both of these. So when the value is greater than or equal to 8, we want to make it a green check box and you can select different things here, if you'd like, different icons. When you do that, you notice that it says the next one is when it's less than 8 and now you can specify maybe greater than 5. And then the last one, if you click away, it says you're going to be we're going to give a X, a red X when it's less than 5. And that's how you can sort of customize your icons. I would strongly encourage you to experiment around with these different highlighting rules and conditional formatting rules and the icon sets. If you do decide that you want to eliminate or clear all those rules, you can always do clear rules, either from the selected cells or from the entire sheet. Hope you learned a little bit more about cell formatting and conditional formatting in this screencast. Thanks for watching.
