Formats vs. Styles

Quick reference

• 00:04 As part of layout tips, we need to address the idea of formats versus styles.
• 00:11 Most modelers are familiar with the concept of formats, but not so
• 00:15 many seem quite aware of the advantages of using styles.
• 00:18 And that's what I'm going to explain in this session.
• 00:21 To put it in context, we need to consider house style.
• 00:25 A lot of companies will spend big on developing brands and
• 00:29 corporate identities where particular colors schemes must
• 00:32 be used by all individuals within an organization.
• 00:36 And they may specify the complete colors and issue documents such as this and
• 00:41 this where they might describe the colors, the fonts, and so on.
• 00:45 In a model there's no exception to this rule.
• 00:49 We actually will specify what the particular formats mean, data input,
• 00:54 formula, error message, etc.
• 00:56 And you will see examples like these throughout the modeling industry.
• 01:01 Styles are simply a pre-defined format applied to a cell.
• 01:05 They cover the six basic elements that a format does, namely number format,
• 01:10 alignment, font, border, patterns and protection.
• 01:14 The actual dialog box that comes up allows you to switch on or
• 01:18 off the others that you wish to override.
• 01:21 Common applications are in assumptions, headings, and the top sections of models.
• 01:26 Let's have a look at some illustrations.
• 01:29 Back to our Excel template file that we're developing.
• 01:34 Last time out I formatted these cells here.
• 01:38 I'm going to contrast this now with styles in this section.
• 01:43 Just for the record, I've renamed these sheet tabs between the two sessions.
• 01:47 This is First Sheet, this is Second Sheet, if you're playing catch up.
• 01:51 I'm going to actually look at new cell styles.
• 01:52 To do this, it's on the Home tab.
• 01:55 Then you go to the Cell Styles button here,
• 01:57 which may look different on your machine.
• 02:00 It depends on the resolution you have, as to how the ribbon will appear.
• 02:04 It may look a little more like this when you click on the that.
• 02:07 And I want to go down to the bottom, New Cell Style.
• 02:11 One of my favorite keyboard shortcuts, this one,
• 02:16 it's the rather intuitive New Cell Style, New N, Cell C, Style S.
• 02:22 It's got nothing to do with those letters whatsoever.
• 02:25 It's Alt+H J N is all I can say.
• 02:32 I'm gonna call this style Assumption.
• 02:39 Having renamed this assumption, notice the six check boxes here.
• 02:44 I've got them all checked cuz this is a training session.
• 02:47 That means to overwrite the underlying formatting in the cell that's in there
• 02:52 at the moment before you apply this style.
• 02:54 So I've got number, alignment, font, border, fill, and protection.
• 02:59 I'm going to do that here for the sake of training, but
• 03:02 it's an artificial environment.
• 03:03 In the real world you probably wouldn't do that.
• 03:06 For instance if you had an assumption,
• 03:08 yes you might want to have a particular colored font.
• 03:11 Yes, you might want to put a border around it.
• 03:13 Yes, you might want a particular fill.
• 03:15 And yes, you'd certainly want to unprotect it.
• 03:18 But you probably wouldn't want to define the number and
• 03:21 the alignment because numbers will be aligned right.
• 03:23 Percentages might be in the middle, dates might be in the middle.
• 03:27 Text will be to the left.
• 03:28 You can apply more than one style to a cell.
• 03:32 So you can keep doing this with more and more styles while having thousands of
• 03:37 styles in here that actually just total up over time.
• 03:42 Let's go through that.
• 03:43 We're gonna do exactly what I did before.
• 03:44 Click the Format button.
• 03:47 So I had in here I was Number to 0 decimal places with 1,000 separator.
• 03:54 Alignment was shrink text.
• 03:56 Font was bold italic bright red.
• 04:00 The border I was going to have like that.
• 04:06 My fill I'm going to go for that color, and
• 04:09 the protection unlocked again, and click OK.
• 04:13 Here you go then.
• 04:14 Here's a big difference,
• 04:16 the key difference between formats and styles, watch this.
• 04:21 Nothing happens!
• 04:24 Now apparently this is intentional since Excel 2007.
• 04:26 I still think it's a mistake, don't tell anyone.
• 04:32 But what has actually happened is in Cell Styles,
• 04:35 you got this new custom section which has got Assumption.
• 04:38 It will put all these in alphanumerical order.
• 04:40 If I click on this now or hover over it,
• 04:43 you can see it does all the different styles in here.
• 04:47 I'm going to use Assumption.
• 04:47 And in fact I can go Ctrl+A for the whole lot and
• 04:51 put in here the whole cell style Assumption.
• 04:55 And I'm going to do it down here too, Ctrl+A, cell style Assumption.
• 05:00 Beats doing the formatting doesn't it?
• 05:01 And on the second sheet, do the same thing.
• 05:04 Ctrl+A, cell style Assumption, look at this.
• 05:08 Ctrl+A, cell style Assumption, I'm a machine.
• 05:13 Ctrl+A, cell style Assumption, done.
• 05:19 Fascinatingly, what's the point of this?
• 05:22 The point's this.
• 05:25 How often have you had to build a model in an Excel file where at the last minute
• 05:28 someone's got you to change something that has been painful.
• 05:30 One of those things might be a change of format.
• 05:34 You can do this.
• 05:35 Just click back in here, and you can go to Cell Style.
• 05:39 And you can modify your assumption.
• 05:41 Where's modify?
• 05:43 Well, believe it or not, you right-click on here, and there it appears.
• 05:48 Let's say somebody wants to change it to a different color.
• 05:52 Maybe I'll go for a blue background is what you want.
• 05:55 I click OK, then OK again, this time it will update.
• 06:00 Look at that, it didn't just do it for the cell I'd highlighted.
• 06:04 It did it for all the cells similarly styled,
• 06:08 not just there but throughout the sheets.
• 06:12 Now that's cool, very useful to put styles in here.
• 06:17 I'll talk more about it again next time.

Lesson notes are only available for subscribers.