Create an Excel template from an existing spreadsheet and save time

7 Oct 2020   | Computer Stuff · Excel tips and tricks

In this tutorial I am going to discuss how to create an Excel template from an existing Excel spreadsheet that you use often and are happy with but which only needs new data entered for each new period.

Very often with Excel we find ourselves using the same basic spreadsheet over and over just adding the new data for each period. For example monthly budgets, sales figures, annual rainfall or monthly planners. The spreadsheet may have been through several tweaks to eventually arrive at the version that works best for you. When you arrive at this point you don’t want to create the spreadsheet from scratch for each new period which could be daily, weekly, monthly or annually. This would be time consuming.

Ok let’s go through creating an excel template, step-by-step

  1. Select the existing spreadsheet including all headings and formulae.
  2. Right click- Copy
  3. Open a new Excel workbook
  4. Select a cell near the top left –right click-PASTE. Choose the paste option that shows the spreadsheet exactly as you copied it. Usually first paste option.
  5. Carefully select only data in the spreadsheet and press DELETE
  6. Check formulae are still functional by entering a few test figures – if OK then delete these figures.
  7. Save workbook by clicking SAVE-AS then give it a file name such as ‘Rainfall template’. Then under file name ‘SAVE AS TYPE’ drop box select ‘EXCEL TEMPLATE’ and SAVE
  1. Now, by default, Excel saves templates in C:Documents\Custom Office Templates, but you can save them wherever it makes sense to you.
  2. To change the save location you will first (before saving the template) need to create a new folder in File Explorer eg Excel Templates. Then On the “Excel File” menu, click the “OPTIONS” command. In the “Excel Options” window, click the “SAVE” category in the left column.
  3. On the right, you’ll see a “Default personal templates location” box where you can type a path to a custom save location for templates. There’s no “Browse” button for some reason, so type the full path to the folder you want to use or copy and paste the location from a File Explorer address bar.
  1. To use this template you can start a new work book by double clicking on your template file. If you want to add the template as a spreadsheet in an existing work book then select and right click-COPY the open template spreadsheet. Then go to your existing work book, open a new spreadsheet, select a cell near top left. Then right click-paste. Right click the sheet name click rename and give it a name like “Rainfall Template” and perhaps move it into the position of the first spreadsheet in that work book.
  2. Start entering the new data.
  3. In this example (Rainfall) I will open a new spreadsheet for each year to copy the template into a new spreadsheet (period). Right click on the template then select from this menu MOVE or COPY. Then select (MOVE TO END) and tick the box “CREATE a COPY” This will create a new spreadsheet to the right which will be named template(2). Right click and rename it according to your next period eg 2021 etc.

This video explains all this in more detail:

Here is more stuff on Excel:

Comments are most welcome, try this and send us some feedback☺️ Why not visit our YouTube channel and browse through our other videos? http://www.youtube.com/c/AnnErasmus

Short Disclaimer/disclosure: My videos/articles are for informational and educational purposes only, you should consult with a professional before attempting anything seen or done in my videos/articles. Please read full disclaimer here: https://basiclowdown.com/disclaimer-disclosure-page/