Microsoft Excel — Get Started with a Custom Template Workbook
Save Yourself the Excel Setup Ritual Everytime You Create an Excel Workbook
In a past Medium post ( “Microsoft Excel user? Consider these 5 Productivity Hacks!”), I touched on template files. In this post, I expand specifically on this concept.
Anyone who uses Excel on a regular basis knows the routine…
Open up a new workbook…
Then…
- Set your font and size
- Set number formatting
- Set column width
- Set up a print footer (if one is created at all…)
- And on and on…on each sheet you use in the file!
THERE HAS TO BE AN EASIER WAY! :-) Fortunately, there is AND it is really easy!
Use a Template Workbook and Worksheet
All this means is…pre-define your layout and then set Excel to open up the template EVERY TIME you start a new file.
It’s that simple.
My template workbook and worksheet are set up with these preferred settings:
- 1 sheet in new workbook (bonus Sheet included with links to Macros that will help you!)
- Font set at Arial, size 10 point
- Numbers formatted in Accounting format, no decimal places
- Column width 12
- Basic print footer — Filename on Left bottom, Page # on Right bottom
In addition, the template workbook contains a sixth sheet that provides access to several macros that are useful for any Excel user:
To add a macro, click on the link to access, copy VBA code and paste it into the current workbook VBA Editor (you will then need to save as a “macro-enabled” workbook — which is a .xlsm format).
Setting up a Workbook and Worksheet Template for use
- Create your template workbook and worksheet file. The “worksheet” file is a ONE-SHEET file that Excel uses any time you want to CREATE a new worksheet in an existing file. It is named “sheet.xltx” and has the same settings as your worksheets in the Workbook file.
- If you want the template files I refer to above, you can request them here.
- Turn off Excel’s Start screen — this is the screen that, by default, pops up when you start a new Excel file. Instructions to do this can be found here (thanks to from contextures.com) for this helpful post!
- Copy the template files to the XLSTART user directory — Excel 2010 & 2013 — C:\Users[username]\AppData\Roaming\Microsoft\Excel\XLSTART — ([username] in my case is “dtomoff”). Another option is to SEARCH your directories for the XLSTART folder:
Final Notes
Once you get started using the new default templates, you will notice a significant time savings. It just eliminates steps that we have historically just lived with.
If you don’t use the MACRO worksheet in the file, or are sharing the workbook with others, just DELETE the worksheet!
