Focus video player for keyboard shortcuts
Auto
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Microsoft Excel 2019 - Advanced course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

About this lesson

Learn how to apply built-in number styles and create your own custom styles packages.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Styles
18.1 KB Styles - Completed
18.3 KB

Quick reference

Topic

Applying built-in number styles and creating our own custom styles packages.

When to use

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
Login to download
  • 00:04 When we talk about styles in Excel, what we're referring to is essentially
  • 00:08 a prepackaged collection of formatting commands.
  • 00:12 And the biggest benefit from styles is the ability to be able to, very quickly, push
  • 00:17 changes throughout an entire workbook, if we've actually used styles for our work.
  • 00:23 Styles come in a lot of different format and shapes.
  • 00:25 One of the styles that you'll see and
  • 00:27 recognize is something like the dollar sign up here, accounting number format.
  • 00:31 And if I click on it, anytime I make a modification to this particular style now,
  • 00:35 it'll roll through every cell in the workbook that uses that style.
  • 00:39 Likewise, if I wanted to go and set up the top row here and
  • 00:44 I wanted to use a specific built-in style for my cell styles.
  • 00:47 Maybe I want to choose heading one.
  • 00:48 It would actually update this right away and it would make it consistent with
  • 00:53 every other cell in the workbook that uses that same style.
  • 00:57 Beyond this though we can actually create our own style packages.
  • 01:01 So, to do that, what I'm gonna do is I'm gonna select a set of cells here.
  • 01:06 And I'm gonna go to Cell Styles, and we're gonna say New Cell Style.
  • 01:11 And I'm gonna give this a name like DE, for data entry, underscore, and
  • 01:16 we'll just call this one, how about values.
  • 01:18 DE_Values since we have values here.
  • 01:21 Now, you'll notice that one of the things is unchecked here.
  • 01:24 I'm going to say you know what, I don't really care about Borders, so
  • 01:27 we'll leave that unchecked.
  • 01:29 The number format I definitely want to control.
  • 01:31 The Alignment, I'm not really all that fussed about that.
  • 01:34 The Font, I'm not really all that worried about the Font either, so
  • 01:37 we'll uncheck this guy here.
  • 01:39 The Fill, I definitely wanna control, and the Protection Status I do as well.
  • 01:43 So I'll say, Format.
  • 01:46 So we'll go to our number format.
  • 01:48 And I'll say, you know, I really want to see this in accounting with
  • 01:51 no dollar signs, no decimals on this at all.
  • 01:55 That'll give me numbers that have commas in them, unlike what I'm seeing here.
  • 01:59 I'm also gonna to go to the fill tab and we'll say, you know what?
  • 02:02 I'd like to see my numbers in a light green fill.
  • 02:05 And on the protection tab, I would like to unlock them so
  • 02:08 that when I actually protect the workbook, they will work.
  • 02:12 At this point we'll say OK, and we'll say OK.
  • 02:15 And there's no changes.
  • 02:18 In order to do this, we actually create our style first, and
  • 02:20 then we have to apply it.
  • 02:22 So we'll go to Cell Styles Gallery, and
  • 02:24 here's my custom format that I just created, DE_Values.
  • 02:27 When I click on it, you'll notice that it changes to green.
  • 02:30 And I can now go and apply this style
  • 02:33 to the other cells on the workbook that I'm using for data entry.
  • 02:37 So, I'll just select a couple of blocks here by holding down my Ctrl key,
  • 02:41 we'll go to DE values, and we'll set those up.
  • 02:46 Now, that's all wonderful you say, but we could have done that with formatting.
  • 02:51 Well, we absolutely could, but check this out now.
  • 02:54 Now I get a request from somebody to update something.
  • 02:58 I can right click, and I can modify my style.
  • 03:02 And I'll go into Format,
  • 03:03 and I'll go to Fill, because somebody prefers to see this in light blue.
  • 03:08 We'll change it here, and say OK, and say OK, and bang,
  • 03:14 every cell in the workbook that uses the style that I created is updated.
  • 03:20 Just like that.
  • 03:21 This is really, really useful for quick reuse of things and making modifications
  • 03:26 in a hurry on stuff, if you're actually prepared and set everything up in advance.
  • 03:31 I try to format all of my templates and work books using styles.
  • 03:35 Number one, to enforce consistency throughout the work book.
  • 03:38 But number two, to allow me very quickly make updates if I need to do that.
  • 03:42 So, this is one of those things that you definitely don't have to do but
  • 03:45 if you do discipline yourself and use it, you will find that it pays big dividends.
  • 03:49 Usually when you have the least amount of time to actually make
  • 03:51 a significant update that somebody requests.

Lesson notes are only available for subscribers.

Gift this course