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.
=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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!