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