Creating and Modifying Custom Styles

It's very easy to apply an existing style to cells, but the choices you have in terms of different styles is somewhat limited.  Fortunately you can create your own custom styles.

Creating a Style in Excel 2007 and 2010

There are two main ways of creating a style in Excel 2007 and 2010: you can either format a cell first and then create a style based on that cell; or you can create a style and set all the formatting options using a dialog box.

To create a style based on a formatted cell:

1. Select a cell and apply all of the formatting options you want to be saved in the style.

It's a bit gaudy but will do as an example. You don't need to type text into the cell, but it helps you to see if you have applied the font formatting that you intended.

2. Select the cell you have formatted and from the ribbon select: Home -> Cell Styles -> New Cell Style...

If your styles list is expanded like this one you'll need to click the drop arrow at the bottom right corner.

3. Type in a name for the style and then click OK.

Try to make your style names descriptive.

4. Your style will now be available in the list.

Custom styles will appear at the top of the list of styles.

To create a style without formatting a cell first:

1. From the ribbon select: Home -> Cell Styles -> New Cell Style...
2. Type in a name for the style and then click Format...

Click the Format... button to display the Format Cells dialog box.

3. Using the dialog box, apply all of the formatting options you want to be saved in the style and then click OK.

Use the different tabs to change different parts of the cell format.

4. Click OK once more to create your style.

Creating a Style in Excel 2003

In Excel 2003, the way you create a style depends on whether or not you have added the Styles list to a toolbar.  To create a style in Excel 2003:

1. Select a cell and apply all of the formatting options you want to be saved in the style.
2. Select the cell you have just formatted and  press ALT +  '

If you don't have the Style list on a toolbar you will see the Style dialog box.	If you do have the Style list on a toolbar you will see if becomes activated.

3. Type in a new name for your style and click OK if you're in the Style dialog box, or just press Enter if you're using the Style list on a toolbar.

Your new style will be added to the list of styles.

What can be Included in a Style?

One of the great things about a style is that it can save information about any settings you've applied to a cell using the Format Cells dialog box.  So that includes everything from the font size to the cell's protection settings

Anything you change on any tab of this dialog box can be saved in a style. This includes complex custom number formats, which can be a pain to reproduce!

Choosing What's Included in a Style

When you create (or modify) a style you can choose which elements of the cell's format you want to include in the style.

Here we've unchecked the Border and Fill elements of the style.  This means that when we apply this style to another cell it won't change that cell's border or fill colour settings.

Modifying a Style

If you decide that you need to change something about a style after you've created it, you can!

To modify a style in Excel 2007 or 2010:

1. From the ribbon select: Home -> Cell Styles
2. Find the style you want to modify, right-click on it and choose Modify...

Right-click on the style and choose Modify...

3. On the dialog box, click the Format button to open the Format Cells dialog box.  Make any formatting changes that you want to make and then click OK and then OK again.

To modify a style in Excel 2003:

1. From the menu select: Format -> Style...
2. On the dialog box, click the Modify... button to open the Format Cells dialog box.

Click this button to edit your style.

3. Make any formatting changes you want to make and then click OK twice to update your style.

When you've finished modifying your style you'll see that any cells that already use the style you've changed will automatically be updated to include your changes.  It's a great way to change the formatting of a large range of cells in one go.

Merging Styles

One slightly disappointing things about styles is that they only exist in the workbook that you create them in.  You can get around this by merging styles from one workbook to another.

To merge styles:

1. Open the workbook that contains the styles you want to use.
2. Open or create a workbook that you want to copy the styles to and make sure that you have this file selected before the next step.
3. In Excel 2007 or 2010, from the ribbon select: Home -> Cell Styles _> Merge Styles...
4. In Excel 2003, from the menu select: Format -> Style... and on the dialog box, click the Merge... button.
5. On the dialog box which appears, select the name of the workbook that contains the styles you want to copy and then click OK.
6. On the next dialog box, click Yes if you want to replace any styles that have the same name (such as the Normal style), or click No if you only want to copy the unique styles from the other workbook.

You should now have access to all of the styles from your other workbook.  It's annoying to have to do this each time you create a new file - a much better solution would be to create a template workbook which contains all the styles you're going to use regularly, but that's a subject for a future blog!