How to create a template in MS Excel - Image by OpenClipart-Vectors from PixabayThe last two tips were about what is a template and how to create one in Word. Today it’s about how to create a template in Excel as it is slightly different to Word. For those of you who are new to this subject please read the first tip on what is a template in Office.

To start off, we do not need to create the whole template in one go. I still have some people in my classes thinking that you can’t start one off, say on a Friday and finish it on the following Tuesday. The easiest way I find is creating a normal document with the title of “Template for xxx“, or “Blank Customer Template“. Save it in a folder where you will easily find it to update and modify until you are happy that it works and then save it as a template to be used by you and others in your work group.

An Excel Template can hold all the information you require to create a new document on a regular basis. it can hold all the formulas you need but without the numbers. so that when you use that template to create a new document most of the work is done and the layout will be the same, together with the formatting. Thus you continue the “house style” in every document that you produce from that template.

What Excel was never created to do was forms, therefor I would suggest creating your forms in an appropriate application.

Other things Excel was not designed to be used for: Project management, Task management, Big Data analysis, Approval processing. There are better tools out there that should be used instead of Excel. Excel is for calculating.

Here is an example of a possible template you might need to create. This is a very simple spreadsheet. Template example

In the above image you can see the formulae I have used. Please don’t forget to hide these if you show them at all. Saving the document as a normal document (*.xlsx) and sleeping on it and using it a couple of times on dummy jobs will iron out the kinks so that you have everything you might need in it. So take the time to do this.

Once you are happy!

Saving the document as a template.

  • Select [File] and [save as] from the list. saving document
  • If you are unhappy about the name for this template, overtype the name in the first bar.
  • If not, leave it as is and move to the second bar, where you see a dropdown arrow for the extensions.

This list appears. extension list

  • If you do not have any macros in this template, then I suggest you select the [Excel Template (*.xltx)]

If you do have macros, then you need to select the Macro-enabled selection [(*.xltm)]

As soon as you select the extension, you are immediately navigated to this folder. destination folder

Yes, you can save this template to a different folder but keeping it here could make your life easier in the long run.

There is of course the possibility that your organisation has changed the defaults or ask you to use a specific folder for personal/departmental templates. Ask a colleague or manager if you are unsure.

How to use this new template

Once you have created and saved the template, please close the template. It is far too easy to start entering data and find yourself saving this information in the template thus creating a problem in future use of the template.

  • With no template in sight, select File then New from the list. Do not use the New tool, as this only creates a blank document. create new
  • Select the word Personal under the image of a blank workbook. Personal Templates

All templates that you have created will be listed here.

  • Locate and select the one you have just created.

The screen will show you a new document fashioned on the template you have just chosen. Empty of data but with all the text and calculations you require.

  • Read the name at the top of the screen to satisfy yourself that you are creating a new document using the template in question. Not amending the template itself. It should read like this.

Blank Customer Template

This shows you the template that you are using and the number corresponds to how many times you have used this template today.

Name of type or template = Blank Customer Template

Number used = 1

If you see this instead

the template

Then you are using and modifying the template itself, which you do not want to do at this stage. Close it and try again.

Once you have used the template once it will be shown in the list of possible templates in the first screen. You do not need to select the Personal word under the blank workbook as before.

One more thing

You can pin this template to this screen area.

pin template

If you start to create many templates, the list will grow and templates will not be seen easily. Therefore, you will have to go to the word [Personal] under the blank workbook as before.

Personal Templates Button

Removing from list

If you no longer wish the template to appear in this list, you can unpin it or right-mouse click on the image and select [Remove from list].Remove from list

This does NOT delete the template. It simply removes it from this first-level list.

Enjoy creating your templates and then having time to think about your work and doing it, not pancaking you have not got the time.

How to use the Transpose paste function in Excel

How to convert time to minutes using Excel formulae

Formatting across sheets in Excel



Please enter your comment!
Please enter your name here