≡ Menu

Using Custom Cell Styles

Excel Tips and Tricks

How many times have you had to set up a custom format to show a negative cell value in red font with brackets?

(£105,000) or if I’m being fancy (£105k)

Custom Cell StylesI think I do it once every work day and I bet I am not the only one.

No more though, because today I have discovered custom styles and have put them in my startup folder for excel. Every time I open up a new workbook, these custom styles will be available to me.

Oh the joy is hard to contain.

custom formatsThe other thing I learned today is that the addition of the underscore and dash symbols _- to the end of a custom format, applies a little bit of padding to the right of a cell value so that it is not butting up against the cell border. I’ve seen this before and it has driven me to distraction trying to fathom out why some values are right aligned and others just “almost” right aligned.

Now I know and I’ve opted (at least for now) to use it in all my custom styles.

How do you Save Custom Cell Styles so that they can be used over and over?

Example Cell StylesFirst of all you need to set yourself up with some great styles, or better yet, nick someone else’s. Here’s a copy of my example Excel Styles workbook.

Make sure you’ve got this workbook open.

Open up a new blank workbook and then import the styles from the open excel styles workbook by using the merge styles functionality:

  • On the Home Tab go to Cell Styles
  • Select the button Merge Styles
  • Press OK

Now you need to save this workbook as an excel template called book.xltx in the XLSTART folder (see the section below on Where have you Hidden the XLSTART folder) and job done. When you close excel and re-open, your custom styles will be available in every new workbook.

How to Bring Custom Cell Styles into an Existing Workbook

Well this is easier than bringing them into every new workbook.

  • Make sure you have the workbook with the desired styles open.
  • Open the existing workbook that you would like the new styles to appear in.
  • On the Home Tab goto Cell Styles
  • Select the button Merge Styles
  • Press OK

Job done, the styles should be available in your old workbook as well now.

Where have you Hidden the XLSTART folder?

I would suggest using the search functionality to track down your XLSTART folder – hit the Start button and type XLSTART into the search box.

I am on the work network and my path looks like:

Network\org_name\userdata\Org_AppData\username\Application data\Microsoft\Excel\XLSTART

If you are at home it might be more like:

C:\Users\\AppData\Local\Microsoft\Excel\XLSTART

Or

C:\Documents and Settings\\Application Data\Microsoft\Excel\XLSTART

Having found it I have saved mine to my favourites so it is easy to navigate to.

Useful links for working with Cell Styles

  1. https://msdn.microsoft.com/en-us/library/dd537556(v=office.12).aspx
  2. https://support.office.com/en-us/article/Save-cell-styles-to-use-in-all-new-workbooks-0b3905c5-45d0-4ad4-be39-571f445d30cb
Doctor Moxie

Written by -

NHS Accountant with geeky tendencies - serial blogger on subjects varying from Excel, Raspberry Pi, productivity, allotment gardening and running. The NHSExcel blog is reserved for Excel topics.

Comments on this entry are closed.