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

6. Applying Number format

Just as we can apply colors and fonts and so forth, we can also apply formatting to numbers. You see, for instance, column A is a number column, but it hasn’t got any comma separator values, any thousand separators, whereas column H does. So let’s have a quick look and see what you can format your numbers with. Well, first of all there is the general. This is the standard default number, so no commas. Or you could say I want a number which allows a number without commas but with two decimal places. And you can add or remove decimal places using these two icons.

Now, just bear in mind this alters the format of what you’re seeing. It doesn’t alter the original data. So if I put in here a data of 1. 2345, then the computer will round it down to the two decimal places that have asked it to be shown, but internally it still contains 1. 2345. Next, we have got currency. And this formats your currency, your numbers, into the default currency that you’ve got set up, which on my computer is the American dollar. And if you put them in cold in accounting currency, you notice that the numbers don’t quite line up. If I write a line, it the dollar signs themselves don’t align up.

Now, there’s a difference between that and accounting which has the dollar sign or whatever sign you’re using on the left hand side and all of the actual numbers on the right. If you’re in an environment like for instance, the Euro is sometimes formatted on the left hand side and sometimes formatted on the right, then the only difference would be there would be an additional space because it’s already on the correct side. So I’ll show you that in a few minutes.

Now, we can also format dates. So this date column, we can use short dates. Again, formatted according to your default, which you can find in the Windows control panel, you can have long dates. And the reason why there were all these hashes or pound signs was because the column was too small to actually show the entirety of the date. We’ve also got time, so you can format the time. So here we have 945 and 25 seconds in the evening. Now, internally, again, it still retains the date. So any calculations based on this cell will have to take account of the fact it’s not just the time that’s being displayed, but a date.

We’ve also got percentages. So if I go into column A, you can see that 17 is also 1700%. So the percentage is expressed by multiplying by 100. This is better in fractions. So 00:23 is also known as 23%. Now you can also have fractions. So let’s say I have the number a half. Well, that is expressed as 0. 5 or zero comma, five in some locales, but I can express it as a fraction, which, as you can see, will make it one two. Internally it’s still 0. 5. Now, if I had the number one and a half and format that as a fraction, then you can see it’s formatted as one space, one over two. Now, what if I had a different type number? For instance, suppose I had 1112, which is zero 91.

If I format that as a fraction, it actually formats it as the number one. So this fraction is not 100% guaranteed to give you the right answer. What it does is it gives you the right answer ish if the bottom number is formatted, the bottom number being the denominator is a single number. So it tries to work out what a fairly close approximation is. So fractions can be quite useful, especially when you’ve got some really long complicated numbers that you want to express as either exactly a fraction or approximately a fraction. And then finally there’s scientific. So what scientific does it’s a bit complicated perhaps, and I don’t think there are too many instances when I’ve actually used it. It says that this number which you can see is 1,983,000 is 1. 98 times ten to the power of six. That’s what that E plus means. So ten to the power of six, that’s a one with six zeros. So one with six zeros is a million. So 1. 98 multiplied by a million. The advantage of using scientific notation, which may also have some other words, like for instance, in Britain it’s called the standard form as well.

The advantage of using scientific notation is that no matter how big or small your number is, you get it in roughly the same size of location. So if I was putting a really big number, you can see it overfills the cell, but in scientific notation it occupies exactly the same number of digits as a smaller number does. So it can be useful, but I don’t think I’ve ever really used it. Now, the final option is text.

And this can be useful if you want something that not to be translated as text itself, as the number itself or a date. So for instance, suppose I’m putting in the 1 January 2020 and I don’t want that to be converted into a number. Well, I can change this to a date and put in 1 January 2020. And now you can see it’s a text which is overfilling column F into column J.

Now you can see all of these in the format Cells number tab. So starting off, we have our we have our general, so that’s just no specific number format. We have the numbers and we can say, I want this number of decimal places and I want to use the thousand separator. And you can see the preview of what that would look like. You can add a currency to it. So your default currency is the dollar here for my computer, but I could change it to a different one. Suppose I wanted to change it to Euros, for instance.

So let’s highlight all of this column and say, I want to change this to euros. So there’s the euros, I want to change it to click OK, so this is the way of changing what currency you are seeing. It doesn’t affect the value, but it can be useful to have a different currency.

Maybe you have got accounting and you have got pounds and you’ve got dollars and you’ve got euros and you’ve got yen. All of these could be formatted this way. Now, just to show you the difference between currency and accounting in euros, the only difference is there’s a slight additional space for these euros. But again, I could change it now to the British pound. And you can see the pound sign on the left hand side, numbers formatted on the right. And then going further down, we’ve got the date.

So we can have different types of dates format. So suppose I wanted my dates to be format in Japanese format. Well, that is one of the options, the standard options which are available. You’ll notice further down we have date and time. We have time itself. So whether you want a 24 hours notation or using Am or PM, we have percentage, fractions.

So fractions. This is where we can change the number of digits that are going to be used. So over in this previous example, where we’ve got 1112, if I change this to a fraction, up to two digits, you can now see 1112 better represented. You could have it up to three digits, or you can say, I want to express everything as eight or hundreds. So that is a quick introduction to different number formats. There is a custom option, but we’ll be looking at, as part of the expert cost, we’ll be looking at that in future levels. So that is the number formatting.

7. Applying borders

Now, in this video we’re going to apply a few borders and we’ve got borders here in the home menu in the font. And we’ve got this dropdown box. It’s very rare that you’ll be using this left hand bit, which just remembers what you last did. Usually use the drop down. And so you can see you can apply a bottom border. So that applies a border right at the bottom of whatever you selected, top, left or right out. You can have borders everywhere, so throughout the cells.

Or you could have it just surrounding the range. So here you can see it’s not doing the lines in between, it’s just doing the entirety of the range. You can have no borders, you could have thick borders just on the outside and there’s a few variants as well. And quite frankly, there’s an awful lot of choices. And which one do I use? Basically none of them. I go down to the More Borders section, which gets me back to the format Sales dialog box into the fourth tab. And this allows me to really fine tune what I want.

Now there is no I want all borders, but you can say I want outside and I want inside. So that’s the equivalent of all. But there’s a huge variety. For instance, we can change what sort of style, so maybe I want dashed lines all the way through my border. I can change what sort of color I want and if I change the color, it won’t actually change the borders currently being used. It only works to new borders that I had.

So maybe I want a thick line on the right hand side in red. Well, I can just apply it like that. Suppose I don’t want all of these dashed lines in the middle. No problem. You can just click on any line that you don’t want and it disappears. And if the line isn’t there, you can click on any line you do want. You can also have lines in the middle. So you can imagine there is a diagonal and there it is, quite fiddly to get to sometimes. But there you can see the diagonal through all of the cells. It doesn’t actually put an X in a cell, so you still got the actual content of the cell, it just crosses it out.

I don’t often use it, it’s nice to know that it’s there. Additionally, you can draw borders and you get this little pencil and you can start drawing what you want. The vast majority of the time what I use is the More borders section, or to be honest, I just go into the Font dialog box and just go over to the borders section. So this is how you apply and fine tune your borders. And another way of adding them is using these little icons on the left and side and the bottom. But I just normally just point and click. So that’s another way of just selecting which borders you want.

8. Applying cell styles

Now, there’s lots of cells that can be formatted and you may want to take some shortcuts. Now, we can do some sort of shortcuts in cell styles. It’s a colorful way to make important data stand out on the sheet, and I would actually say it’s a way to make the presentation of the data a bit more uniform. So let’s see what styles are currently available. Well, we’ve got ranking styles Bad, Good and neutral. And you can see in cell E five what those look like. We have styles under the data and model, so you can see it adjusts the background as well as in some cases, the italics.

We’ve got fiend cell styles, so if I highlight the headers, you can see it could be useful to have, for instance, your headers being in a particular dark style, and then you might have subheadings later on in a lighter style. And then we’ve got these number styles which, quite frankly, are fairly basic. So if I scroll across and so you can see what they look like, they simply are various types of numbers styles that we have looked at in previous videos. Now, style is not really used as much in Excel as it is in Word, but it’s still there. And there are some nice groups of styles that might just be worth having a look at to see if they can help you with your next video, I mean, your next spreadsheet. So it might just save a little bit of time.

9. Using Format Painter

Now, let’s say you’ve got the perfect formatting. You’ve put in the right font, you’ve put in the right size, you’ve put in right colors, and you want to replicate it in other cells. So does that mean, okay, I have to go into each cell and have to just the size and the board and everything else manually? Or is there a way to say, okay, you know this style right here? Is it possible, please, to put those into these cells? And we saw in a previous video that you can do this using paste format. So if I copy the cell that I want the formatting for, going to the cells I want to copy it into and then go paste special and formats, and there we go. But there is a second and I think underused way. And this is the use of the Format Painter. So the Format Painter copies the format of the first cell and applies it to other cells.

So first of all, we copy the cell that you want to copy from. So this is like charging up your paintbrush, and then you copy the cells where you want them to go. So no copying and pasting literally required. Or what I could also do is double click on the Format Painter, and that gives me rechargeable paint so I can click in lots of different locations for that particular style.

However, how do I move elsewhere? Well, when I finish getting on Format Painter, when I finish my painting, I just press Escape, and you see that the little paintbrush next to the cursor disappears. So Format Painter, what you do is you select the format you like, you click on Format Painter, and you click on where you want it to be applied to. Very easy, quite often overlooked. It’s just like saying, I like this cell, Format Painter into here.

10. Changing text to WordArt

Now, the final video of this second session is how to convert existing text into something called Word Art. Now, Word Art is more used in Microsoft Word and Excel, but it can still be useful in the circumstances. If you go into the Insert tab, you’ll see Word Art in the text section. Now as soon as I click on it, you’ll see that there are 20 different styles, but you can alter them later on. So I’m going to click on this particular style and now I’m going to add some text so you can see that we have got the text appearing and I can now edit it.

So this is my text. Maybe I’ll just call it for now, word Art. Now, we’ll be looking at future levels at how to format Word Art and drawings. But just to give you a preview, we have got this new Drawing tools menu and you can then format shapes, outlines and that sort of thing and change the Word Art style here as well. So if you want to insert some Word Art, then you go to Insert text Word Art and choose your style that you want to start off with.

11. Practice Activity Number 7 – The Solution

Right, how did you do with this practice activity? So I’ve opened up the practice file workbook. I need to double the indent of cell D four so you can see it’s indented. I have no idea what doubling the indent entails. So whilst I could press increase indent, I’d be guessing it’s much better to actually see what the indent is. So the current indent is one. So I will double that to two. Wrap cell D five. So I could go into the format dialog box, but equally, it’s just as quick to click on the wrap text. You’ll notice that you can’t actually see the bottom of the Word certificate at the moment. Set cell D six to shrink to fit. So that isn’t available in the actual home menu. I’ve got to go into the format cells to click Shrink to fit. Merge cells d seven and e seven together.

So, again, lots of reasons, lots of ways you can do it. So click on merge and center. Add a black border around d seven to e seven. So I’m just going to use this outside borders here in the font border section. Change the cell style of d eight to the good style. So click on cell styles and click on Good. And then change the contents of cell b three into word art. So what I’m going to do is I’m going to cut the actual content of b three by going into the formula bar. I’ll insert word art, choose whichever one you want to have a look at, and then I’m going to paste the words. So there we go. So I hope you found that interesting and I hope you’re getting really to hand a grip with all of these formatting that you can do to cells. And I’ll see you on the next video.