How to Work with Cell Styles in Excel [Solved]

How to Work with Cell Styles in Excel

How styles work

Excel LogoA style is just a set of cell formatting settings which has been given a name. All cells to which a style has been applied look the same formatting-wise. When you change a part of a style, all cells to which that style has been applied change their formatting accordingly.

Use of styles takes some getting accustomed to, but may bring you great advantage. Imagine showing your nicely formatted sheet to your boss. Then your boss asks you if you could please change all input cells to having a light-yellow background fill, instead of a dark yellow one. For a large model, this may imply a huge amount of work. Would you have used styles, then it would have been a matter of seconds.

Styles are in fact an addition. Cell formatting is the sum of the applied style and all modifications to individual formatting elements on top of that style. What parts of the formatting options are included in a style is determined during the definition of the style (See screenshot below).

Access the style dialog by choosing Format, Style… from Excel 97-2003’s menu:

Excel 97-2003 Format menu
Excel 97-2003: Styles can be accessed from the Format menu

 In Excel 2010 (and 2013 and 2007) you may access the style dialog from the Home Tab, Styles group, Cell Styles button:

Styles in the Excel 2010 ribbon
Excel 2010 (and 2013 and 2007): Styles are accessed from the Home tab, Styles group

The following dialog comes up in Excel 2003 when you click the style… button:

Dialoogvenster opmaakprofiel

The Styles dialog screen for Excel 97-2003

Excel 2013 (and 2010 and 2007) enable you to access the styles by clicking the dropdown next to the styles gallery. Excel 2013 (and 2013 and 2007) has a slightly different screen to create a new style however (if you click the New Style option in the style gallery):

Opmaakprofielen venster voor Excel 2007

Style dialog for Excel 2013/2010/2007

When you apply a style to a cell followed by another style, the end result will be an addition of the selected parts of both styles. What the end result of such an addition of styles will be, depends on which elements of both styles have been selected as being part of the style (this will be discussed later). Theoretically, this would have enabled us to use cascading styles, but unfortunately Excel does not keep a record of the order of applied styles. Only the last style is remembered. Also, styles can not be derived from other styles whilst maintaining a link to the parent style. Changes to the “original” style are not reflected in the “child” styles.