Excel doesn't respect saved styles in template

User avatar
LineLaline
2StarLounger
Posts: 194
Joined: 19 Sep 2022, 16:51

Excel doesn't respect saved styles in template

Post by LineLaline »

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 :sad:
Ceci n'est pas une signature.

User avatar
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

Post by HansV »

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).
Best wishes,
Hans

User avatar
LineLaline
2StarLounger
Posts: 194
Joined: 19 Sep 2022, 16:51

Re: Excel doesn't respect saved styles in template

Post by LineLaline »

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?
Ceci n'est pas une signature.

User avatar
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

Post by HansV »

What happens if you type or copy/paste

%appdata%\Microsoft\Excel\XLSTART

into the address box of Windows Explorer and press Enter?
Best wishes,
Hans

User avatar
LineLaline
2StarLounger
Posts: 194
Joined: 19 Sep 2022, 16:51

Re: Excel doesn't respect saved styles in template

Post by LineLaline »

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.
Ceci n'est pas une signature.

User avatar
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

Post by HansV »

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.
Best wishes,
Hans

User avatar
LineLaline
2StarLounger
Posts: 194
Joined: 19 Sep 2022, 16:51

Re: Excel doesn't respect saved styles in template

Post by LineLaline »

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! đź’–
Ceci n'est pas une signature.

AdrianJ
NewLounger
Posts: 2
Joined: 28 Sep 2023, 07:20
Location: Jhb, RSA

Re: Excel doesn't respect saved styles in template

Post by AdrianJ »

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 ?

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Excel doesn't respect saved styles in template

Post by rory »

Using Ctrl+N will use the book template. Using File - New - Blank workbook has never, to my knowledge, used the Book template.
Regards,
Rory

User avatar
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

Post by HansV »

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.
Best wishes,
Hans

AdrianJ
NewLounger
Posts: 2
Joined: 28 Sep 2023, 07:20
Location: Jhb, RSA

Re: Excel doesn't respect saved styles in template

Post by AdrianJ »

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.

User avatar
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

Post by HansV »

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.
Best wishes,
Hans