Forum Discussion
Excel Custom Styles available to all workbooks, new or existing
May 15, 2023
Microsoft® Excel® for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit
According to Microsoft Excel documentation you should be able to create custom styles, use them in the workbook in which they were created and, if you wish, make them automatically available in new workbooks, or existing workbooks (when they next opened).
It was suggested that this can be accomplished by opening/creating a new (blank) workbook, then merging styles from the other (still open) workbook where the custom style was created.
This procedure suggests that by doing this, any new workbook subsequently created will automatically include your new custom style. It was further suggested that if you create or open an existing Book1.xltx (template) workbook that you create and save into the XLSTART folder, your custom style will always be available to any new or existing workbooks thereafter.
These methods are not working.
What is the proper way to make custom styles available to all existing workbooks? Is it not Book.xltx that needs to be created in the XLSTART folder? Should it be Book1.xltx (notice the number 1 in the name). Is the XLSTART folder in the right location? Is there a global setting in Excel to enable the use of a standard template that contains your customizations?
I am open to all corrections to my interpretation of methods described above.
Hi RustyCT1
unfortunately, it's a bit complicated... 😉
First of all, whatever Workbooks you store in the XLSTART directory in your users directory will be opened when you launch Excel:
C:\Users\(username)\AppData\Roaming\Microsoft\Excel\XLSTART
If you just want a "customized" workbook with your own cell styles, you create these formats in a blank workbook and store it as a template (*.xltx) in XLSTART. The name must be Book.xltx (not Book1.xltx, so you are right with your assumption).
And whatever you have stored in that template (cell styles, fonts etc.) will be available the new blank workbook, when
- you launch Excel
- you create a new workbook using Ctrl+N
- you create a new workbook by clicking the "New File" icon in the "Quick Access Toolbar", if you use that:
The new styles are NOT available, if you create a new workbook using the menu "File | New | Blank workbook":
This seems to be a different template an I have no idea if this "Blank workbook" in the file menu can also be changed somewhere.
In any case, the adjusted cell style will be available only in new workbooks, but not in existing ones And I'm not aware of a method to make them available to already existing workbooks (but of course I'm willing to be convinced of the opposite 🙂 .
2 Replies
Hi RustyCT1
unfortunately, it's a bit complicated... 😉
First of all, whatever Workbooks you store in the XLSTART directory in your users directory will be opened when you launch Excel:
C:\Users\(username)\AppData\Roaming\Microsoft\Excel\XLSTART
If you just want a "customized" workbook with your own cell styles, you create these formats in a blank workbook and store it as a template (*.xltx) in XLSTART. The name must be Book.xltx (not Book1.xltx, so you are right with your assumption).
And whatever you have stored in that template (cell styles, fonts etc.) will be available the new blank workbook, when
- you launch Excel
- you create a new workbook using Ctrl+N
- you create a new workbook by clicking the "New File" icon in the "Quick Access Toolbar", if you use that:
The new styles are NOT available, if you create a new workbook using the menu "File | New | Blank workbook":
This seems to be a different template an I have no idea if this "Blank workbook" in the file menu can also be changed somewhere.
In any case, the adjusted cell style will be available only in new workbooks, but not in existing ones And I'm not aware of a method to make them available to already existing workbooks (but of course I'm willing to be convinced of the opposite 🙂 .
Thank you Martin_Weiss for this detailed and helpful response. I believe you nailed the problem with Microsoft's documentation with this explanation, especially with:
"[...] will be available the new blank workbook, when
- you launch Excel
- you create a new workbook using Ctrl+N
- you create a new workbook by clicking the 'New File' icon in the 'Quick Access Toolbar'"I realized immediately that I'd been testing the steps of creating and using a customized Book.xltx placed in XLSTART by opening a blank workbook that does not use Book.xltx! Instead of opting to open a new workbook via one of the 3 methods mentioned above by you, I had been using the (wrong/failing) approach available at launch:
Perhaps both new-workbook-opening methods shown above will not work, even though I'm suggesting the one on the left might work. In my testing I only used the one shown on the right.
Bottom Line: Be careful which shortcut you use to open a blank workbook if you want Excel to model your new workbook after your XLSTART saved template: Book.xltx.
