Locked lesson.

Upgrade

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.xlsx
11 KB Styles - Completed.xlsx
10.6 KB Styles - Extra Practice.xlsx
19.7 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:03 Users will spend a ton of time formatting their work before they actually send it off to somebody else to consume.
  • 00:09 We do this because we want to make sure the data looks pretty because it's more engaging when that actually happens.
  • 00:16 Now one of the things a lot of users don't know though is that we can actually
  • 00:20 spend a ton of time formatting but we can also do a lot of formatting very quickly when we actually work with Styles.
  • 00:26 Styles you've probably worked with a lot and didn't even know. All these items
  • 00:31 here in the Number Styles area are actually a Style. That's a prepackaged
  • 00:36 formatting group that's consistent across the application.
  • 00:39 So if I were to go and select my Medical Payments and I were to go and apply say a $ Style I know as soon as I click that button on
  • 00:47 any cell in the workbook that they will all look identical. That its going to have two decimal places, have commas in the
  • 00:52 thousands, it will have a $ left aligned on the cell. No matter where in the workbook it is
  • 00:57 because I've used that style. We can also do a similar thing with our table header. If I go and collect cells A3 through D3
  • 01:07 and I go up to Cell Styles on the Home tab
  • 01:10 I actually get a whole bunch of different options here. I'm going to choose Heading 2 and you can see the live preview goes on these things as
  • 01:16 well on the worksheet. Heading 2 looks pretty nice to me.
  • 01:20 I know again no matter what worksheet or what cell I apply this to its going to look absolutely identical.
  • 01:27 Now the other thing that we can do is we can actually also set up our own Custom Styles and Custom Styles are really great because they
  • 01:34 allow us to deploy really quick updates to our workbook.
  • 01:39 So I'll give you a Custom Style that I tend to use a lot. I do this for data entry. I'll go into Cell Styles and I'll go to create a new cell style and I
  • 01:48 get options here. I'm going to call this one DE which stands for Data Entry, underscore values.
  • 01:55 So I know that every value that I'm looking at for my data entry is going to look exactly the same.
  • 02:01 I'm going to use a number format
  • 02:03 I won't bother changing the Alignment so whatever is formatted on this cell is fine.
  • 02:07 Whatever is formatted for the font is fine. I'm not going to worry about the borders either.
  • 02:12 I'm not going to set any of those but I am going to change the fill and I am going to change the protection status of my data entry cells.
  • 02:20 So there's the name. These are the items that I want to control and now I click format.
  • 02:26 So on the Number tab I'm going to say that yes I want this to be Accounting but I don't want to have a dollar sign.
  • 02:32 And I'm going to, well we'll leave the decimal places, sure.
  • 02:36 I'm going to go to fill and select a nice green, light green color.
  • 02:40 To me light green, I always use this I say green means go, to my users they know if it has a green cell in my worksheet
  • 02:46 that's where they can enter data and if it's not green they don't. Plain and simple.
  • 02:50 The other thing I'm going do is I'm going to go and uncheck the Locked box on the Protection and that way if I ever protected the worksheet all
  • 02:57 my data entry cells would actually be available to be used.
  • 03:02 So now when I say OK
  • 03:04 and I say OK
  • 03:06 Nothing happens. Which is a little bit concerning
  • 03:09 but if I go back and select the data again
  • 03:12 and go to my Cell Styles to see that I now have a custom cell style and when I click on that
  • 03:17 it's actually made the change for me.
  • 03:19 Why is this so important? Well it's because of this. If I use this throughout my entire solution so every time I've got a
  • 03:28 data entry cell I know exactly what it is. I can educate my users to that fact then
  • 03:32 I ship this thing off and the client sends it back and says this is great but
  • 03:36 you know what? Our company colours for data entry are orange and we want to see you change that can you make it orange?
  • 03:42 If you don't use styles what ends up happening is you have to go through the entire workbook
  • 03:47 looking for every cell that's green and updating it to try and get it exactly right.
  • 03:52 But if you use styles
  • 03:54 we go into cell style we right click on our data entry values
  • 03:57 modify it.
  • 03:59 We go into format
  • 04:01 I go back to the fill tab and you said you wanted it in orange here we go. We say OK, OK and
  • 04:07 the entire workbook, every worksheet, every cell that's formatted with that style
  • 04:14 has just updated right away. Styles are awesome for development
  • 04:18 you definitely want to use them if you're deploying a solution to someone else.

Lesson notes are only available for subscribers.

Gift this course