Locked lesson.

Upgrade

About this lesson

Understand the coding in Custom Number Formatting.

Exercise files

Download this lesson’s related exercise files.

Number Formatting Part 2.xlsx
8.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.

Gift this course