Using Styles in Microsoft Excel
Part one 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.

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

Posted by Andrew Gould on 24 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using Styles in Microsoft Excel

Formatting worksheets can be one of the most tedious tasks in Excel, particularly if you're forced to replicate your organisation's standard colours and font schemes.  Using styles is a fantastic way to save masses of time when formatting a worksheet as you can apply a whole range of formatting options to cells with a couple of clicks of the mouse.

A style is simply a collection of formatting options, such as font size, cell colour and border style settings, saved with a sensible name and which can be applied to cells quickly.

You can see a video tutorial covering how to use styles in Excel workbooks by clicking here.

Viewing Existing Styles

Every new workbook that you create contains a number of existing styles which you might find useful.  Seeing which styles you have available depends on which version of Excel you are using.

  • In Excel 2007 and 2010, from the ribbon choose: Home -> Cell Styles
Minimised cell style Expanded cell style
The option might be minimised like this if your screen is quite narrow... ...or it might be expanded like this if your screen is fairly wide.
  • In Excel 2003, from the menu choose: Format -> Styles...
Styles dialog

You can also press ALT +  '   (apostrophe) to display this dialog box.  You can see a list of styles in the drop down list highlighted here.

 

Notice that the Normal style is applied to cells by default.  We'll use this to our advantage later.

Adding the Style List to a Toolbar in Excel 2003

To make life easier in Excel 2003 you can add the style list to an existing toolbar to avoid having to open a dialog box each time.  The basic process is:

  1. From the menu select: Tools -> Customize...
  2. On the dialog box, select the Commands tab.
  3. From the list of categories on the left, select Format.
Customize dialog

From the list of categories select Format, and then look for the Style: drop list on the right hand side.

 
  1. Click and drag the Style: drop down list from the dialog box and drop it onto an existing toolbar.
Adding Styles list to toolbar

Look for the vertical black line which shows where the drop down list will be inserted.

 
  1. Close the dialog box and you should now have an easy way to see the available styles in the workbook.
Styles list on toolbar

You now have an easy way to select styles in Excel 2003.

 

Applying a Style to Cells

To apply an existing style you simply need to select a cell or cells and then select one of the styles from the list.

  1. Select the cells you want to apply the style to.
Selecting cells for style

Here we're going to apply a heading style to the selected cells.

 
  1. From the ribbon or toolbar select the style you want to apply to the selected cells.
Applying a style

Here we're applying the Heading 2 style in Excel 2010.

 
  1. The selected cells should now be formatted according to the options contained within the style.
Applied style

This style makes the font larger, blue and bold, and adds a blue bottom border to each cell.

 

If you have the Style list on a toolbar in Excel 2003 you can press ALT +  '  to access it, and then type in the first letter of the style name you want to apply, or use the arrow keys to scroll through the list.  When you've highlighted the style you want, press Enter to apply it to the selected cell.

Removing Styles

You can't technically remove a style from a cell - every cell uses a style.  Remember that we earlier pointed out that each cell uses the Normal style by default?  To remove the formatting applied by a style you can simply set the cells back to using the Normal style.

Applying the Normal style

Apply the Normal style to remove the formatting applied by other styles.

 

What's Next?

Using existing styles is fine as long as you're happy with the limited choices that you have.  It's more likely that you'll want to create your own custom styles, perhaps to fit in with a corporate colour scheme.  Read the next part of this blog series to find out how to do this.

  1. Using Styles in Microsoft Excel (this blog)
  2. Creating and Modifying Custom Styles
This blog has 0 threads Add post