Garreth Hanley:
Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.
Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode, I'd like to talk about Excel's templates. Now, templates are available in the Office Suite, so PowerPoint, Word, Outlook. You might've even used an Outlook template if you've created an out of-office message, because you typically save a email as a template, and so that you can use it in the rules when you're creating the out of-office rule.
Now, Excel has different types of templates. There's blank templates that you can create, as well as task-based templates, which are already populated in terms of structures, and then you just add the extra data to them. You can access different templates in Excel a couple of ways. File-based templates can be accessed via the file and new option, and there's all of the built-in ones are shown in the listing.
If you point to one of them, you'll see that there's a little pin. So if you did want to pin any of the templates to the top of the list, you can. There's also a personal option there, so you can see your personal templates. Just a reminder that the companion video covers all of this content, and it shows you how to save and where to save the templates and explains in a lot more detail about where the folders are and things like that, that you might use for saving templates.
So the big advantage with templates is that it stops you overwriting a file. So have you done this? Opened up last month's version of a file, made the changes to it, and then saved it, and then thought, "Oh, I should have saved that as this month's version of the file." Well, templates can stop that happening.
So templates force you to basically rename the file, and that avoids that overwriting issue. Now, to access a sheet-based template, so a template for a sheet, if you right click a sheet tab, you'll see the insert option right at the top. And if you click that, you'll see a listing of the sheets that are available that you can add in. There's also an extra tab there that you can click and see some more templates as well.
In terms of blank templates, you can create your own blank template for a file, a workbook, or a sheet, a tab. And in those templates, you can bring in certain settings that make your blank template a bit more useful. The settings you might think about adding to the template are your print settings. So you might have a standard header and footer for your printing. Maybe you need to include a disclaimer or something like that in your footer.
And so you can build that into your blank sheet or blank file template so that you don't have to manually add them in each time. Some other things you might think about formats. So you might want to add some formats that you use all the time. In the example video, I show you the brackets format, so the red brackets. So it's very common for accountants to want to use the red brackets for the negative values.
And so you can actually save that format to the template so that it's available as soon as you create the file. Normally, you have to create what's called a custom number format for the red brackets format, but you can actually include that in your file. Something else that you might want to add is maybe icons or images that you use regularly. So you might have a logo, for example, that you need to use in your files.
So you could have that in the template that you create. Now, the location where you need to save the blank templates, it's called XLStart. It's an XLStart folder. It's a system-based folder. So you will need to track it down. It'll be on your C-drive somewhere, and that's a fairly special folder. In that folder, if you have a file in that folder, it automatically opens when Excel starts.
It's also the folder that you save your personal macro workbook if you use macros. When you're saving the files for the templates, you need to save it as an Excel template, which is a file type, and you need to use special names for the files. So for a workbook, for a file, an Excel file, you need to use the word book. So, B-O-O-K, that's the name of the template that you save to the XLStart folder.
For a sheet, you need to use the word sheet, S-H-E-E-T. And again, that's a template. That sheet should only have a single sheet in it, and that's the template that will be used when you insert a sheet in any files from then on. Now, in terms of customised templates, so these are templates you might use for special tasks like creating a journal or a budget submission.
Those type of templates, you can save in the system template folder. So as soon as you change the file type to template, Excel defaults to either the system-based template folder or a personal template folder. So you can actually set up your own folder for your own templates. To do that, if you press in sequence, alt TOS, that will open up the save section of the Excel options, and there's a special personal folder there for your templates that you can update.
So again, check out the companion video that shows you how you can use that. So another idea is to use a shared folder to hold your templates on an organisational level. So if you want everyone to be using the same templates, then maybe your IT section can create a shared folder that you use to hold the templates that your organisation want to use. Now, there is a technique in Excel that can use any Excel file like a template.
If you click file on the ribbon and any of the files listed there, if you right click it, there is an option for open a copy. And so that takes a copy of that file. It puts a number on the end of the file name to sort of differentiate it. And then when you try to save it it's going to ask you to confirm the name and then confirm the location where you want to save it.
So again, it stops that overwrite issue that you can often face. Okay. So templates in Excel can save you lots of time. They can capture lots of settings to save you doing them manually each time, and you can also share templates with other people. So I hope you found that useful. Thanks for listening.
Jackie Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.
