Custom Number Formats

Quick reference

• 00:04 Another useful trick for
• 00:05 reporting is taking really granular control over your number formats.
• 00:10 Most of us know that we can grab a set of data here, and go and
• 00:14 hit the comma up the top and maybe drop off some decimal places.
• 00:18 But what if you want even more control than what the user interface offers you?
• 00:23 Maybe you want to build a new date format for example,
• 00:26 that also shows the day of the week.
• 00:29 Where you're not going to find that in your little pre-formatted dropdown
• 00:33 options, but you can build it if you know how.
• 00:35 So check this out, here's what we're going to do.
• 00:37 I'm going to press Ctrl+1 to bring up the Format Cells ialogue.
• 00:41 Of course, you can right click and choose Format Cells as well.
• 00:43 And what you can see here is that we have a lot of different number
• 00:46 formats available to us.
• 00:48 So because all dates are numbers, if I was to format this,
• 00:51 the first cell would show up as 45,635.
• 00:54 I could use numbers, or currencies, or accounting, and
• 00:57 I've got some control over changing these things.
• 00:59 But obviously, this makes more sense as a date.
• 01:03 Now, what you don't know though is down under Custom, we actually have
• 01:08 a series of codes that we can use to format things exactly the way we want.
• 01:13 This is the format string that we're using type, yyyy-mm-dd, and
• 01:17 that is what is actually showing as a year, month, day in here.
• 01:21 Yours may be different depending on what you actually have in Excel, but
• 01:26 here's what I want to do.
• 01:27 I want to provide my own custom string.
• 01:30 Now, the four y's will give me a four digit year.
• 01:33 The two m's will give me a two digit month, two d's give me a two digit day.
• 01:39 If I back this up to one d,
• 01:41 you'll notice that I get just dash 9, because it's December 9th.
• 01:45 If I go with two d's, I get the 9, and if I go with three d's,
• 01:49 I get Mon for Monday, and four d's gives me Monday.
• 01:53 So this is interesting, because I can actually use this to say, all right,
• 01:56 let's try this.
• 01:57 We're going to put in a space, parenthesis, ddd.
• 02:01 And you can see that we actually get Mon showing up in parenthesis afterwards.
• 02:05 And if I close that, there we go.
• 02:08 So what I'm going to do is I'm going to click OK.
• 02:10 And what you can see is that all of my days now have not only the original date,
• 02:16 but also the day of week.
• 02:17 And if I left the aligne it, it'll actually show a little bit easier, so
• 02:21 I can read it.
• 02:22 Now, recognizing this, we actually have the ability to do a lot more with this.
• 02:26 What if all my product codes here are all supposed to be five digits long,
• 02:32 or even six digits long?
• 02:34 Well, let's control this.
• 02:36 So this is a common problem with Excel is that it drops leading zeros.
• 02:40 Now, interestingly enough, if you go and
• 02:42 you take a look at some special formats like social insurance number,
• 02:45 you'll see that you can actually get leading zeros on there.
• 02:48 So what does that actually do?
• 02:51 Well, if we select social insurance number and we go to custom,
• 02:54 we can see what the number format is for that string.
• 02:57 Now, that's not exactly what I want, but it does give me a template to start from.
• 03:02 So let's try this, let's go and back off a few 0's here.
• 03:06 This gives me a six digit string with some tech with a space in the middle.
• 03:10 But if I come back here and delete that,
• 03:13 notice that my first data set is going to be 012345, interesting.
• 03:18 Let's go and hit OK, and see what happens.
• 03:21 Look at that leading 0's on everything.
• 03:23 So if I put six 0's into my custom number format, it puts a numeric placeholder for
• 03:29 0 in place of that if it doesn't have a character to fill it, very cool.
• 03:34 Let's go and take a look at our actuals and budgets when we actually got here.
• 03:38 So again, I'm going to go back into the Format Cells dialog box.
• 03:41 This is the number format that was actually used there.
• 03:45 Now, this was created by clicking the comma style,
• 03:48 which gives us our accounting and dropping a couple of 0's off, and
• 03:51 it writes this horrible, ugly format that you see here.
• 03:54 The big secret to understand,
• 03:56 the opening-closing parenthesis are a parenthesis, so those make sense.
• 04:01 The number signs give us a number if a value exists for that place and
• 04:04 suppresses it.
• 04:05 If not, the comma gives us a 1,000 separator,
• 04:09 the 0 gives us a placeholder of a 0 even if there's no number for it, and
• 04:13 the underscore is an alignment out from the right-hand side.
• 04:17 So knowing all this, can we go and build something really cool for our variants?
• 04:24 Let's go right click and Format Cells ,and I'm going to show you a really cool little
• 04:28 cost accounting trick with this one here.
• 04:30 Where we go, number sign, or pound, or hash,
• 04:35 hash, hash, 0, space, in quotes F.
• 04:39 And this puts in an F to indicate that we have a favorable or positive variance.
• 04:44 We also have a semicolon that allows us to separate between positive and
• 04:48 negative formats.
• 04:49 So in here, we can also say, hash, comma, hash, hash, 0,
• 04:54 space, quote U for unfavorable.
• 04:57 Our next semicolon says, how would you like to format if this is a 0?
• 05:01 And I'm going to go and put in a dash, and I'm going to go and say,
• 05:05 close the quotes on that.
• 05:07 And at this point, that's probably good enough, okay?
• 05:10 So before the semicolon is positive number format, after the semicolon is negative
• 05:15 number format, after the second semicolon, it is what happens if we get into 0's.
• 05:21 And when we go and say OK, what you'll see is that we get a cool little thing here,
• 05:24 where all of our positive numbers have an F behind them.
• 05:27 All of our negative numbers have a U behind them, and if we did have a 0,
• 05:31 let's say, this was 12,000 right on the nose,
• 05:34 you'll notice that it gives us a dash instead.
• 05:37 So there you go, you want to experiment with this and play around with it.
• 05:41 But custom number formats can be very powerful for
• 05:44 making your data look exactly the way you want it to look.

Lesson notes are only available for subscribers.