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.


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 > Applying Styles
Deze pagina in het Nederlands

Styles in Excel

Applying styles

There are two methods to apply a style to a cell.

The first one is by using the menu: "Format", "Style…". Select the style you need from the dropdown list and click OK. Or in Excel 2010 (2013, 2007): Home tab, Styles group, Expand the Cell Styles gallery and click a style.

In Excel 97-2003 you can save yourself some time (in future) by adding the Style dropdown to a toolbar, like this:

TIP: Adding the style dropdown to a toolbar.

The Customize dialog screen
The Customize dialog screen.
Drag the Style control to a toolbar and let go

Dragging the Style control to a toolbar and let go.

Applying a style to some cells now takes nothing more than picking the right one from your new style dropdown. If the format of any of the selected cells deviates from the selected style then Excel (Excel 97 - 2003) will ask you whether or not to update the style to match the formatting of the selected cells (Excel 2010 (2013, 2007) does not ask you this question!):

Confirm to Excel how it must apply the style

Confirm to Excel how it must apply the style

This dialog needs some explaining to understand what happens when you click a button.



All comments about this page:

Comment by: Alan Hutchins (6-2-2009 06:16:48)

I added a Style in the way that you suggested (Excel 2003), and then added the control to one of the toolbars. The newly created style is available for selection in the workbook from which it is created, but it is NOT available in any other workbook. Have I misunderstood something?

I was hoping that I could use this to create a few specific styles that would be available for me to select in any workbook.


Comment by: Jan Karel Pieterse (6-2-2009 06:59:33)

Hi Alan,

You have uncovered a small omission in the article. Styles are part of a workbook, not stored anywhere else.
So if you create a style, it stays with the workbook.

You can however copy a style from one workbook to another by simply copying a cell with that style to the other file.

Also, there is a Merge button on the style dialog that lets you merge the styles from another workbook into the one you have in front of you.


Comment by: John Pittman (3-6-2009 16:38:33)

Maybe I missed something but at the heading of this page you mention that "There are two methods to apply a style to a cell"
I would like to dynamically add "Good", Neutral", or "Bad" Styles to cells in a spreadsheet.
I can manage getting the content in cells to change, but would like to change the style as well.
Am I right in suggesting that this is not the second method?


Comment by: Jan Karel Pieterse (3-6-2009 23:40:59)

Hi John,

The text on this page isn't very clear on what the second method is. In fact I meant to write that the second method is by using the styles dropdown, which you can add to a toolbar.
I'm not sure if you can use conditional formatting to change the style in Excel 2007. If not, then I would just use a conditional format for your three situations and manually set up their formatting.


Comment by: Chris Ainley (23-10-2009 18:22:08)

Just above, you mentioned that things within the Cell Styles dropdown can be added to a toolbar. I assume that there's a way of making the 'good', 'bad' icons available on a toolbar, making them more accessible for interactive tagging of cells.

How does one get the Good, Bad, Neutral, etc designation icons onto the toolbar (so that I don't have to keep using the dropdown)?




Comment by: Jan Karel Pieterse (24-10-2009 01:08:30)

Hi Chris,

I'm afraid you can't add a specific style button to the QAT. But you can create small macros and add macro buttons to the QAT for this purpose.

For example:
Sub AddBadStyle()
Selection.Style = "Bad"
ENd Sub


Comment by: PROF. GOVARDHAN (7-12-2012 03:43:02)

It is not wise to use one method of addition of styles to quick access in MS Word some other method in EXCEL; it makes the application so much UNFRIENDLY to the users.


Comment by: Rohit (6-3-2014 20:49:19)


I am running a Macro to split a file with 57 tabs to 57 separate files but but the Macro is ran and the files are created, the col. colors (Style) in the new files changes from grey to black. Any help?



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.