This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Using Styles in Microsoft Excel Part two of a two-part series of blogs
Constantly formatting cells in the same way can be one of the most tedious and time-consuming tasks in Excel. This blog teaches you how to use styles so that you can quickly apply consistent formatting across a range of cells. Using Styles in Microsoft Excel Creating and Modifying Custom Styles (this blog)

Using Styles in Microsoft Excel

Part two of a two-part series of blogs

Constantly formatting cells in the same way can be one of the most tedious and time-consuming tasks in Excel. This blog teaches you how to use styles so that you can quickly apply consistent formatting across a range of cells.

Using Styles in Microsoft Excel
Creating and Modifying Custom Styles (this blog)

Posted by Andrew Gould on 24 February 2012

In this blog

Creating a Style in Excel
What can be Included in a Style?
Choosing What's Included in a Style
Modifying a Style
Merging Styles

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

There are two main ways of creating a style in Excel: 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:

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.

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.

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

Try to make your style names descriptive.

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:

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

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

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.

Click OK once more to create your style.

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:

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

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

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.

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.

You can also modify any of the existing styles in a workbook. If you do this with the Normal style you'll find that all of the cells in a new workbook will be changed - that's because every cell in a new workbook has the Normal style applied to it by default.

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:

Open the workbook that contains the styles you want to use.
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.
From the ribbon select: Home -> Cell Styles _> Merge Styles...
On the dialog box which appears, select the name of the workbook that contains the styles you want to copy and then click OK.
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!

Parts of this blog
Using Styles in Microsoft Excel Creating and Modifying Custom Styles (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

ALL BLOGS

EXCEL BLOGS