Cell Styles in Microsoft Excel

Nov 19, 2020 | Excel Hints and Tips

Consistent formatting helps to make documents look tidier and makes them easier to read and understand.  One feature that can help with this is cell styles.  There are over 40 pre-created styles for you to get started with or you can create your own bespoke designs to format your spreadsheets with ease.  They can even be transferred from one file to another.

Pre-setup Styles

To assign a style to a cell or range of cells they must first be selected.

Then Cell Styles is selected from the Home tab.

The styles are divided into 3 categories

  • Good, bad and neutral
  • Data and Model
  • Title and Headings

Image of Excel Styles

The text on these styles indicates when you might use them but they can be used for anything you like if these do not suit your needs.

New Cell Style

New cell styles can be created to enable you to produce the exact formatting experience you require.  You can create as many styles as you like.

  1. From the Home tab select cell styles and New cell style.
  2. Give the style a useful name and choose which of the Excel format styles are going to be applied as part of this.  The styles ticked will override the formatting for these when applied.
  3. Click on the Format button to open up the format cells dialog box.  Here the specific format can be produced including Number, alignment, font, border, background fill and protection options.

Image of New Excel Style

Clicking on OK does not apply the format but produces it ready for use.  The new cell style will now be available from the Cell Style dialog box under a new section at the top called custom.

Working with styles

This applies to both pre-set styles and custom ones too.  When you right-click on a style you get the following options

  • Apply – does the same as clicking on the style and applies the formatting to the selected information
  • Modify – takes you back into the style dialog box where all the formatting can be modified as required
  • Duplicate – duplicates the style so that this can be used as a starting point for a new style.  This is really useful if you want several styles that are fairly similar
  • Delete – deletes the style
  • Add gallery to quick access toolbar – adds the cell styles icon to the quick access toolbar for speedy application

Image of Modify Style

Transferring styles from one file to another

The file containing the style and one it is to be transferred to must both be open.

  1. Start from the file you need to transfer the style to
  2. Choose the file you want to transfer the style from
  3. Select merge styles

It may ask about merging styles where the same name of style appears in both files

If you are spending a lot of time manually updating your formatting it is worth considering whether setting this up will save you a lot of time in the future.  You could even set this up in a template and use this as a starting point for all your Excel spreadsheets in the future.

Want to learn more about Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.