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

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


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

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
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.




All comments about this page:

Comment by: Mahmood Anwar (7-3-2010 21:08:30)

I had 88 data and I applied style # 11. After that I added more clients and data. Now I want to combine both so that I can sort them. It does not allow me to delete the first syle and restyle or merge the top one to the bottom no style data. Thanks,


Comment by: Jan Karel Pieterse (7-3-2010 23:20:27)

Hi Mahmood,

Are you sure the new lines have become part of your table?
You can try by converting the table to a range and then converting the range back to a table.


Comment by: arun (14-7-2010 16:10:26)

I am working styles in excel.Some styles are getting added to the work book like 20%-Accent1,20%-Accent2.

I never added these styles in my work book.Is there any way we can check unused styles.So i can remove the styles.


Comment by: Jan Karel Pieterse (15-7-2010 04:56:47)

Hi Uran,

Those Accent styles are built-in styles as of Excel 2007, My guess is you cannot remove them.


Comment by: Ivy Wong (12-10-2012 14:34:49)

I would like to perform "format as table" in Excel 2003. Someone told me that i need to do it in the Style Group. However, I can't find that helpful? Would you please kindly help?


Comment by: Jan Karel Pieterse (12-10-2012 15:10:44)

Hi Ivy,

Like this article states, this option was made available in Excel 2007. However, Excel 2003 has the option unde rthe Data menu: Data, List, Create List.


Comment by: Ashwini (26-6-2013 13:46:00)

How can i see hidden styles again in Excel 2010.


Comment by: Jan Karel Pieterse (26-6-2013 15:52:36)

Hi Ashwini,

As far as I know, styles cannot be hidden so I'm afraid they are simply missing from the file in question.

If you copy cells from another excel file which does contain those styles into your file, the styles are copied as well.


- Open both files
- On the file with the styles, create a couple of cells that are formatted using the styles you need.
- Copy those cells and paste them into the file which does not have the styles.


Comment by: Joanne McClelland (12-7-2013 19:13:16)

Is there a way to search for a particular cell style? Thank you.


Comment by: Jan Karel Pieterse (13-7-2013 19:12:49)

Hi Joanne,

You can do a find and select a format to look for that matches the style in question perhaps?


Comment by: Jason (18-10-2013 17:44:19)

Thanks for the explanation, I think I get it - BUT
how do I change a style so that every cell where that style applies is changed. For example, I have a sheet with lots of currency cells formatted with a style to show as US Dollars but now I want all those, throughout the spreadsheet to show as GB Pounds, how do I do that please?




Comment by: Jan Karel Pieterse (19-10-2013 16:20:52)

Hi Jason,

You can right-click the syle in question and select Modify. Then change its number formatting to show GBP instead of USD.


Comment by: Jason (21-10-2013 09:03:52)

Thanks - I was trying this but I think the base spreadsheet I'm working with has some gaps in the use of styles - I'll clean it up first and ensure the cells I want to change are indeed covered by the styles.


Comment by: Sam (17-1-2014 00:54:50)

Is there a way to delete style formats quickly or in a batch? This is for Excel 2010.


Comment by: Jan Karel Pieterse (17-1-2014 11:06:03)

Hi Sam,

This little macro removes all custom styles:

Note that it may wreck your workbook's formatting, so make sure you have a backup copy!

Sub RemoveCustomStyles()
    Dim lCt As Long
    'count backwards to avoid VBA getting confused due to the delete
    For lCt = ActiveWorkbook.Styles.Count To 1 Step -1
        If ActiveWorkbook.Styles(lCt).BuiltIn = False Then
        End If
End Sub


Comment by: Delise Matheny (14-2-2014 21:48:42)

Is there a way to delete style formats quickly or in a batch? I see there was a response for a macro to remove all custom styles, but I just need to delete some of the custom files (which there are several to delete). Right clicking and deleting them one at a time takes forever. Is there any other way to delete a group of styles at a time and delete?


Comment by: Jan Karel Pieterse (17-2-2014 06:23:39)

Hi Delise,

Check out this blog post:


Comment by: vickie (5-3-2014 16:36:43)

on my screen all the styles are blocked out and I can not modity any of those?


Comment by: Jan Karel Pieterse (6-3-2014 17:24:18)

Hi Vickie,

Perhaps the workbook is protected? Also, if any worksheet is protected, editing styles is not allowed.


Comment by: Melissa (14-3-2014 17:50:09)

How can I restore styles that I have accidentally deleted?


Comment by: Jan Karel Pieterse (15-3-2014 17:44:59)

Hi Melissa,

I'm afraid you can't, you'll have to turn to a previous copy of the file.


Comment by: Patti Thomas (6-5-2014 17:45:27)

My cell styles have been "rearranged". They are in a different order and in different groups than they were before. Two of the preset styles have moved into the custom group, and one of my custom styles has moved into Good, Bad & Neutral.

Is there a way to prevent this from happening?

Is there a way to manually rearrange the styles? I'd like to put them back where they belong.


Comment by: Jan Karel Pieterse (8-5-2014 07:19:09)

Hi Patty,

Hmm, they shouldn't move at all, you have no control over their position. Are you sure the ones appearing in Custom are not really customn ones? If you right-click one of them and select Modify, what is the exact name in the top box of the dialog?


Comment by: jenny (28-11-2014 02:52:58)

In excel what is a style sheet ?


Comment by: Jan Karel Pieterse (28-11-2014 13:25:22)

Hi Jenny,

There is no such thing per se, it is just a normal worksheet that I often insert that contains a list of styles and shows an example of how they look when applied. That is all.


Comment by: Charlie (10-12-2014 04:15:37)


The default cell styles have all converted to 20% styles, ie good, bad, normal etc are gone and I just have a list of pastel coloured cells with "20% accent" as the names. If I cut an paste any of these cells into a different book, the new book converts all the normal styles to this 20% accent. How do I dswitch it back to the normal default cell styles?




Comment by: Jan Karel Pieterse (10-12-2014 09:21:54)

Hi Charlie,

I think the only way is to copy and then pastespecial formulas, do NOT paste any formatting.


Comment by: Annie (12-1-2015 17:54:58)

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

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)

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)

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)

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)

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


Comment by: Brent Dixon (6-5-2016 22:21:47)


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)

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:

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 accoring to our Privacy Policy.