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

Styles in Excel

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.


 


Comments

All comments about this page:


Comment by: Bob Phillips (2/19/2009 5:23:13 PM)

Great idea for my style manager, build that worksheet!

 


Comment by: Elardus Mare (3/16/2010 8:42:09 PM)

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 (3/16/2010 11:26:09 PM)

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