Locked lesson.
About this lesson
Learn to build your own custom number formats to display dates, values, and variances how you want to see them.
Exercise files
Download this lesson’s related exercise files.
Custom Number Formats - Begin.xlsx26.1 KB Custom Number Formats - Complete.xlsx
26.1 KB
Quick reference
Custom Number Formats
Building your own custom number formats to display dates, values, and variances the way you want to see them.
When to use
Custom number formats are a helpful tool that allows you to control how numbers are formatted, even though Excel may not have the built-in number style that you want.
Instructions
Creating ISO-compliant dates
- Select A4:A9, right-click, choose Format Cells, and select the Number tab
- Click Custom, and enter yyyy-mm-dd in the Type box
- Notice that the sample (at top) updates to show what the currently selected data will look like
- Click OK and notice that the dates are all represented in the ISO YYYY-MM-DD format
Showing leading zeros
- Select C4:C9, right-click, choose Format Cells, and select the Number tab
- Click Custom, and enter 00000000 in the Type box
- Notice that all the numbers in the column now display with leading zeros (a 0 in the custom format will put in a value or a 0 if no data exists for that character place.)
Displaying variances
- Select F4:F9, right-click, choose Format Cells, and select the Number tab
- Click Custom, and the following in the Type box: #,### “F”;#,### “U”;”-“??
- The # will put in a value if one exists, and leave it blank if not
- The , will put in a thousands separator (but only if there is a value to the left)
- The ; separates the key pieces of the format (positive format; negative format; zero format)
- Anything between quotes is entered as text
- The ? pads from the right with a space
- The net effect is that positive numbers are displayed with an F after them (Favourable), negative numbers are displayed with a U next to them (Unfavourable), and zero values are displayed with a dash that is lined up with the rest of the values in the column
- This is a useful trick as you can build reports that never leaves someone wondering if a negative variance on an expense is a good or bad thing!
- 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.