字幕表 動画を再生する 英語字幕をプリント >> Hello. Today we're going to talk about number formatting. Number formatting is one of those often times overlooked things. It's the type of thing that can make a worksheet go from functional to impressive, or in some instances from worthless to worthwhile. So right here I've kind of got this little looking like maybe a quarterly finance thing, just some data. We'll format that in a minute. Right now it's functional, right? But it's just - it could look better. We'll have a look at that in a minute. I'm going to head over to sheet two. Notice I've got some numbers here, so I'm going to look at the three most common formatting, and then another one, so comma. So when I'm talking about number formatting I'm looking at the home tab. I'm in the number group right here. So you notice we've got these three things right here. They pretty much are what they look like. Here's comma. You can apply comma to a single cell, so 96 I apply the comma formatting to it, not much happens. Notice it got two decimal places which is not really what we would expect, or I don't know maybe it is, maybe it isn't. It doesn't do much. I probably should have chosen better numbers. If I do a larger number where commas are actually going to be relevant, well it's going to put commas in there, probably like what you would have guessed. The thing you might not have guessed is that it puts two decimal places on it. You can select a range of numbers and apply the comma format to them. Notice it doesn't do a whole lot. Comma formatting is about readability. If you've got big numbers, it's really nice to have those commas separating the digits. Let's move over to the next, probably most obvious one, let's look at these numbers. Well, let's look one at a time. Let's look at percentage. Notice that probably wasn't what you expected. It used to say 50.1. So what's happening here is there's a little math happening behind the scenes so that 50.1, the decimal place got moved twice and it got a percent sign. So, it doesn't just put a percent sign, some math happens. My best advice with percents is - so if you just enter data like 51, it doesn't look like much and it's general. If you take that same 51 and you just manually put the percent in there, that's how I would do it, because if you take an integer or just a normal number and convert it to a percent often times you don't get what you expected. I'll show you something kind of interesting. If I were to take this percentage, 51, and I covert that back to just a good old fashioned number, it's .51. So you can see there's some math conversion happening there. So, my best advice is if you're formatting something as percent, fine just go ahead and do that, but if you're entering data, right, like 40 percent I would just manually type in the percent sign and you'll see that it'll do the math for you and you'll probably get what you want. Okay, so that one's kind of complicated. Let's get the rest of it. Notice, no decimal places is the default here. But, this is kind of an interesting one, so this one right here. What is this? Accounting and currency. Pretty similar, both of them have a dollar sign. Notice if you hover over it you get a tool tip, accounting, so that tells you that that is accounting by default, used to be currency by default, so not really the easiest thing to work with. Me, let's say - so let's say I want to make these accounting. You can make some mistakes, but just assuming that is something, I like to pull down this menu and be sure of exactly what I'm doing. Here's accounting, here's currency. In a sense they're similar. Right, and the accounting folks have the way they like their numbers. So notice the difference here is they both have two decimal places, there was nothing mathematically that happened there. The dollar signs are aligned to the right of the cells with this weird split alignment, and that's the difference. Do you care? Well, you may and you may not. I personally don't care very much, but there are some people that do care, and that is the difference. There is one key difference though, it is negative numbers. Plain number, that's pretty different right? Like this is how I write numbers, but in the accounting world negatives are typically denoted by parenthesis. So that's something to look out for. No use in really - I don't think there's a ton of use in memorizing them, but just understand that this looks like something, but you've got options in here. Also understand that there's more here than just these three buttons, and there's no reason not just to be specific. Additionally, like everything else, there's always a dialog box at the bottom. So, alright like accounting isn't just as simple as that. If you want more decimal places this is where you can do that, particularly like date and time there's a million different ones, and you can even create your own custom formats which is beyond the scope of this video. Let me show you something else. So notice that comma had those two decimal places which maybe I want, maybe I don't. If you want to get rid of decimal places that's what these buttons do. Very useful. Alright, so decrease decimal to once, twice, that's all I can do. I can't go any further. There's no reason you can't grab a bunch of data and just kind of do the same thing. Let's say I want these percentages to have some decimal places which I don't know why I would. Alright, I mean I can do things like that, so that's completely in your control. So pretty simple, more straight forward for the most part. That's kind of how this works. Let's head back to sheet one and look at this in action. So here I've got just some kind of weird finances, a little worksheet going on here, not the most readable in terms of formatting. A common way to format numbers is kind of like this. Often times you will grab these, kind of this first row and the last row, and maybe just apply some formatting to those. I don't know, let's go currency or accounting, whatever you want to do. That's kind of a typical way to do it, and then these guys here look kind of goofy because they don't have any decimal places, so let's just give them some decimal places. Oops, wrong one. Alright, maybe I want to format it like that. I'm sure it would have made sense to just have them all be currency, but we're trying to make this a little bit more than trivial. Often times when you're looking at data like this there's something called the "total cell style" and so this is a style, it's not a number format. More often than not, when you have data aligned like this you will often times select that bottom row, head on over here to styles. This looks different sometimes. Pull down the more, and head to total. So all total does is it puts that top bottom double border on it, and it bolds it, and you can see it looks a lot better, really. So remember I kicked off this video saying that these formatting options, they don't do anything. I mean sometimes they do, like percent does something, comma sometimes does something, but it increases the readability of your data, and if you want someone to see it there's a good chance you do, then this might be something that you want to spend some time on. It's one of those things that you can look past, but it's also something that can add quite a bit of value to a worksheet. Thanks for watching.