Displaying Variances with Custom Number Formats

Quick reference

• 00:04 As an accountant, one of the things that used to drive me crazy was putting
• 00:08 a financial statement in front of someone and
• 00:10 watching them try to do the mental math to understand whether a variance number
• 00:14 showing as negative was good or bad.
• 00:17 The issue, of course, is that when you look at revenue,
• 00:20 if your revenue is less than budget, that's a bad thing.
• 00:23 But if your expenses are less than budget, that's generally a good thing.
• 00:26 So a negative number can show up in either location, but one's good, one's bad.
• 00:30 Nobody likes to try and work through that problem.
• 00:33 So what I'm gonna show you here is how we can apply custom number formats
• 00:37 to put in an old cost accounting trick to actually help people out with this.
• 00:41 So the first thing I'm gonna do is I'm gonna select some data here.
• 00:44 And I'm gonna right-click on this guy and go to Format Cells.
• 00:48 And you'll notice right now that we have a custom number format, but we have
• 00:52 the ability to go through and create any kind of number format that we want.
• 00:55 So if I wanted a number with a thousand separators and
• 00:58 zero decimal places that have red in it, I could go down, select at first,
• 01:02 then look at the custom number format and see what it actually brings back.
• 01:06 And notice we have a positive format, semicolon,
• 01:09 negative format, there's no zero format.
• 01:12 If it ends up as a zero or text, it defaults back to the first format.
• 01:17 But can I use this?
• 01:18 Can I modify this to actually do something?
• 01:21 Let's try it out.
• 01:22 Let's build our own format.
• 01:23 What we're gonna do is we're gonna go with 0,000, space, F, for favorable.
• 01:31 Notice there's a minus in front of it though.
• 01:33 That's because it's a negative number and I'm building a positive format.
• 01:37 So I'll click my semicolon right now, and now it goes blank.
• 01:41 Let's try this, 0,000, space, U.
• 01:47 Well, that will give me a U for unfavorable.
• 01:50 And then I could try semicolon quote dash quote because I kind of like a dash,
• 01:55 if it's a zero number.
• 01:57 And when I say OK, it actually changes this up now.
• 02:00 So now I can see that this is unfavorable, favorable, unfavorable, unfavorable and
• 02:04 nobody has to do the mental math.
• 02:06 So that's kind of cool, but now, let's try and copy this now.
• 02:10 So we're gonna double click on the format painter.
• 02:13 If we single click on it, and click somewhere, it copies the formats and
• 02:16 turns off.
• 02:17 But if we double click the format painter, it keeps painting.
• 02:21 And that's pretty nice, except that.
• 02:24 Now I'm just gonna go click it to turn it off.
• 02:26 You'll notice that my number format didn't quite work.
• 02:29 It's preserved leading zeros, and that's not really what I want to see.
• 02:34 So I'm gonna modify this format down here, and
• 02:38 let's see if we can get this to look a little bit better.
• 02:41 Notice it gives me a sample up in the top.
• 02:43 So let's try and change this up.
• 02:45 Instead of going with number, number, or 0,000, let's try this,
• 02:50 #,##0.
• 02:54 And again we'll try this here, #,##0.
• 02:59 And notice that it seems to work a little bit better now,
• 03:01 drops the leading characters.
• 03:02 The reason I'm putting a zero is if I have a value of 0.2 or something like that,
• 03:07 I'd like to see it as a zero, okay?
• 03:09 That way I can actually make sure that it doesn't just disappear on me all together.
• 03:13 So now when I say OK, it suppresses things and that looks a little bit better.
• 03:17 And I could go again, I could copy once and paste or double click and
• 03:22 copy with our format painter to get data into a different area.
• 03:26 Now, that's kind of neat, but
• 03:28 it might be better if I actually made this even a little bit more obvious.
• 03:32 And I can do that as well.
• 03:34 Let's right click, let's go to format cells.
• 03:37 And in front of my custom for negative, right after the semicolon,
• 03:42 I'm gonna go and put in square brackets the color code red.
• 03:45 Now, there's only a few color codes you can use red, blue, green, cyan.
• 03:50 There's eight altogether I believe.
• 03:52 Or you can use color1, color2, spelled the American way of C-O-L-O-R,
• 03:57 no u, will allow you to get those things as well.
• 04:03 When we do this, we say, OK.
• 04:05 You'll notice that I now get red for my negatives as well.
• 04:08 So this is kinda the cool.
• 04:10 Now, you'll notice that I did not need to wrap my Us or Fs in quotes.
• 04:18 That's because these characters are not reserved.
• 04:20 Had I wanted to put a semicolon in here though,
• 04:23 I would actually have to put that in quotes to make sure that it comes out.
• 04:26 Now the best part about this particular statement here is when the impact it has
• 04:31 when you're actually putting it front of someone.
• 04:33 Because you can land this in front of someone who's never seen it before and
• 04:36 say here's your financial statement.
• 04:37 Now, let me tell you why it says FU all over it.
• 04:41 And they look at you really nervous and laugh at you, but
• 04:44 then when you explain that F is a positive thing and U is unfavorable.
• 04:49 So we got a favorable and
• 04:50 unfavorable, they'll never ask you again about your variances.
• 04:52 And that is what helps people consume their information quickly.
• 04:56 That's why I love this particular trick.
• 04:58 It's got a funny intro story to get them going and they'll never forget.
• 05:02 So that's kinda cool.
• 05:03 It's a neat little trick that you can use.
• 05:04 There's all kinds of other number formats that we can use, but this one here I found
• 05:08 very, very useful when trying to put out intelligence in front of people.

Lesson notes are only available for subscribers.