Windows 10 Home
Office 365
For some time I wondered 'where are my styles?' and decided to now do something about it.
Excel would usually create a new file based on 'book.xltx' or '.xltm', or even the 'book1' version of both. Not anymore.
I opened the files and the styles are in them but Excel doesn't use them and simply uses the defaults.
I tried to find the solution and found that Microsoft advises to store the template in an 'XLSTART' folder, which I don't have.
I checked if any 'temp' files were created in the defined 'templates' folder, as is the case with Word and Outlook when those are launched, but there is no such temp file (the file preceded by a tilde).
I again saved the workbook in all possible worksheet template options but it doesn't help.
I then created a different folder and pointed Excel to use that one for the templates, saving the template files in that new folder. This didn't help either. I am stumped.
Can someone shed some light? I mean, I can of course merge the styles every time but this should not have to be the case
Excel doesn't respect saved styles in template
-
- 2StarLounger
- Posts: 194
- Joined: 19 Sep 2022, 16:51
Excel doesn't respect saved styles in template
Ceci n'est pas une signature.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel doesn't respect saved styles in template
The Book.xltx template MUST be stored in the XLSTART folder. But that is a hidden folder. To see it, activate the Folder Options dialog from Windows Explorer or by searching for it from the Start button. Activate the View tab, then tick the check box Show hidden files, folders and drives. Click OK.
Excel will use the template when the application starts, when you press Ctrl+N and when you click the New button on the Quick Access Toolbar (if you have adde it there).
Excel will use the template when the application starts, when you press Ctrl+N and when you click the New button on the Quick Access Toolbar (if you have adde it there).
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 194
- Joined: 19 Sep 2022, 16:51
Re: Excel doesn't respect saved styles in template
Thank you Hans.
I am very confused because the hidden folders are visible here. All hidden files, folders, and drives, as well as extensions, are visible.
But I really don't have an XLSTART folder. I am fully stumped.
I installed the suite from an earlier version and much later purchased 365. Is it possible that the earlier version used a different folder and 365 simply respected the previous settings?
I am very confused because the hidden folders are visible here. All hidden files, folders, and drives, as well as extensions, are visible.
But I really don't have an XLSTART folder. I am fully stumped.
I installed the suite from an earlier version and much later purchased 365. Is it possible that the earlier version used a different folder and 365 simply respected the previous settings?
Ceci n'est pas une signature.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel doesn't respect saved styles in template
What happens if you type or copy/paste
%appdata%\Microsoft\Excel\XLSTART
into the address box of Windows Explorer and press Enter?
%appdata%\Microsoft\Excel\XLSTART
into the address box of Windows Explorer and press Enter?
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 194
- Joined: 19 Sep 2022, 16:51
Re: Excel doesn't respect saved styles in template
Thanks Hans.
If i do that this happens:
'Windows can't find 'C:\Users\accountname\AppData\Roaming\Microsoft\Excel\XLSTART'.
Check the spelling and try again.
If i do that this happens:
'Windows can't find 'C:\Users\accountname\AppData\Roaming\Microsoft\Excel\XLSTART'.
Check the spelling and try again.
Ceci n'est pas une signature.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel doesn't respect saved styles in template
Select File > Options > Advanced.
Scroll down to the section 'General'.
Has a folder path been entered in the box labeled 'At startup, open all files in'?
If so, try moving Book.xltx to that folder.
If not: go to %appdata%\Microsoft\Excel in Windows Explorer.
Create a subfolder named XLSTART.
Move Book.xltx to the XLSTART folder.
Scroll down to the section 'General'.
Has a folder path been entered in the box labeled 'At startup, open all files in'?
If so, try moving Book.xltx to that folder.
If not: go to %appdata%\Microsoft\Excel in Windows Explorer.
Create a subfolder named XLSTART.
Move Book.xltx to the XLSTART folder.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 194
- Joined: 19 Sep 2022, 16:51
Re: Excel doesn't respect saved styles in template
Thanks Hans!
No folder path has been entered there, it's blank.
I then went to %appdata%\Microsoft\Excel.
To my surprise it did find the folder XLSTART.
But nothing was in there.
I moved the book.xltx file to that folder.
I then made sure 'Office-Click-To-Run' was shut down and relaunched Excel.
And now it works!!!
I have NO idea why the system could not find the XLSTART folder before, I even did a system search but nothing came up. Very strange.
But it works now and the XLSTART folder now contains book.xltx and a new file is created containing the styles.
Thank you sooo much!
No folder path has been entered there, it's blank.
I then went to %appdata%\Microsoft\Excel.
To my surprise it did find the folder XLSTART.
But nothing was in there.
I moved the book.xltx file to that folder.
I then made sure 'Office-Click-To-Run' was shut down and relaunched Excel.
And now it works!!!
I have NO idea why the system could not find the XLSTART folder before, I even did a system search but nothing came up. Very strange.
But it works now and the XLSTART folder now contains book.xltx and a new file is created containing the styles.
Thank you sooo much!
Ceci n'est pas une signature.
-
- NewLounger
- Posts: 2
- Joined: 28 Sep 2023, 07:20
- Location: Jhb, RSA
Re: Excel doesn't respect saved styles in template
I know this post is nearly a year old, but I am tearing my hair out !
I have the same problem - I am using Excel from the Microsoft 365 Apps for enterprise suite. For years I have nursed my Book.xlt, Sheet.xlt as well as the unrelated Personal.xls/xlsm from Office version to office version. I think the XLSTART folder has always been used for these templates. In 2017 or so, I converted to the required Book.xltx and Sheet.xltx formats. Yesterday was the first time I noticed that in a new blank workbook, my "Normal" style suddenly has vertical alignment set to "Bottom". I really dislike this so I had set this to "Center" in my template files. I opened each of these template files, from XLSTART, as templates, checked that the Normal style format was as I wanted it (vertical alignment) and saved as, overwriting the old ones. Actually they were identical, but I thought I'd re-save them anyway.
I can confirm that File/New/Blank Workbook gives me a workbook with one sheet as I required, but the Normal style still has vertical alignment set to "Bottom". If I insert a new sheet, then it is OK. So, Excel is ignoring the style settings in Book.xltx, but it is using them from Sheet.xltx.
What to do please ?
My current workaround has been to add a one liner to my Personal macro workbook with a keyboard shortcut to modify the vertical alignment of the Normal style, so now I can open a new workbook, hit the shortcut and carry on as usual! I just wonder why Excel is not doing what it's supposed to wrt Book.xltx ?
I have the same problem - I am using Excel from the Microsoft 365 Apps for enterprise suite. For years I have nursed my Book.xlt, Sheet.xlt as well as the unrelated Personal.xls/xlsm from Office version to office version. I think the XLSTART folder has always been used for these templates. In 2017 or so, I converted to the required Book.xltx and Sheet.xltx formats. Yesterday was the first time I noticed that in a new blank workbook, my "Normal" style suddenly has vertical alignment set to "Bottom". I really dislike this so I had set this to "Center" in my template files. I opened each of these template files, from XLSTART, as templates, checked that the Normal style format was as I wanted it (vertical alignment) and saved as, overwriting the old ones. Actually they were identical, but I thought I'd re-save them anyway.
I can confirm that File/New/Blank Workbook gives me a workbook with one sheet as I required, but the Normal style still has vertical alignment set to "Bottom". If I insert a new sheet, then it is OK. So, Excel is ignoring the style settings in Book.xltx, but it is using them from Sheet.xltx.
What to do please ?
My current workaround has been to add a one liner to my Personal macro workbook with a keyboard shortcut to modify the vertical alignment of the Normal style, so now I can open a new workbook, hit the shortcut and carry on as usual! I just wonder why Excel is not doing what it's supposed to wrt Book.xltx ?
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: Excel doesn't respect saved styles in template
Using Ctrl+N will use the book template. Using File - New - Blank workbook has never, to my knowledge, used the Book template.
Regards,
Rory
Rory
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel doesn't respect saved styles in template
Welcome to Eileen's Lounge!
Rory is correct. To use your Book.xltx, press Ctrl+N or click the New button on the Quick Access Toolbar (if it is not available there, you can add it).
Selecting File > New > Blank Workbook does not use Book.xltx, and never has.
Rory is correct. To use your Book.xltx, press Ctrl+N or click the New button on the Quick Access Toolbar (if it is not available there, you can add it).
Selecting File > New > Blank Workbook does not use Book.xltx, and never has.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 28 Sep 2023, 07:20
- Location: Jhb, RSA
Re: Excel doesn't respect saved styles in template
Great, thanks for the replies Rory and HansV and thanks for the Welcome HansV. I was a user of Woody's Lounge many moons ago for quite some time and I definitely recall HansV was there too ! Eileen sounds familiar too. Wow, was that in the mid to late 1990's ?
I suspect that I just hadn't noticed that new workbooks using the File - New - Blank workbook route wasn't ever using my Book.xltx, because I do now recall that another setting I wanted implemented in new books was to kill the grid lines from the outset. I however had a customised button on my QAT for that because when I receive client workbooks with grids on, I turn it off while I'm busy.
The next issue was that I have already assigned CTRL+N to a procedure in my Personal workbook to format numbers with thousands separators and 0 decimal. Rather than change a shortcut which is built into my subconscious now, I have just added a New item to my QAT as suggested by HansV.
I suspect that I just hadn't noticed that new workbooks using the File - New - Blank workbook route wasn't ever using my Book.xltx, because I do now recall that another setting I wanted implemented in new books was to kill the grid lines from the outset. I however had a customised button on my QAT for that because when I receive client workbooks with grids on, I turn it off while I'm busy.
The next issue was that I have already assigned CTRL+N to a procedure in my Personal workbook to format numbers with thousands separators and 0 decimal. Rather than change a shortcut which is built into my subconscious now, I have just added a New item to my QAT as suggested by HansV.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel doesn't respect saved styles in template
Woody's Lounge was active from the late '90s until the end of 2009. It then became the WindowsSecrets Lounge and has since been subsumed in the AskWoody Forums. I was active there from 2002 to 2009.
Eileen Wharmby was one of the founders of Woody's Lounge, together with Woody Leonhard. We named this board in her memory.
Eileen Wharmby was one of the founders of Woody's Lounge, together with Woody Leonhard. We named this board in her memory.
Best wishes,
Hans
Hans