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.

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

All comments about this page:


Comment by: Mahmood Anwar (3/7/2010 9:08:30 PM)

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 (3/7/2010 11:20:27 PM)

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 (7/14/2010 4:10:26 PM)

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 (7/15/2010 4:56:47 AM)

Hi Uran,

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

 


Comment by: Ivy Wong (10/12/2012 2:34:49 PM)

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 (10/12/2012 3:10:44 PM)

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 (6/26/2013 1:46:00 PM)

How can i see hidden styles again in Excel 2010.

 


Comment by: Jan Karel Pieterse (6/26/2013 3:52:36 PM)

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.

So:

- 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 (7/12/2013 7:13:16 PM)

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

 


Comment by: Jan Karel Pieterse (7/13/2013 7:12:49 PM)

Hi Joanne,

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

 


Comment by: Jason (10/18/2013 5:44:19 PM)

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?

Thanks

J

 


Comment by: Jan Karel Pieterse (10/19/2013 4:20:52 PM)

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 (10/21/2013 9:03:52 AM)

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 (1/17/2014 12:54:50 AM)

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

 


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

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
            ActiveWorkbook.Styles(lCt).Delete
        End If
    Next
End Sub

 


Comment by: Delise Matheny (2/14/2014 9:48:42 PM)

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 (2/17/2014 6:23:39 AM)

Hi Delise,

Check out this blog post:

http://excelandaccess.wordpress.com/2013/01/14/delete-styles-in-excel/

 


Comment by: vickie (3/5/2014 4:36:43 PM)

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

 


Comment by: Jan Karel Pieterse (3/6/2014 5:24:18 PM)

Hi Vickie,

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

 


Comment by: Melissa (3/14/2014 5:50:09 PM)

How can I restore styles that I have accidentally deleted?

 


Comment by: Jan Karel Pieterse (3/15/2014 5:44:59 PM)

Hi Melissa,

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

 


Comment by: Patti Thomas (5/6/2014 5:45:27 PM)

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 (5/8/2014 7:19:09 AM)

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 (11/28/2014 2:52:58 AM)

In excel what is a style sheet ?
Please.

 


Comment by: Jan Karel Pieterse (11/28/2014 1:25:22 PM)

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 (12/10/2014 4:15:37 AM)

Hi,

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?

Thanks

Charlie

 


Comment by: Jan Karel Pieterse (12/10/2014 9:21:54 AM)

Hi Charlie,

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

 


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