Formatting with Style in Excel

Consistent formats made easy

Styles are an underrated feature in Excel. They provide an easy way to achieve consistent formatting throughout a workbook.

Consistent formats are one way to make spreadsheets easier to use. For example if all input cells are coloured orange it makes it easier for the user to know where they need to make entries.

Styles were updated in Excel 2007 and made more prominent by placing them on the Home Ribbon tab. See below.

Styles on Ribbon

Clicking the drop down arrow shows all the Styles available.

All Styles

A Style is a standard format that you can use to apply the same format to the same type of cells.

If you have existing formats applied to a cell you can create a Style based on that cell by first selecting the cell and clicking the New Cell Style option above. You will need to give it a name and click OK.

Once a Style has been used to apply a format to a cell then amending that Style will automatically change all the cells that were formatted using that Style.

For example the default colour for input cells is orange. Let’s say you use that orange input Style throughout a new spreadsheet and then show it to your manager. He says you should have used yellow for input cells. All you need to do is open the file amend the Style to use the yellow background and all the cells that used that Style will update automatically and the job is done.

To amend a Style right click it and choose Modify.

Modify Style

The dialog that displays shows the standard options which you can change. Click the Format button to see all the format options you can use.

Modify Dialog

Note this is cut-down version of the Format Cells dialog. You can’t use all formats with a Style. You also can’t use Conditional Formats with a Style.

Style_05

WARNING:

Another issue you need to be aware of is that if you apply a Style and then adjust the cell format with another format say the % format. If you then amend the Style that will remove the % format that you had applied.

Multiple Styles

One way around the above issues is to use multiple Styles. Eg one input Style each for %, for $ and for dates. This requires slightly more maintenance but it handles changes much better.

Please note: I reserve the right to delete comments that are offensive or off-topic.