Locked lesson.

Upgrade

About this lesson

How to use Custom Number Formatting.

Exercise files

Download this lesson’s related exercise files.

Number Formatting Part 3.xlsx
8.7 KB Number Formatting Part 3 - Solution.xlsx
9.1 KB

Quick reference

Number Formatting Part 3

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

  • The first section, [Blue] _(#,##0_),specifies the formatting for positive numbers. In this case, positive numbers will be formatted blue.
  • The underscore and open bracket, _(, is not necessary, strictly speaking, but ensures there is space made for an open bracket, even though there is no such character shown.
  • #,##0, ensures positive numbers contain thousand separators (where needed).
  • Finally, the _) requires Excel to maintain enough space at the right end of a cell for a digit (not necessarily zero and a close bracket). It should be noted that a separate underscore is required for each character that is to be allowed for.
  • The second section, [Red](#,##0), specifies the formatting for negative numbers. It is similar to the first section, but colors the number red, and encloses it in brackets.
Login to download
  • 00:04 Continuing looking at number formatting.
  • 00:08 Again, I'm going to use the Format Cells dialog box,
  • 00:13 I look at the number tab using Ctrl+1.
  • 00:17 I've taken the liberty of putting a number into my Excel spreadsheet already here in
  • 00:22 cell C4, and it took me a while to actually practice and get this right.
  • 00:26 It took a lot of memorizing, number 1, 2, 3, 4, 5, 6, 7, 8,
  • 00:31 9, 0, pretty sophisticated, aren't I?
  • 00:34 To demonstrate number formatting, I'm going to take the negative of that in
  • 00:38 the cell beneath, then I'm going to sum them both up.
  • 00:45 And then I'm going to put the word dog in the cell at the bottom because dog isn't
  • 00:49 a number and I'm talking about number formatting.
  • 00:53 Now, to show you the rudiments of number formatting, let's highlight all four
  • 00:58 cells, and go Ctrl+1 to bring up the Format Cells dialog box, and go to Custom.
  • 01:04 Now, the square brackets here define color, so if I go here,
  • 01:08 open square brackets, red, close square brackets and just put the 0.
  • 01:13 Just say I want a general number in here, watch what happens when I click OK.
  • 01:18 I get 1, 2, 3, 4, 5,6, 7, 8, 9, 0, as red for
  • 01:23 the first one, negative that in red, 0, dog stays blue
  • 01:27 because dog is text, it's all about number formatting here.
  • 01:34 So just understanding and putting it in context.
  • 01:37 Now normally you don't see numbers all in a crash like that,
  • 01:42 what you do is you'll have a comma separator.
  • 01:45 So we've got the comma separator over here, which is the 1,000 separator, so
  • 01:49 let me show you how that would work.
  • 01:50 And let's change it back to blue at the same time.
  • 01:54 So we're gonna go for custom, Blue with
  • 01:59 a capital B, then #,##0,.
  • 02:05 That means put a 1,000 separator in if you need it,
  • 02:09 the 0 is to show 0s but the hash is to say, put a number where necessary.
  • 02:15 And the sample actually shows the 1.234.567.890.
  • 02:21 It won't do the colors, but it will show you everything else that's in there.
  • 02:24 I'm gonna click OK, lovely, everything now is blue color.
  • 02:29 Now I want negatives to be red, and
  • 02:32 I also want negatives to be in brackets, so let me try again.
  • 02:40 To do this, there's this thing called delineator, and what a delineator is,
  • 02:46 is it defines, depending on which side of the delineator it is,
  • 02:50 how to treat a number that meets a certain condition.
  • 02:54 As soon as I put a delineator in here, notice what's happened,
  • 02:59 is that I've got on this side, it's now the non-negative numbers, and
  • 03:04 on this side, it should be the negative numbers.
  • 03:06 So positives and zero here, and negatives over here.
  • 03:10 So if I do red in square brackets and then round bracket, hash,
  • 03:16 comma, hash, hash naught close round bracket, and click OK.
  • 03:21 That should have negatives now appearing in red in brackets, and it does.
  • 03:27 Look at that, wonderful.
  • 03:29 Now as I've got amount, I've centered it.
  • 03:31 I'm not going to center it anymore, I'm going to go back and
  • 03:35 I'm going to left align it, and you will see if I make this bigger,
  • 03:39 that those numbers don't actually line up.
  • 03:42 Now to make them line up, I'm going to use this underscore character here.
  • 03:46 To do that, I'll go back to Format Cells and
  • 03:50 what I'm going to do then is actually say what I'm going to be making space for.
  • 03:57 So, Ctrl+1.
  • 03:59 Back in here, for the positive side, I need to make space for brackets.
  • 04:05 So straight after the closed square brackets are blue,
  • 04:07 I'm going to put underscore open round bracket, and before the semicolon,
  • 04:12 after the 0, I'm gonna put underscore close round bracket and click OK.
  • 04:20 Do you see now that the numbers line up?
  • 04:24 I've made space for an invisible closed round bracket.
  • 04:27 And the reason I put one the other way is if I happen to have bizarrely left align,
  • 04:31 it would work the other way too, that's the only reason I did that.
  • 04:35 The next thing I want to do is actually make the 0s a dash, so
  • 04:39 I can tell the difference between a 0 that's approximately 0, and
  • 04:44 something that is exactly 0.
  • 04:46 And we'll cover that off in the next session.

Lesson notes are only available for subscribers.

Gift this course