How To Create A Custom Cell Style In Excel

Make A Custom Cell Style Available For All Workbooks

by Francis Hayes (The Excel Addict)

When you create a custom Cell Style, that style is saved only in the workbook in which it was created. If you want to make a custom Cell Style available for all workbooks, you must save it in Excel's default workbook and worksheet templates. These are the templates used for all new workbooks and worksheets.

Here's how...

1) Open a new workbook (CTRL+N );

2) Delete all but one sheet tab;

3) Create a new Cell Style: From the Home tab click Cell Styles and New Cell Style... and enter a descriptive name for the style;

4) In the Style dialog, check each group of formatting characteristics

(e.g. Number, Alignment, Font, Border, Fill, Protection) you want to include in the style and uncheck those you don't;

5) Click the Format... button and choose the specific characteristics (e.g. font name, font size, fill color, etc...) that you want to include in the style then click OK;

6) Depending on the version of Excel you are using, the formatting that you selected when you created the new style may have been applied to the cells that were selected on the worksheet. If that's the case for you, you will need to either delete those cells or overwrite them with formatting from a normal cell (i.e. Copy and Paste);

7) From the File menu select Save As...;

8) In the 'Save as type:' field select Excel Template (*.xltx);

9) In the 'Save in:' field, browse to your XLSTART folder. If you don't know where to find your XLSTART folder, copy and paste the following line into the 'File name:' field in Excel's Save As' dialog and press Enter. That should open your XLSTART folder in the Save As dialog;

%APPDATA%\Microsoft\Excel\XLSTART

Easily locate your XLSTART directory / folder

10) Select book.xltx from the XLSTART folder or type book i n the 'File name:' field. This is the default workbook template;

11) Click Save (book.xltx remains open);

12)

Excel's default worksheet template is a one-sheet workbook used for inserting a new worksheet. By saving our custom Cell Style in the default worksheet template, we can then add that style to any existing workbook simply by inserting a new worksheet. To save the Cell Style (now in book.xltx) to the default worksheet template, click File, Save As. The XLSTART folder should now be visible. If you can see sheet.xltx in the XLSTART folder click on it, otherwise type sheet in the 'File name:' field and click Save;

13) Exit and restart Excel;

14) Open a new workbook and click Home, Cell Styles and you should see your custom cell style in the Custom section at the top of the Styles Gallery;

Plus you also get my 'Excel in Seconds' E-book as a BONUS!

(Download it immediately after you sign up)

This site is not affiliated with Microsoft Corporation.