Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Styles in Excel > How Styles Work
Deze pagina in het Nederlands

Styles in Excel

How styles work

A 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.

 


 


Comments

Showing last 8 comments of 35 in total (Show All Comments):

 


Comment by: Annie (1/12/2015 5:54:58 PM)

On the same type of issue that Charlie mentioned - my workbooks are creating HUNDREDS of new styles of formatting that I'm not creating myself. These are huge workbooks used as a carry forward each month for tax return data - how can I keep it from creating these new styles and how can I delete them from an existing file without having to go in and delete one by one....it has created a problem that is corrupting my files beyond repair and I'm losing material data :(

Any help would be greatly appreciated :)

Thanks!

 


Comment by: Jan Karel Pieterse (1/13/2015 10:12:06 AM)

Hi Annie,

Styles are created mostly when you copy data from one workbook and just paste it into another including formatting. So avoiding the buildup of styles is relatively straightforward: do not include formatting when pasting but use "Values and number formats", or "Formulas and Number formats".

 


Comment by: Val Gaskill (3/17/2015 5:11:06 PM)

At one point, my cell styles showed up in the styles group without having to click the drop-down to get them. Then they stopped. I'm using 2013. Can I change a setting, so a group of styles is always showing on the ribbon?

 


Comment by: Jan Karel Pieterse (3/17/2015 6:17:12 PM)

Hi Val,

I'm afraid there is not much you can do to affect what styles are directly in view on the ribbon.

 


Comment by: BETH (3/20/2015 7:21:10 PM)

I had the same problem and the way I restored styles is to copy workbook into new workbook and paste as xml spreadsheet to keep format of original.

 


Comment by: Raghavi (6/21/2015 10:22:10 AM)

How can I style my galleries step by step?????

 


Comment by: Brent Dixon (5/6/2016 10:21:47 PM)

Hi,

I want to use a format style as part of a pick list. The standard behavior of a pick list is to only bring in the value, not the formatting. I can use the conditional formatting rules to very tediously tell it which format to use with each individual value on each pick list, but I'm hoping there is a way using named styles or just using the formats of the cells in the pick list.

I found the same question on another forum and the only answers offered were different forms of the tedious manual approach above - but the question had been accessed 124,000 times so apparently I'm not the only one looking for an easier answer.

Thanks!

 


Comment by: Jan Karel Pieterse (5/11/2016 4:01:48 PM)

Hi Brent,

You could create styles for each item in the pick list which have the same names as the items. Then use a little bit of code to update the style. This goes in the code module of the worksheet in question. Assuming we only want the behaviour in cells A1:A5.

Right-click the sheet-tab and choose View code. Then paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A1:A5")) Is Nothing Then Exit Sub
    'in case the style does not exist
    On Error Resume Next
    Target.Style = Target.Value
End Sub

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].