(Side note - please feel free to email me or comment with questions or suggested topics for this blog...it makes me have to think less, which is always welcome!)
Right, lets get to it. Last week we looked at how to create a standard template which opens every time you open Excel. Why would you do this? To save time and create a fabulous, unique brand for your Excel output.
If I can summarise the emails/comments I received into 3 main questions:
1) Once you are already in Excel, how do you get Excel to give you your lovely, newly formatted workbook every time you press Ctrl N, instead of giving you the boring old default workbook?
2) How do you get your new formatting to apply to new sheets you add to your current workbook?
and
3) Can you have more than one specially formatted workbook?
The solutions to these points are very similar (use an Excel template), but have very specific differences in implementation, so take heed of the answers:
Question 1:
Once you have followed the steps in last week's blog, you will have succeeded in saving an Excel Workbook into your XLStart folder, which ensures you get your new template every time you start Excel.
If you want every new workbook you create to look like this, without having to re-open Excel, then save your Workbook into the same XLStart folder, BUT save it as an Excel Template (*.xltx), and VERY IMPORTANT - name it as book.xltx. Don't accept any names your helpful pc might suggest, as this will only work as book.xltx.
So, just to be sure to be sure (as us Irish would say):
a) Make sure it is in the XLStart folder (as soon as you select Excel Template, your PC might helpfully direct you to a Template folder...push the back button to get back to the XLStart folder)
b) Make sure it is named book.xltx (unless you have Macros in your workbook, then save it as an Excel Macro-Enabled Template (*.xltm))
Once you have done that, if you are in Excel and press Ctrl + N, your newly formatted Workbook will appear, as opposed to the standard default option.
If you are ever feeling nostalgic and want to get the standard Microsoft default workbook back, go to File - New - Blank Workbook.
Question 2:
To apply your formatting to new sheets you add to your workbook, you follow the same steps as above, with two small differences:
a) Make sure your formatted workbook contains only one sheet, and make sure that sheet is formatted as you want all new sheets to be, and
b) Save your sheet into the XLStart folder as an Excel Template BUT name it sheet.xltx.
Simple as... Now, when you're in Excel and add a new sheet, it should be formatted with your new brand!
Question 3:
What if you need to have a range of different templates? Handy if you prepare analysis for different companies with different colour schemes, for example.
To do this, create your first workbook in the design and layout you need. Now you are going to save this as a Template too, but you save it into the Template folder which Microsoft suggests. So click Save As, and select Excel Template (or Excel Macro-Enabled Template) from the dropdown list:
Make sure the Template folder comes up (as above) and save your Template with an appropriate name. You can repeat this for as many different templates as you need.
When you need to use a specific template, you just need to be in Excel. Go to File - New - My Templates:
The following little box will pop up with your selection of Templates:
Pick your template and you're sorted.
One word of warning - remember to save your newly opened workbook as something else immediately, or as soon as autosave kicks in, your template will be changed!
If you would like a friendly reminder to read this blog whenever there is a new post, please subscribe here.
Thanks for reading, and happy spreadsheeting!
Re Q2 above: This works a dream when you click the insert worksheet to the right of the existing sheets in a file. But the other way I often add a sheet is to insert a Pivot Table and opt to have it in a new sheet. For some reason the new sheet created this way does not follow the template structure (in my case, the page setup options I have in my template are not applied to this new sheet). Any ideas why this would be?
ReplyDeleteHi.
ReplyDeleteThis appears to be a glitch in the system. The same way as if you go File-New-Blank Workbook you get a 'old style' workbook, I think when you insert a pivot table into a new sheet, it does the same.
One way to get around this is to insert a new sheet first, as this will have your formatting. Then when you create a new pivot table, select the option "Existing Worksheet" and click on cell A1 on your new worksheet.
Not ideal, I know, but as good as its going to get!