Microsoft Excel MO-200 – Level 3, Section 2 – Format cells and ranges

1. Modifying cell alignment and indentation

Hello and welcome to level three, session two. And we’ll be using this workbook. It’s the same workbook that we have used in the previous session, but you can download it if you’ve just jumped here as a resource. It’s attached as a resource to this video. In this video we’re going to have a look at the alignment parts of formatting and if you have a look at, some of these particular cells are centralized, some are left aligned and some are indented. And Excel gives you quite a number of opportunities to be able to say I want a particular cell aligned in a particular way. First of all, in the home tab we have the alignment section, so we can have items aligned to the left, to the middle, or to the right. Now, if I left aligned, these two increase your indent.

So you can see if I click on the indent that it moves away from the left hand side. Now the other is technically known as an out dent. It decreases your level of indent. And then we have a vertical alignment. So if I just increase the height of this row, you can see that we can align text to the top, the middle and the bottom, as well as to the left, the center and the right. Now I’m just doing this to one cell, but you can highlight an entire column or a range and adjust that all at once.

More powerful is the Alignment Settings dialog box and it can be accessed with this little drop down, this little arrow going to the bottom right in the Alignment section. So if I click on this, we get an alignment box. It’s the Format sales box and Alignment is a second tab. We’ll have a look at the other tabs in other videos. So let’s see how these icons align to this Alignment tab. Well, first of all we have the horizontal alignment, so we can go left, center and right. You’ll notice that both the left and the right allows for indenting. We’ve also got the vertical, so we can go top, center and bottom.

Now we do have got additional options available and we’ll have a look at some of those in later videos, but for now I just want to have a look at the fill. Let’s see what fill does. I’m going to type in a dot into a particular cell and change the alignment so that it is fill and you can see that it has filled the entire cell with lots of dots. And the good thing about this is that if I then change the color weight, the number of dots changes with it. So I’m not it’s not a fixed number of dots. So however many dots or dashes or whatever I choose to put in are needed, it will add as many as needed. So if you want two lines going across, you could have an equal sign. But remember, if you put in an equal sign, you have to put in an Apostrophe beforehand so that the computer knows that what you’re typing is text rather than the start of a formula. This could be quite useful for decorative purposes.

Now, just one other word about cell alignment. By default, if I put a number in a completely blank cell which has not previously been used, the number gets aligned to the right if I put in a date. So 1 January 2020, it gets aligned to the right. If I put in some text, it gets aligned to the left. So it’s very easy to see if there hasn’t already been formatting added, what has been put in as a number and what has been put in as text. For example, I have just inserted these three particular cells, which one has been formatted as text? For? Quick answer, it’s probably the second one. And there’s more possibility there. There’s more indication by this little triangle that’s at the top left of that cell, if I click onto this and then click on the exclamation, you can see that this number in the cell is formatted as text or preceded by an Apostrophe. So I can convert it to a number if I wish to do so.

Now you can see it is right aligned. So it is now probably, and in this particular case is actually a number. So by default, numbers are aligned to the right hand side, text is left aligned. But you can change that using the alignment icons.

2. Wrapping text within cells

Now, sometimes text will not fit what is available in a cell. Take for instance, most of column F. You can see it gets truncated after the word simple. Now, we could, as we have done in previous videos, expand the column width. But alternatively we could make it so that whilst it stays in the one cell, it becomes more than one line. And we can do that using wrap text. So here we have wrapped text in the home alignment section. Let’s click on it and see what happens. So you can see it’s retained in the one cell. So on the one row, but is now two lines. Now, if for some reason you can’t see the entirety of the text, then you can double click just underneath the row on the left hand side in the gray to expand the row to its maximum height. Now, what else can we do?

Well, I’m just going to reduce the amount of width that’s available and I’m going to go into the alignment settings dialog box, part of the format cells. Now, this wrap text is currently left aligned, but I can justify it. Instead, let’s see what happens. You can see that all of the text is now no longer left aligned. It is left and right aligned. So now if I adjust the row again the column width, you can see that it tries where there’s a space to change this width of the space so that it is both left and right aligned. The reason why this top line is not left and right aligned is because there isn’t actually a space there. It’s just one continuous block of text, no spaces.

Now, you’ll notice that it doesn’t actually work on the bottom line. The bottom line ends where it ends. It doesn’t get right justified. However, if I change this from justified to distributed, it does. And you can see that there was a minor changer with the top as well. It was more centered. Now, if I click justify on the vertical, then again you can see that it goes from the top to the bottom. It’s no longer bottom justified, but it’s top and bottom justified. There is another setting which is distributed which looks almost identical. In fact, it does look identical in this particular instance to justified.

So what does distributed do? Well, if I have all of these as being about three lines deep and I put write text on and I put justify on, you can see that any text which is a single line is right at the top, whereas if I distribute it, then they get centered. So wrap text, it’s good when you’ve got too much data for your particular column width. And then you can apply additional options of justify and distributed.

3. Merging cells and Shrink to Fit

Now sometimes your information is too big for the one column and you want to expand it to more than one column. And this is particularly the case with headers. For example, the cell in A one is too big for column A. Now because wrap text isn’t on and there isn’t anything in column B, then it just spills over into what you can see in column B. In column see there’s nothing actually in cell B one and C one and D one. It just sort of spills over and that’s perfectly fine if you want a heading which is left justified to occupy as much space as it needs. And that’s fine if you want it to be left justified. But what if you wanted it to be centered? Well, there are two ways of doing this. The first way is to physically merge the cells together. So instead of there being four cells in this case there’ll be one that’s called merging. So let’s click on merge and center which is part of the home menu.

And now you can see all of these four cells have been put into one. So if I just use the mouse keys you can see it’s highlighting just one cell at a time. When I go up it’s highlighting eight one to D one which is now one big cell. Let’s have a look at some of the options you can use. So we’ve had a look at merge and center. Merge across. Well, instead of the centering it keeps it left aligned. So if I highlight a one to D one and click just merge across then that doesn’t actually century, it remains left justified. But we also have merge cells and that does the same thing. So if I click on merge cells as you can see one big cell. So what’s the difference between them? Well, the difference is sometimes you have got multiple rows as well as multiple columns. So let’s add a new column. I so suppose I wanted to merge this size across two columns so I can click merge and center then go down to next one merge and center and so forth.

But instead of doing that, what I can do is highlight all of the values that I want merging across and click on merge across. And so you can see each raw is treated as a separate merge. Whereas if I was to click merge cells that will try to merge all of the cells into just one big cell, I’ll lose a lot of data that way. So to be honest I generally use the merge and center because if I want to left justify it, I find it easier to click on that and then left rather than going into the drop down box. But if I was working on multiple cells then I would use merge across like that. Now if I wanted later on for these cells not to be merged then I can unmerge the cells and that now becomes individual cells. We’ll also see the merge in the alignment format cells dialog box. And here we have the merge cells right near the wrap text. So now it’s probably a good time to talk about shrink to fit. And what this does is it temporarily reduces the size that is displayed so that the entire cell is shown in contents.

Now it doesn’t actually reduce the actual font and the reason for that is it’s dynamic. If I was to increase the column width then the apparent size of the font would increase similarly if I reduced it. Now you can use merge cells and shrink to fit. So I can merge these cells this heading into a one and B one. So merge and center. You can see it’s a bit too big and now apply shrink to fit. So now the header is contained within a one and B one. I’ve given it more space but I’ve not given it endless space. However, if the column width was changing then the apparent font size would also change as well. So this is merge. We can merge and center, merge across, we can unmerge cells and we can also use shrink to fit afterwards to make sure the display is exactly what you want.

4. Changing font and font styles

Now we had a look in our first level about fonts and the fact that you can change fonts and you can see roughly what the font would look like if you click on the drop down. You don’t have to click on any particular item just to see a quick preview. So this is what’s called a live preview you and it’s the same with font sizes as well. Now let’s have a look at some of the other things. In this home font section we’ve got two little icons next to the font size. This will increase the font size and this will decrease it. It starts off increasing it by one but later on increases it in twos and later on bigger items as well. So here you can see it goes up by twelve. So it might be you don’t know exactly what the right size is but you know it when you see it. I can also change text, bold italic and underlined and you notice there’s a drop down next to it. I can change it to double underlined as well. It probably looks better if it’s not quite at the bottom of a cell with some borders on it.

Now let’s have a look at our dialog box, our font settings dialog box and you can see it’s the same format cells dialog box that we’re looking at earlier in the alignment. It’s just a different tab. So in the font and font style we have your font. So we have whether it is bold and italic we have your size. Now feel free to change it to a different size one that’s not mentioned here. So I could have size nine and a half if so wished and you can also type nine and a half out into the home menu as well. The color, you’ve got access to all of these 70 colors plus the 16 million colors that are available in the color wheel.

For underlining we’ve got several options, not just the single and the double, but we’ve also got single accounting and double accounting. And what that is, well if we change the number type to accounting, put it into the middle and then we change the underline. We add an underline to a single accounting. You can see that it goes across the entirety of the number including the dollar sign whereas just single does that as well. But it’s a bit more difficult to see. It’s very difficult to see the colors, the commas for instance.

So if I really zoom in you can see little bit of trouble seeing the commas but if I change it to a single accounting it moves the text away from the underlying much easier to see the commas now. And you can also change other things as well like strike through Superscript and subscript and you can see what that looks like in the preview. Now, what might not be obvious from all of this is you can also change the color to part of a cell. So if I go into sheet free and I say going to the sum text, I can change, say just the word text to red. So I can now say this is red and this is blue and underlined.

So I can alter the red section to red, and the rest of it I can change to blue and I can put an underlining as well. So you’re not just restricted to one four color and one back color in a particular cell. You can have as many four colors as you want. You are restricted to the one back color. But if you wanted to sort of alter it the column width so that some of the text appears to be in a different cell, then you can change that as well. So these are all of the options that allow you to fine tune your font for yourselves.

5. Applying highlighting

Just as this letter A indicates the foreground, this paint indicates the background. And we saw highlighting again in level one. You can pick on any particular color. We’ve got the 70 colors there. We’ve got the color wheel. We’ve also got no fill. Now, no fill is different to white because let’s say you’ve got an object in the background around, maybe a graphic. No fill will color it transparent as opposed to white, which would actually be colored the color white. Now, if we go into our font dialog box in the fifth tab, we’ve got a few extra options. We have got fill effects. This allows us to go from one color to another. So I could go from a red to a blue. Probably not the most attractive, but it allows me to visually see what it is more clearly. So I could go it horizontally. And you can see this sample vertically, diagonally, or from one particular corner or from the center.

So probably best used when you have got a really big raw height. Occasions when you might want more than one color, I suppose, right at the beginning of a report. Not often used, I suspect. Now, if I go back into the format cells download box, you can see we’ve also got patterns. So we don’t just have to have one constant color. So if I change that back to a constant color, you can also have spotty effects or hashes or dots. Okay, using the right place, they could draw attention, they could look a bit pretty. But don’t go overboard on highlighting. Highlighting does draw the attention of the user. And if there’s a reason, say, for instance, putting into headers, that’s okay.

If you want to highlight, for instance, transactions which are unusually high or usually low, that’s fine. But if you start putting it everywhere, then your spreadsheet can start to look a bit messy. However, it’s nice to know that hidden away, there are additional fill effects that you can have if you so wish. You can also change the pattern color as well.