Locked lesson.

Upgrade

About this lesson

Learn to build your own custom number formats to display dates, values and variances how you want to see them.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Custom Number Formats.xlsx
10.3 KB Custom Number Formats - Completed.xlsx
10.3 KB Custom Number Formats.xlsx
14.7 KB

Quick reference

Topic

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 actually built in a 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)
  • The “-“ puts in a dash as text
  • The ? pads from the right with a space
  • The F and U have no special meaning, so are treated as text
  • 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!
Login to download
  • 00:03 In this video we're going to look at Custom Number Formats. These are cool little tricks that we can use to actually display
  • 00:10 numbers the way that we want to see them rather than by the built in defaults that Microsoft actually provides in the program.
  • 00:17 I'm going to show you three different scenarios that we can use them here for different ways and different tricks that we
  • 00:22 apply to our numbers. The first one is going to start with dates.
  • 00:27 As you can see I have dates already formatted down between cells A4 and A9
  • 00:32 but the problem we've got with them is that they could be month/day/year, they could be
  • 00:35 day/month/year and if you're shipping your spreadsheets internationally that can be really confusing.
  • 00:39 So we dig down, we can see in A6 that we do have 4- 23 so this must be month/day/year it's just
  • 00:46 really not all that apparent unless you start digging into the data.
  • 00:49 Well fortunately there is an ISO standard of a four digit year and a two digit month and two digit date that actually would allow us to remove
  • 00:57 all the ambiguity from a particular area. So let's work with that and build that ISO standard
  • 01:01 in Excel. The way that we do that is we select our range of data.
  • 01:04 We're going to right click and say Format Cells.
  • 01:08 That brings us into the Format Cells dialog box and what you should notice is that we have the Date here. There's the date format.
  • 01:15 We can also go and click on Custom
  • 01:17 and when we do that there is a couple of things that we want to see.
  • 01:20 Number one we can see up in the top left is a sample and this sample will always react to the
  • 01:25 first cell in your selection. As you can see here it is 3-06-12.
  • 01:30 It also gives us a Type which we can actually fill in our own custom codes. You can see that this
  • 01:35 is the one we have selected with M for single digit month - dd for double digit day and
  • 01:41 and yy for double digit year.
  • 01:44 Let's go and change that. We're going to go with a four digit year so YYYY and you can see the sample changes to 2012.
  • 01:52 We'll put in the dash MM
  • 01:55 dash DD.
  • 01:58 MM being the code for month and DD being the code for day. So that'll actually give us our ISO compliant date.
  • 02:04 That's perfect and say OK.
  • 02:07 Some other tricks we can do with custom number formatting is we can look at this HMO code. Let's assume for a second that the HMO
  • 02:15 code actually needs to be an eight digit code with leading zeros. So let's go and format that. We'll right click and say Format Cells.
  • 02:23 And again we're going to go to Custom and we can see that we have our number up at the top. Let's go take our Type and we're going to put in
  • 02:34 eight zeros and you can see that the sample which had seven digits now has a leading zero here when we apply it and say OK.
  • 02:42 It now shows up, all of them are eight characters long so that's one way that you can put leading zeros on a cell.
  • 02:49 Now be aware that when you click on the cell itself the value is still 1043834.
  • 02:55 It doesn't have a leading zero in it if that leading zero is important that it actually be in the cell then you need to format it as text but if you don't
  • 03:03 have to worry about that if it can just look like it then this is the way to go.
  • 03:08 The last trick I want to show you is around Variances and one of the big challenges especially when we're dealing with accounting
  • 03:14 style things is that Variances can be a little bit confusing because when we're looking at revenues and expenses nobody knows if a
  • 03:21 negative variance is a good thing or a bad thing because it's different depending on what you're looking at.
  • 03:25 So here's a little trick you can use in financial statements to show favorable or unfavorable
  • 03:31 variances .So again we're going to select our range of data we right click and say Format Cells
  • 03:37 I'm going to move this out of the way so we can see that we're working with 600. What I'm going to do is I'm going to go to Custom.
  • 03:43 In Custom I'm going to go and build a custom number format that's a little bit trickier than the other one. It's going to start with a pound sign.
  • 03:52 What a pound sign does is gives me a value if one exists. In this case it says 600 alright I'll give you the value of 600.
  • 03:58 Well now I'm going to put in a comma
  • 04:00 and another pound pound and zero. So what that's saying is give me a number if it exists then
  • 04:06 I'll put in a thousands separator, and a couple of numbers if they exist and then a zero
  • 04:12 that zero will always put in a value. I'm going to say F for favourable.
  • 04:16 I'm going to put in a semi colon so what's happening now is we're saying the semi colon we're going to move to a new
  • 04:20 number format. So the first one was positive the second one is negative so if it's a negative number
  • 04:26 we'll go number number number zero space U for unfavorable
  • 04:32 semi colon and then I'm going to put in quote dash quote. Remember everything between the quotes
  • 04:38 goes in as text and this is the zero format. That's what the second semi colon does is we go positive
  • 04:44 negative zero. And to line these up I'm going to put in two question marks. Which will
  • 04:49 align to the left, bump it out two characters. When I say OK to that
  • 04:54 we can see that I have 600 favourable, 3200
  • 04:57 favourable a zero that's actually lined up nicely with the last decimal
  • 05:01 place and then I've got unfavourable for my negative numbers. When I look in the cell it still runs with a formula
  • 05:06 but it actually returns me something that's a little bit easier to read and a little bit easier to explain
  • 05:11 to other users: Favourable is good and unfavourable is bad.

Lesson notes are only available for subscribers.

Gift this course