Wednesday, June 4, 2014

Use A Template For Pre-Applied Formatting


If you need to use a specific format for all your files, you can try saving the format you want as a template file. and whenever a new Excel file is created, you can directly apply template rather than making changes manually. For example: if you always want gridlines to be off when you create a new workbook or want to have a standard text for header etc, you can use templates.

One of my friends in billing team spends most of his time preparing invoices in Excel and printing them. He keeps an invoice format in a file on his desktop and opens that each time. He could save some time if he can save that invoice format as a template and keep it in start-up folder so that every time he opens up an Excel file, it opens with his invoice format by default. Let’s see how to do that.
  1. Create a new workbook
  2. Apply all the formatting you need (cell format, styles, sheet format), data validation settings etc to this workbook
  3. Press Ctrl+S
  4. In the Save in field, locate XLSTART folder. This folder can be found in one of the following locations depending on your operating system.
    • Windows XP: C:\Documents and Settings\<username>\Application Data\Microsoft\Excel\XLStart
    • Windows Vista: C:\Users\<username>\AppData\Local\Microsoft\Excel\XLStart
    • Windows 7: C:\Program Files\Microsoft Office\Office\XLStart
  5.  In the File name field, type Book
  6. In the Save As window, Select Excel Template (In Excel 2007 onwards) / Template (In Excel 2003 and below) in Save as type field drop down list.
  7. Click OK.
That’s it. From now on, whenever you create a new workbook, it will contain all the formatting you applied in step 2 above.