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.
Easy guides on creating a custom MS Excel worksheet template

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.

This is part 1 of chapter 7 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the previous chapter (chapter 7) at http://www.microsofttut.com/2017/04/learn-what-if-analysis-tools-scenario-manager-and-data-tables.html, 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 at http://www.microsofttut.com/p/microsoft.html.

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 at http://www.microsofttut.com/2016/06/chapter-1-part-2-user-interface-and.html.

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 drop down menu and select the Excel Template option from the menu option as shown below.

Select the file format and click save

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:
Click http://www.microsofttut.com/2017/04/how-to-create-custom-excel-worksheet-template.html to view full tutorial post with images.

..