Styles

Quick reference

• 00:04 In this video, I want to show you how a little bit of pre-planning and foresight
• 00:09 can actually help make updates to the styles of your workbook very, very easy.
• 00:14 So typically, what we do, and we're building something,
• 00:16 is we'll grab a set of rows for our header here.
• 00:19 And we'll increase the font size, and change the bold, set the color,
• 00:22 and all those kind of things.
• 00:23 And we do it all manually.
• 00:24 So when you get to another worksheet, you've gotta do the same thing again.
• 00:27 And after you've got 50 worksheets,
• 00:29 there's bound to be a couple that have an inconsistent style heading.
• 00:32 Put that in front of an accountant and
• 00:34 they lose their mind because they value consistency.
• 00:36 And this is what the Styles Gallery is all about,
• 00:39 is promoting consistency through your workbook.
• 00:42 So in this case, if I have a report title, for example, I could go and
• 00:45 choose the Title style.
• 00:47 And if I apply that consistently on every single workbook,
• 00:50 everything will be the same on every single worksheet,
• 00:53 because it's a predefined package that gets applied to the cells.
• 00:57 And if it's ever modified or
• 00:59 updated, it rolls through every single cell that has that style.
• 01:03 We also know that we can do number formatting using things like comma styles,
• 01:07 for example, to go and actually assign this block.
• 01:09 But this actually starts to show the problem that we have when our stuff has
• 01:14 been applied willy-nilly and ad hoc.
• 01:16 Notice that these data entry cells have this format, these ones do not.
• 01:21 But how do I roll it through to make it consistent without having to go and
• 01:24 apply it to each block of cells?
• 01:26 So let me just undo this and show you how I like to take care of this.
• 01:30 Now, Microsoft has provided some built-in styles for data.
• 01:33 So we've got an input cell here that would make it look like this nice lovely orange
• 01:38 here, not my favorite.
• 01:39 So what I'm going to do is I'm going to build my own input cell,
• 01:42 because in actual fact, when I'm building things,
• 01:45 I usually create multiple styles for what we actually have here.
• 01:49 I'm going to create one called DE, for data entry, underscore values.
• 01:53 And I'll typically create one for values, dates, and text.
• 01:58 And if I've got different numeric styles, I might have values underscore decimal
• 02:02 zero underscore decimal two or something like that.
• 02:05 Notice that it's picked up, that there's consistency amongst the cells but
• 02:09 not the border.
• 02:10 This one here has a bottom border.
• 02:11 So it says, well, I don't know what you want to do with that.
• 02:13 If you'd like to force a border on everything, we can do that,
• 02:16 but I'm going to leave it alone.
• 02:17 It says, your font is this, your alignment is general.
• 02:20 Okay, no problem, your number format is general.
• 02:24 Yeah, that's great, but you know what?
• 02:25 I don't really want that to be a general number format.
• 02:28 So I'm going to go to click on Format > Number,
• 02:31 I'm going to choose Accounting with no symbol and 0 decimal places.
• 02:36 Will say, OK, and what I'm going to do at this point in time is I'm going to say,
• 02:40 all right, let's accept this.
• 02:41 So, this cell style that I'm building is going to have my comma style with
• 02:46 zero decimals.
• 02:47 It's going to be a line general on the bottom.
• 02:50 It's going to use this specific font, it will not influence borders at all.
• 02:54 So if there's a border on the cell, it will leave it there.
• 02:56 If there's not a border, it will not add one.
• 02:59 It will put in the fill color that I have selected, blue, and
• 03:02 it will lock the cell from a protection standpoint.
• 03:05 If I go and say, OK, at this point, you'll notice nothing happens.
• 03:09 Why?
• 03:10 Well, because we just created the style, we now need to apply it.
• 03:14 And when we do, it looks like this, okay?
• 03:18 Well, but you still have to go through and apply it to every block.
• 03:21 So have I really saved myself any effort?
• 03:25 And the answer on this one is not during the setup, as a matter of fact,
• 03:28 that cost you time.
• 03:29 But check this out.
• 03:32 I now want to deploy this as an input template to somebody.
• 03:37 I'm going to go right-click on the style, and
• 03:39 I'm going to modify it because I realize that there's something I need to do.
• 03:42 I need to make sure that the protection is changed, and
• 03:45 I actually want the color to be different.
• 03:47 So I'm going to go to Format, and Fill, and change the color to light green.
• 03:52 In my workbooks, green means go.
• 03:54 If it's green, you can put data in.
• 03:56 I'm also going to go to the Protection tab, and
• 03:58 I'm going to unlock these particular cells.
• 04:00 I'm going to say, OK, and OK.
• 04:03 You'll notice that all of them change to green immediately.
• 04:06 And if I go and right click, and go to Format Cells on this one, and
• 04:09 go to the Protection tab, you'll notice that it is now unlocked as well.
• 04:13 And this is where that pre-setup and thinking about things in advance makes it
• 04:17 very easy to deploy consistent changes throughout your entire workbook very,
• 04:22 very quickly.

Lesson notes are only available for subscribers.