Locked lesson.
About this lesson
Learn how to apply built-in number styles and create your own custom styles packages.
Exercise files
Download this lesson’s related exercise files.
Styles.xlsx19.7 KB Styles - Completed.xlsx
19.9 KB
Quick reference
Topic
Styles.
Description
Applying built-in number styles and creating our own custom styles packages.
Where/when to use the technique
Styles have many benefits, but probably the biggest is the ability to easily update the look and feel over our spreadsheets with minimal effort.
Instructions
Applying built-in styles
- Select D4:D20, go to the Home tab
- Apply the Currency style by clicking the $ sign on the Number group
- Select A3:D3, got to the Home tab --> Cell Styles --> Heading 2
- These styles will be the same through the entire workbook
Creating custom styles
- Go to the Home tab --> Cell Styles and click New Cell Style
- Enter “DE_Values” for the Style Name
- Uncheck Alignment, Font and Border, as we don’t want to control those (but we do the rest)
- Click Format
- Set the Number to Accounting with no symbols
- Set the Fill tab to a light green background
- On the Protection tab, uncheck the “Locked” checkbox and click OK
- Select D4:D20, go to the Home tab --> Cell Styles --> DE_Values
- Notice that your cells turn green. You can now educate your users to only enter data in the green cells
Updating built-in styles
- Assume head office asks you to turn the green to orange
- Go to the Home tab --> Cell Styles --> right click DE_Values and choose Modify
- Click Format, change the fill and click OK to return to the worksheet
- Every single cell in the workbook that has this style applied will update immediately to reflect the changes.
- 00:00 Now in this video, we're going to look at styles in Excel.
- 00:08 And this is kind of a piece that a lot of people don't use.
- 00:12 But what you'll find is that if you're developing spreadsheets and you actually
- 00:14 take the time to apply styles, it doesn't necessarily help you design a better
- 00:19 workbook any faster, but what it does do is if somebody comes back and
- 00:23 wants you to make changes, it can really help you do that quite quickly.
- 00:26 So I'll show you first off, we've got some built-in styles in the gallery.
- 00:30 Let's say that we wanted to
- 00:32 change the header of this to look a little bit more bold and whatnot.
- 00:37 Over here on the home tab, we have the cell styles box.
- 00:40 And you'll notice on here that there's a few different titles and headings that
- 00:43 are prebuilt, as well as some accents and some data and model stuff as well.
- 00:47 I'm going go click on title,
- 00:49 and you'll notice that this changes into a new title style.
- 00:52 So that's kinda neat, because I can use the same kind of theme everywhere to
- 00:55 make sure it's consistent through the document if I've got multiple pages.
- 01:00 What I would like to do, though, is I'd like to set up a custom style.
- 01:03 This is where things get kind of interesting.
- 01:06 So I'm gonna grab this block right here of cells, and what I'm gonna do is I'm
- 01:10 gonna say, okay, let's format this the way that I'd like to see it.
- 01:15 So maybe I'll go and put a comma style on this, and
- 01:18 I'll just drop the decimals back.
- 01:21 I'm gonna go to Cell Styles and I'm gonna say New Cell Style.
- 01:25 And what I'm gonna do is I'm gonna call the cell style DE_Values.
- 01:30 So this is for data entry for values.
- 01:33 And you can see that I have the option to choose different pieces that I would like
- 01:36 included in the style package.
- 01:39 It says, would you like to include the border?
- 01:40 And it's already unchecked it because it recognizes that my bottom cell
- 01:44 has a border and the rest don't.
- 01:45 So I say well, that's cool.
- 01:46 I don't wanna deal with that then.
- 01:48 The font, I'm not really worried about it, so I'm gonna uncheck it.
- 01:51 The alignment, I'm not really worried about that, either.
- 01:53 Although I might wanna put that in.
- 01:55 The number format, absolutely.
- 01:57 I wanna make sure it has this number format.
- 01:59 The fill is shaded, sure.
- 02:01 Protection locked, no problem.
- 02:03 If I want to modify any of these things, I just click on Format and
- 02:06 I can actually go in there and take a look at the different pieces, and
- 02:09 work here all the tabs to get everything set up exactly the way I want.
- 02:13 For right now, we'll say OK.
- 02:14 I'll say OK.
- 02:17 Now the trick with this though is that once you've done this,
- 02:20 you actually have to go back and you actually have to tell Excel that
- 02:23 I really want to apply my custom style to these cells.
- 02:27 Right now you can notice down at the bottom, it's still set with the number
- 02:30 format of comma, it hasn't picked up my custom style.
- 02:33 So I'm gonna click DE_Values on this.
- 02:37 Why is this important?
- 02:38 Well there's a couple reasons.
- 02:39 Let's go and select this one, and say DE_Values.
- 02:43 And you'll notice that it changes the number format as well.
- 02:45 I'm gonna scroll down my worksheet a little bit here and
- 02:48 I'm gonna grab some more of these too.
- 02:50 And you'd be forgiven for
- 02:52 asking at this point in time why would you bother doing this?
- 02:55 This just seems like really kind of copying a bunch of different formatting so
- 02:58 why change this at all?
- 03:00 What's the point if really all we need to
- 03:02 use is the Format Painter to make this happen?
- 03:05 And as I said before,
- 03:07 if you're just trying to design your spreadsheet the first time,
- 03:10 it probably isn't gonna be something that you're really thinking about.
- 03:13 But what you wanna think about is what's gonna happen in the future.
- 03:16 If I'm building another worksheet later,
- 03:18 I don't have to remember what the style packages that I've used,
- 03:20 cuz this is gonna inherit everything that I've actually claimed, or
- 03:23 everything that I've assigned to it, from font colors to font sizes,
- 03:27 to borders and all that kind of jazz if I actually configured those.
- 03:30 But where things get really useful is where you run into a situation like this.
- 03:36 And I say you know, my boss comes in he says,
- 03:38 hey listen, I would like you to modify the input cells here,
- 03:43 because I don't really like a dark or a light blue for the color.
- 03:46 In my world, and
- 03:48 I'll tell you in my world, all my data input cells are always green.
- 03:51 Green means go, and I tell people, if it's not green, don't put any data in there,
- 03:55 because it's probably formula.
- 03:56 But if it's green, it's your license to do with what you like.
- 03:58 So I'm gonna say, well this is kinda interesting,
- 04:00 let's change the fill on this now.
- 04:02 So we'll go to format, we'll go to fill, and
- 04:05 I'm gonna change this background color to a light green here.
- 04:09 And I'm going to say OK.
- 04:11 I'm gonna say OK.
- 04:14 And you can see immediately that all of the data entry cells
- 04:18 that are using that cell style have updated.
- 04:22 This is the benefit of styles.
- 04:24 When your marketing department comes in and says, hey we changed the colors on our
- 04:27 entire organization and we need you to use your proper color schemes.
- 04:31 One click, well, couple of clicks, but one package you have to change and boom,
- 04:34 it updates throughout your entire worksheet.
- 04:36 You know everything's consistent.
- 04:38 That's where the benefit of styles really, really comes in.
- 04:42 As compared to, if you'd actually set all your formats independently, you'd now be
- 04:46 doing a whole lot of copying and pasting with the format painter trying to get
- 04:49 those colors rolled through the entire document, wherever they could be found.
Lesson notes are only available for subscribers.