I use a lot of custom formats and custom colours in my spreadsheets. I have to re-create them in my new files. Is there a way to have these custom formats automatically included in new sheets and files?
By Neale Blackwood
Yes, by using templates. Excel allows you to create templates that have specific uses. There are a number of built-in templates in Excel and more are available through the Microsoft website. Right-click a sheet tab and choose Insert and click the Spreadsheet Solutions tab to see the built-in templates. Click Cancel if you don’t want to insert a template.
You can use a template to change the default sheet and default file that Excel creates by saving templates into is the XLSTART folder, this folder’s location can vary depending on the installation. You need to identify the path of the XLSTART folder before you can save anything to it. Right click the Windows Start icon and select Search. Type in XLSTART in the file name box, use the drop down arrow in the Look in box and select Local Hard Drives, then click the Search button. Make a note of the folder path. If there is more than one XLSTART folder you will need to note the current folder.
You now need to create your templates. The template for new sheets will be a single sheet file with all the custom formats, colours, headers and footers that you want the sheet to contain. You need to use Save As (press F12 key) then change the Save As Type to Template (Excel 2007 users see note below). This will automatically open the default template folder where you save standard templates, not default templates. You need to change the file name to Sheet and save the file to the XLSTART folder.
To create the default file template you do exactly the same as the sheet file above (formats, headers etc) but the file can have multiple sheets and the template file is named File and also saved in the XLSTART folder.
Excel 2007 note: In Excel 2007 there are three types of Templates. The standard Template can’t contain macros and is in the Excel 2007 file format. The macro-enabled Template is for any templates that contain macros. This is also an Excel 2007 file format. The last type of Template is compatible with previous versions.
These templates are only available on the computer concerned. To use on other computers you would need to copy the files to the XLSTART folder on those computers.