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

Tips for using styles

Managing styles

If you like to keep an overview of what styles are available in your file I'd advise you to add a special worksheet to your workbook. Put the names of the styles in column A and an example output in column B:

Table with styles in a worksheet

Table with styles in a worksheet

If you need to adjust a style, select the cell in column B and adjust the style settings from there.

Creating a new style based on an existing one is easy now: Just copy the applicable row and insert it anywhere in the table. Select the cell in column B of the newly inserted row and choose "Format", "Style...". Enter the name of the new style and click Add. Then click Modify to change the style details. Don't forget to update the name in column A too.

Using styles

I advise you to use styles as strictly as you can. Avoid modifying one formatting element of a cell with a style. Instead, consider if it is worth the effort to add a new style. If for instance you have a style for percentage with 2 decimal places and you have a cell which requires three, then add a style for that purpose. You can thank me later.

Adapting this method will likely trigger you to think about what cell styles your document will need. By doing this your Excel models will gradually improve. You'll gain in consistency and loose the ad-hoc (often messy) formatting jungle.

Use functional sets of styles

By looking at your Excel model you will likely be able to categorise your workbook cells into various categories:

Consider creating styles for each of these cell functions, each (e.g.) having its own fill color. Don't forget to make decisions on whether or not a style's locked property needs to be on or off. If you use a system like this, it becomes very easy for you to maintain your file. Imagine how easy it now becomes to change a cell from an input to an output cell: you change its style. Done.



All comments about this page:

Comment by: Bob Phillips (19-2-2009 17:23:13) deeplink to this comment

Great idea for my style manager, build that worksheet!

Comment by: Elardus Mare (16-3-2010 20:42:09) deeplink to this comment

I agree on the use of styles instead of individual formatting of cells. I've alwasy used it to great effect.
One thing I could never underastand is why MS do not allow for multiple deletion of styles at once. I have several spreadsheets with data sourced from many other files. Unfortunately every time data is copied into these summary files, it also means that the summary files sometimes end up with zillions of sometimes very unlogical styles that just clutter up the styles box or even worse, their stupid form atting get5s imposed on other wheer I dont want it. Deletion of those unwanted styles certainly solve the problems, but it is very time-consuming deleting them one-by-one.

Do you have a tip for getting rid of many styles at once so I can only keep the ones I want?

I have to spend

Comment by: Jan Karel Pieterse (16-3-2010 23:26:09) deeplink to this comment

Hi Elardus,

One way is to round-trip the file through html (that is: save as filetype html and then open the html file and save as normal Excel file). That rids the file of unused styles.

If you have Excel 2007, saving to the new Excel 2007 xlsx or xlsm format does so too (the html route is less complete there, you might loose some stuff).

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.