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

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:

Input cells
Cells that are the main input to your model
Parameter cells
Cells that contain constants for your model, such as boundaries.
Output cells
Cells in an area that is meant for output, such as printing or presenting the results of a calculation on screen.
Calculation cells
The cells where the actual calculation work is performed
Boundary cells
By shading otherwise empty cells you can easily make areas with differing functions stand out from other areas.

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.