Home Newsletter

Deze pagina in het NederlandsHome > Article index > Styles in Excel >

Styles in Excel

Pages in this article

  1. How Styles Work
  2. Creating Styles
  3. Applying Styles
  4. Deviate from a style
  5. Tips
  6. VBA Examples
  7. Conclusion

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

You access the style dialog from the Home Tab, Styles group, Cell Styles button:

Styles in the Excel 2010 ribbon
Excel enables you to access the styles by clicking the dropdown next to the styles gallery. The screen to create a new style looks like this (if you click the New Style option in the style gallery):

Opmaakprofielen venster voor Excel 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.




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


Comment by: Annie (12-1-2015 17:54:58) deeplink to this comment

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 :)


Comment by: Jan Karel Pieterse (13-1-2015 10:12:06) deeplink to this comment

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 (17-3-2015 17:11:06) deeplink to this comment

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 (17-3-2015 18:17:12) deeplink to this comment

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 (20-3-2015 19:21:10) deeplink to this comment

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 (21-6-2015 10:22:10) deeplink to this comment

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

Comment by: Brent Dixon (6-5-2016 22:21:47) deeplink to this comment


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.


Comment by: Jan Karel Pieterse (11-5-2016 16:01:48) deeplink to this comment

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, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website according to our Privacy Policy.