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.
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. 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 excelAlso 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.
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:

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.
Select move or copy option
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.
Select new book and check the create a copy option in the move or copy dialogue box
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.
Select current workbook name and select move to end in the move or copy dialogue box

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.
Select any of the predesigned templates of your choice
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.

Recommended Entrepreneurship Books

the passionate entrepreneurs strategies book by buzzer joseph

The Passionate Entrepreneur’s Strategies: Learn how to discover business ideas that will be successful, grow multiple online income streams, make money from home, attract new and retain customers, applications of SWOT analysis in any business, skills that will help you succeed as an entrepreneur, self-discipline, etc. Lucrative Business Ideas Series Book 1. By Buzzer Joseph. Read more about the book.

how to make money from blogging and affiliate marketing book by buzzer joseph

How to Make Money from Blogging and Affiliate Marketing: Learn the Step by Step Guide of Creating, Growing and Optimizing your WordPress Blog from Scratch for Search Engines (SEO) and How to Make Money through Google AdSense, Affiliate Marketing and Other Strategies for Earning Passive Income Online. Lucrative Business Ideas Series Book 2. By Buzzer Joseph. Read more about the book.

how to make money self publishing kindle books by buzzer joseph

How to Make Money Self-Publishing Kindle Books for Beginners: Learn the Step by Step Guide to Self-Publish a Best Selling Book on Amazon KDP, How to Design Book Covers with Adobe Photoshop, Format, Edit and Proofread your Book Manuscript with Microsoft Word, Promote your Book on Various Online Platforms and Earn Monthly Passive Income Online. Lucrative Business Ideas Series Book 3. By Buzzer Joseph. Read more about the book.

diy grid and off-grid solar pv installation guide by buzzer joseph

Complete Solar PV System Installation Guide: DIY guide on how to install grid-connected and off-grid solar PV systems, how to build solar-powered electric cars, convert conventional cars to electric cars, online and offline ways to make money from solar energy sector (including stocks), electrical wiring and protection system installation guides, plus free resources for skill acquisition and personal development. Lucrative Business Ideas Series Book 4. By Buzzer Joseph. Read more about the book.

how to make money from cryptocurrency trading and investments by buzzer joseph

How to Make Money from Crypto Trading and Investing for Beginners: Step by step guide with screenshots on the basics of blockchain technology, bitcoin and altcoins, crypto security tips, cryptocurrency market analysis (FA and TA), long term and short term trading, how to calculate your profits in excel, legit ways to make money with cryptocurrencies, DeFi yield farming and staking, how to use any centralized and decentralized exchange platform and more, plus crypto investment bonus! Lucrative Business Ideas Series Book 5. By Buzzer Joseph. Read more about the book.

blockchain practical guide kindle book by buzzer joseph

Blockchain Practical Guide: Step by step guide with pictures, on how to monetize the latest blockchain technology trends: crypto trading and tools, especially Ethereum, DeFi, Staking, NFTs, Top 100 Play-to-Earn (P2E) and NFT games, Web 3.0, Metaverse, Virtual Reality (VR), Augmented Reality (AR), blockchain job opportunities, with crypto investment bonus! Lucrative Business Ideas Series Book 6. By Buzzer Joseph. Read more about the book.

how to make money with blockchain web3 apps kindle book by buzzer joseph and kelly joseph

How to Make Money with Blockchain Web3 Apps: Step by Step Guide with Screenshots and Videos on How to Make Money from Blockchain and Web3 Projects – NFTs, Play-to-Earn (P2E) Games, Move-to-Earn (M2E) Apps, Watch-to-Earn (W2E) Apps, SocialFi and GameFi Apps, with crypto investment bonus! Lucrative Business Ideas Series Book 7. By Buzzer Joseph and Kelly Joseph. Read more about the book.

No comments:

Post a Comment

WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!