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)
I 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.
The 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?
First 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.