ICAEW 20 Principles Excel workbook template

• ICAEW 20 Principles Template

A recent article in Chartech News covered the detail of how to install and use the template. For convenience, the relevant content is also included below.

Template sheets

The template is designed as a simple first stage in implementing the 20 Principles. It includes sheets that document details of the workbook's contents and version history. The Good Practice Score (GPS) sheet shown above is intended to provide a reminder about the application of each of the 20 Principles in the form of 'self-assessment' questions. The GPS sheet has been designed to highlight possible failures to apply the 20 Principles, using conditional formatting to automatically apply traffic light colouring. A check sheet is also included to emphasise the need to include relevant checks in any Excel workbook, and to make the results of those checks as visible as possible. Finally, Sheet 1 is intended as a model sheet, containing a key to the cell formatting used, to be copied to create additional sheets in the workbook as required:

Using the template

Excel templates are a long way from being adopted universally by Excel users, so it is worth looking at some different ways to deploy this, or any other, Excel template. Excel templates are normal Excel workbooks saved as the File Type: 'Excel Template (*.xltx)'. Depending on the version of Excel you are using, Excel might automatically change the 'Save As' file location to the correct templates folder when you choose the Excel Template file type, or you might need to specify the location. One way of finding the folder path for the templates folder, and for the XLSTARTUP folder that we will be using next, is to go to Excel Options in the File screen and choose Trust Center, click on the Trust Center Settings… button, and then choose Trusted Locations. This will display various User and other locations. You can select the location you want to save your file to, then click on the Modify button, in order to be able to copy the full folder path, ready for pasting as your save location:

Once you have sorted out where the template file is to be saved, open the downloaded template file if it is not already open in Excel, and use File, Save As, or the equivalent F12 keyboard shortcut, and change the Save as Type to Excel Template (*.xlxt). If the location shown at the top of the dialog doesn't change automatically, you can then paste in your location as copied from the Modify dialog of Trusted Locations. If you do paste a location in, make sure that you press the Enter key to change to that location before you click on the Save button.

With your template file now saved in an appropriate templates folder, when you use File, New to create a new workbook, you should find that template in the Custom section and you can just click on it to create a new workbook based on your template.

One step further – making this your default template

As we have just mentioned, saving the template in the templates folder allows you to choose it when you use the New option from the Excel File screen. However, if you just click on the New button that you might have added to the Quick Access Toolbar, or use the Control+n keyboard shortcut, you will not be given the opportunity to choose a template. If you wanted to make this, or any other, template the default, then you need to save the template with a specific name to a specific location. The template needs to be called 'book' and to be saved to one of Excel's StartUp folders. The procedure would be the same as in the previous example, except that we would copy our location from the User StartUp Trusted Location and we would change the file name to: book.

With a file named book.xltx in the User StartUp folder, we should find that this is used as the default new workbook, without needing the user to choose.

We can use the same technique to create the default sheet that is used when we press the New Sheet button. We would just create a workbook with the single sheet we wanted to use as our template, perhaps including our formatting Key as our template Sheet 1 does, and then save that workbook to the User StartUp folder, but with the name 'sheet' rather than 'book'.