How To Create Custom Excel Worksheet & Workbook Template – Step By Step Guide

Creating or designing a custom worksheet template in MS Excel is simple and helps to save a lot of time and stress especially if you have to create different worksheet of the same format over and over again. Using this, operation, you prepare the first worksheet and save it as a template. You can create different worksheet templates for different purposes like the payroll, the Students’ grade list, Sales record and report, semester GPA calculation, loan payment or amortization worksheet templates, etc. You can also select from any of MS Excel’s pre-designed templates. I will also show you how to customize the Quick Access tool bar and tabs in Microsoft Excel.

CREATING CUSTOM WORKBOOK AND WORKSHEET TEMPLATES IN MS EXCEL

Now, I will give you a step by step guide on how to create a custom worksheet template for any version of Microsoft Excel. Note that Excel custom templates are saved with the file extension “.xltx”.

You can also create a worksheet or workbook template from some default Excel Workbook templates as you will see below. Some of the pre-designed MS Excel templates in Excel 2013 include:

Billing Statement, Blood Pressure Tracker, Expenses Report, Loan Amortization, Personal Monthly Budget, Sales Report, Time Card. But you can get more updated Excel templates at https://templates.office.com/en-us/templates-for-Excel. For all MS Office packages template, click Here.

This is part 1 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous chapter (chapter 7), I introduced the what-if analysis tools and explained how use the scenario manager and data tables in MS excel. Also see the full tutorial course content of this Microsoft Excel Tutorial Series.

To Create a New Custom Worksheet Template in MS Excel:

1. Open a new workbook.

For those using Excel 2007 and lower versions, you have three workbooks by default. Delete two of the default worksheet (probably Sheet 2 and Sheet 3).

For those using Excel 2013 and newer versions, you don’t have to border because you have only one worksheet by default.

To see the difference between a workbook and a worksheet, go to chapter 1: part 2. 

2. Design the worksheet inserting the static data (data common to all the worksheet that you will create from this template).

For example, a template for the monthly sales record will contain titles like: PRODUCT ID, PRODUCT NAME, QUANTITY BOUGHT, COST PRICE, QUANTITY SOLD, SALES PRICE, QUANTITY IN STOCK, etc.

Leave columns that will contain dynamic data (data that will vary from worksheet to worksheet like products name, sales price, cost price, etc.) empty.

3. Insert the formulas for calculating QUANTITY SOLD in the cell formed by the intersection of the first record and the QUANTITY SOLD column using their cell references in the formula. 

For example, in the worksheet sample shown below, the QUANTITY SOLD is in column E, QUANTITY BOUGHT is in column C and QUANTITY IN STOCK is in column G of the worksheet, then the formula for calculating the QUANTITY SOLD would be: QUANTITY BOUGHT – QUANTITY IN STOCK. 

So place your cursor in cell E3 which is the intersection of column E and row 3 (the first record) and type the following formula: =C3 – G3 then press the Enter key.

A sample of a monthly sales report is shown below.

From the sample MS Excel worksheet template above, the static data columns are the PRODUCT ID column (A) and the dynamic data columns are the PRODUCT NAME column (B), the QUANTITY BOUGHT column (C), the COST PRICE column (D), the QUANTITY SOLD column (E), SALES PRICE column (F) and the QUANTITY IN STOCK column (G).

4. For the QUANTITY IN STOCK column, type the following formula in cell G3:
=C3-E3 then press the Enter key. A circular reference warning dialogue will appear. Just click OK.

5. Click the File tab, then click Save As. Then specify the save location. This prompts the Save As dialogue box.

6. Type in a unique file name for the worksheet template in the File Name text box.

7. Click the drop down arrow at the extreme right of the Save As Type dropdown menu and select the Excel Template option from the menu option as shown below.

8. Click the Save button and the template file will be saved to a default folder called Custom Office Templates.

To Make Use of your Newly Created Custom Excel Worksheet Template:

1. Don’t close the currently open MS Excel workbook (not worksheet), go to chapter 1 to see the difference between Excel workbook and worksheet.

2. Right click the worksheet name at the bottom left of the window and select Move or Copy option as shown below.

3. This prompts the Move or Copy dialogue box. Click the To book: drop down menu and select (new book) from the resulting menu to create a new workbook from the previously created Excel custom template.  Then check the Create a copy check box as shown below and click OK.

4. MS Excel automatically creates a new workbook with only one worksheet based on the previously created custom template.

To Create Another Worksheet Inside the Same Workbook Based on the Custom Template: 

1. Right click sheet 1 of the newly created workbook that contains the custom template.

2. Select Move or Copy option. This prompts the in the Move or Copy dialogue

3. Under the To book: drop down menu, select the name of the current workbook. Under Before sheet:, select (move to end) option. Also check the Create a copy check box. Then click OK as shown below.

To Create a Worksheet Based on the Default or Pre-designed MS Excel Workbook Template:

1. Create a new workbook by clicking the File tab > New > Blank workbook.

2. Right click Sheet 1 at the bottom left of the workbook window and select Insert from the drop down menu options. This prompts the Insert dialogue box.

3. Select any of the MS Excel’s pre-designed templates under the Spreadsheet Solutions tab and click OK as shown below.

4. MS Excel now inserts your selected pre-designed template. You now have to edit the template, deleting the unwanted parts and formulas and customizing it to your own taste.

NOTE:

To insert another pre-designed template in another new worksheet, repeat steps 2 to 4.

This is the end of part 1 of chapter 7 of this online tutorial series. 

In the next part (part 2), I will explain how to create excel worksheet template and data form in MS Excel.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.