Placeholder Image

字幕表 動画を再生する

  • >> 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.

>> Hello. Today we're going to talk about number formatting.

字幕と単語

動画の操作 ここで「動画」の調整と「字幕」の表示を設定することができます

B1 中級

数字の書式設定 - 通貨、会計など - Excel 2013 (Number Formatting - Currency, Accounting and More - Excel 2013)

  • 60 0
    陳虹如 に公開 2021 年 01 月 14 日
動画の中の単語