Setting Workbook/Worksheet Defaults for new Workbooks

[Mr. Walnuts]

I feel like such an idiot for having to ask because I've known the answer before but...

Where and 'as what' do you save a workbook once you have the settings (cell size/font/number of sheets/formatting/etc) so that every new workbook you open will have these atrributes on that machine?

 

[Michael M]

Hi Chris
I think you mean either
File / SaveAs / Excel Template
OR
Home Button / Excel Options / Popular

 

[Mr. Walnuts]

[size=-1]

Hi Chris
I think you mean either
File / SaveAs / Excel Template

[/size]

You are correct.

The method (I learned before, but have since forgotten) of altering the default worksheet formatting (column widths, row heights, font face, font size, text wrap [by default], alignment properties, etc... for all new worksheets added in Excel DID use the process mentioned above File --> SaveAs --> Excel Template (.xlst)

Only, I CANNOT remember the directory location the template must be saved in for it to work in perpetuity.

I remember that once you formatted a worksheet (worked on workBOOKS also)... if you saved the template in a specific directory (with the title 'Worksheet' and 'Workbook'... respectably & without the apostrophes), new worksheets added from that point forward would automatically populate with your desired formatting in place.

Does ANYONE know what that directory is? I'm so tired of 11px calibri font on 11.5w x 8.09h Column Width & Row Height respectively... and general & bottom alignments (horizontal & vertical respectably)

Could someone PLEASE tell me the folder directory to save these templates to.


C.Rudolph
SFC, US Army
Retired
• Budget Analyst
• Aviation Maintenance &
Logistics Management
• Aviation Financial
Management

Email: chris@clever-dudes.com
Blackberry: 205-577-One-Nine-Five-Eight

No Number Mining by Phone # collectors.

 

[Mr. Walnuts]

Grr... I know back on Vista, the directory was:

C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart

 

[Mr. Walnuts]

AAhhh-HA!@#!

a.k.a....

EurEKA!!#~... By Watson, I've GOT IT!

The principle is fundamentally the same; I was thrown off because the new Excel (2007- Present) give you the option in the actual options menu to set the default font. Prior to then, you had to use this trick to change ANY of the defaults...

On XP and Vista... the procedure was to format a workbook and then save it as a template in the directory:
C:\Users\user name\AppData\Local\Microsoft\Excel\XLStart...
... which does NOT exist in Windows 7, which through me for a loop.

The new fix for it though... is the same fix, different directory.

To change the default formatting for a new Workbook... open a new workbook, format everything as you would want a new workbook to look like and operate when you first open it...
Examples: Font Face, Font Size, Number of WorkSheets, Alignments, Borders, Page View, Number Properties [currency, accounting, text, general, date, special], background fill, etc... and once you have the Workbook formatted the way you would want every new workbook formatted from the 'Get-Go'... (For Making a Workbook Default, name the file Book.xlst, if you're making a Worksheet default... name it Sheet.xlst.


Go to File --> SaveAs --> Browse Computer (For Folder Directory) --> Once you're in the "Save" Box... find the following folder: C:\Program Files\Microsoft Office\Office15\XLSTART for Office 2013
Now the directory can be different based on which version of Office you're using... and whether you're on a 32bit or 64bit Windows system... so here's a list detailing which directory you'll need to save it in for which windows system.

List -

Windows 7 x86 (32 Bit)

• MS Office 2010 or MS Office 2013 32-Bit - C:\Program Files\Microsoft Office\Office15\XLSTART
  MS Office 2010 or MS Office 2013 64-Bit (Will not run on Windows 7 32-bit)

Windows 7 x86 (32 Bit)

• MS Office 2010 or MS Office 2013 32-Bit - C:\Program Files (x86)\Microsoft Office\Office15\XLSTART
  MS Office 2010 or MS Office 2013 64-Bit - C:\Program Files\Microsoft Office\Office15\XLSTART

For most, its obvious why the directories are different... but for those that do NOT understand... There are TWO "Program Files" folders on every 64-bit version of Windows™... one for the 32-bit programs, and one for the 64-bit programs... the 32-bit programs are located in the folder titled "Programs Files (x86)"... 32-Bit Windows Systems only have the one Program Files folder.

*** Remember, To set the default formatting for a Workbook, save the file as book.xlst...
To set the default formatting for a Worksheet, save the file as sheet.xlst...