Understanding Custom Number Formats

Quick reference

• 00:04 So an interesting thing about data in Excel is that Excel only actually sees two
• 00:09 types of data.
• 00:11 It recognizes text and it recognizes numbers.
• 00:15 Now, why is that and is it even relevant to us?
• 00:19 Well, yeah it absolutely is.
• 00:22 Basically what Excel does,
• 00:23 it makes use of number formatting codes to display that data in different ways.
• 00:28 Even dates are just numbers,
• 00:30 they actually represent the number of days since January 1, 1900, but
• 00:35 with a specific formatting code on top of them to render them as a date.
• 00:40 That's why we can actually subtract a value from a date and
• 00:42 it returns a previous date or add a day to a date and it works as well.
• 00:47 So that's kind of a cool thing, especially when we know,
• 00:50 because we actually have the ability to write our own custom number formats
• 00:55 to display the values in the way we want to see them.
• 00:58 The secret though is to understanding the syntax.
• 01:02 And the syntax basically breaks down like this, its got four parts.
• 01:05 The first part is a positive number format and then there's a semicolon.
• 01:10 And then we go into a negative number format, separated again by semicolon.
• 01:14 We have a zero format, a semicolon, and a text format.
• 01:19 So some examples of this.
• 01:20 You may wanna set up something that says, maybe it reads as + whatever the value is.
• 01:25 Maybe it's 9.50.
• 01:28 So if we wanted plus 9.50 to show for a positive number and negative 9.50 for
• 01:31 a negative number, a dash if it were actually 0.
• 01:36 Or if it were text we'd actually like to return the value,
• 01:39 not a number, we can do that with a customer number format.
• 01:43 Likewise, we could change some things up a little bit.
• 01:45 We could put parentheses around our zeroes if you prefer to
• 01:48 see your numbers that way, or use a 0 for the zero character instead of a dash.
• 01:53 Again, the key part is it's positive number format, semicolon,
• 01:56 negative number format, semicolon, zero format, semicolon, text format.
• 02:01 Now something that's important that you need to be aware
• 02:05 of is that certain characters are reserved for
• 02:07 specific purposes in the number formatting code, like a semicolon for example.
• 02:12 So if you wanted to return a semicolon, n, as a piece of text,
• 02:16 you would need to wrap that in quotes within your custom number format.
• 02:20 So, what different characters are reserved?
• 02:23 Well, the first one would be 0,
• 02:25 which is used to preserve leading and trailing zeroes.
• 02:28 So when you actually go and throw out a number format, here's a comparison chart
• 02:32 that shows you what a custom number format would look like.
• 02:35 And if we go to the second column to the end which goes 0000.00, you'll notice
• 02:40 that the value of 5.6 actually maintains both leading and trailing zeros.
• 02:45 So if there is a numeric character that will take that place it will put it in,
• 02:50 but if there isn't, it will actually contain a zero even though the original
• 02:55 value of the cell is still only 5.6.
• 02:58 There's another character, which is the hashtag,
• 03:00 which is used to suppress leading and trailing zeros.
• 03:03 Like zero, it will put a value in if one exists for
• 03:07 that placeholder, but if it doesn't It leaves it blank.
• 03:11 So if you look at, again, the second column from the end, where we've got 5.6,
• 03:16 it'll render as 5.6.
• 03:17 But 1456 will render properly as 1456.
• 03:22 So that's the difference.
• 03:23 Now we can use a mixture of these when we actually build up customer number formats.
• 03:27 And when we get into a practical example, we'll do exactly that.
• 03:31 Dates also have their own special reserved characters.
• 03:35 The letter d is used to represent days.
• 03:37 And as you can see a single d would return either a 1,
• 03:41 in the case of November the 1st, or 16, in the case of July 16.
• 03:46 Where we go with two ds, we would get 01 or 16.
• 03:50 When we go into three ds,
• 03:51 we actually get the day of the week abbreviated to three characters.
• 03:55 And if we go four ds, we get the full day of the week.
• 03:58 The same kind of syntax is used for month.
• 04:01 Looks very similar, we're just using the m character instead.
• 04:05 Years are a little bit different in that we don't really have a concept
• 04:10 of working with a three digit year, so it just returns the four digit value.
• 04:15 So those are some reserved characters that we can use as well.
• 04:20 Times also have their own character set.
• 04:23 Some sample formats here, might look like this.
• 04:26 If you go with h:mm, you'll get that hour, minute setup in a 24 hour time clock.
• 04:34 If you add :ss to the end you will also get seconds.
• 04:39 By putting in the characters AM/PM,
• 04:43 we now convert from 24 hour clock,
• 04:47 military time, into the 12 o'clock time format that we're very used to.
• 04:53 So, if we wanted to preserve leading zeroes, like on the very last column,
• 04:56 we would put in two hs, two ms, and also add the AM/PM to show it in AM or PM time.
• 05:03 Now, some interesting things about times.
• 05:06 They always round down.
• 05:08 So, when you look at these, they're actually holding seconds at 42 seconds and
• 05:12 27 seconds, you'll notice that they always round down.
• 05:16 Excluding your AM or
• 05:17 PM will always yield your military time with your 24 hour clock.
• 05:22 I'll also show you really quickly some interesting or
• 05:25 different formats that you might wanna know.
• 05:27 In order to return July 17th, 2017, we use this particular format.
• 05:32 If we wanted 17-Jul-2017, we'd change it up a little bit.
• 05:36 The implications of this are huge as we can build whatever customized format we
• 05:40 want to display the date in the format that we need.
• 05:44 Because it's based off the date serial number which is stored in Excel,
• 05:47 that means that we can send it anywhere in the world.
• 05:50 And if we've customized our format it stays exactly the same.
• 05:53 So if you've ever had to send data from the US into Europe, you'll recognize that
• 05:58 we look at dates differently as far as month, day, or day, month.
• 06:02 Customize your format into something like the ISO format of year,
• 06:05 month, day, save that as your custom format, and guess what?
• 06:09 You never have to worry about that again because the format you've created
• 06:12 will stick to the data when you send it out and there's no question anymore.

Lesson notes are only available for subscribers.