Locked lesson.
About this lesson
Understand the coding in Custom Number Formatting.
Exercise files
Download this lesson’s related exercise files.
Number Formatting Part 2.xlsx8.7 KB Number Formatting Part 2 - Solution.xlsx
8.7 KB
Quick reference
Number Formatting Part 2
Number Formatting explained using the CRaFT methodology.
When to use
Number Formatting can assist in building a financial model in excel which is easy to understand and navigate through.
Instructions
- Cells may be individually formatted using CTRL + 1 or ALT + O + E in all versions of Excel:
- To the uninitiated, coding custom number formats may appear incomprehensible. However, understanding the following tables from Microsoft soon puts things into perspective
Login to download
- 00:05 Having now looked to quick number formatting,
- 00:07 let me turn my attention back to the more long winded number formatting.
- 00:12 So brought up by pressing Ctrl+1 to get Format Cells.
- 00:18 It's the first tab in the dialogue box that results.
- 00:23 Okay, so let's do a quick crash course in number formatting.
- 00:28 What is the problem here?
- 00:30 If I do control one to bring up the format cells dialogue box and
- 00:33 go back to number, these are all self explanatory.
- 00:38 I don't think I really need to talk about much here.
- 00:40 The issue is the bottom one, Custom.
- 00:43 Custom has all this Swahili sort of stuff in here.
- 00:47 And to a lot of people they just avoid it, think, I have no idea what this means.
- 00:51 But it's not quite as complex as it looks.
- 00:55 Here I've put a little kind of definition set of the main things that are in there.
- 01:01 The hash or pound symbol means put a number if needed,
- 01:04 whereas 0 means put a number if you have to or not.
- 01:08 Let me explain, let's use the James Bond decimal as an example, 0.07.
- 01:15 If I actually format this using
- 01:24 0.00, click OK.
- 01:26 I don't really see what's happened.
- 01:30 If I instead change it in Custom to has point hash hash and
- 01:37 click OK, do you see it does 0.07?
- 01:42 Because it doesn't need the number before the decimal point because that's implied
- 01:46 isn't it?
- 01:46 We've got in the fact that we know it's zero.
- 01:49 The first zero at the decimal point is required though because we've got
- 01:53 zero tenths and seven hundredths.
- 01:56 So that's what it means.
- 01:58 Let me show you again with another example, if I actually went 00.000 and
- 02:04 clicked OK, do you see it showing 00.070 because the 0,
- 02:11 it doesn't mean put a 0, it means force a number to be shown.
- 02:17 This can be really useful.
- 02:20 If I actually put a 7 here instead, and change it to a different text,
- 02:26 let's say I'm going to actually put in here Invoice Number.
- 02:35 In speech marks, and then put one, two, three, four, five, six.
- 02:39 Do you see here the sample is actually showing me what's going to happen anyway?
- 02:43 And if I click OK, it looks like that is text invoice number and
- 02:48 I've got five 0s and then the number 7 was in fact is just the number 7.
- 02:54 If I do equals that cell here multiplied by 7 for instance, I get 49.
- 03:00 It's just the number 7, so that's sort of the rudiments of mixing text and a number.
- 03:07 Next time out I want to look at number formatting again about how we can
- 03:11 get numbers lined up and putting brackets around negatives and
- 03:15 putting a bit of color in there.
Lesson notes are only available for subscribers.