WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Create Templates in Excel

Create Templates in Excel

By Madhuri ThakurMadhuri Thakur | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Create Excel Templates

Creating Excel Templates helps you to avoid the cumbersome repetitive tasks and help you to focus on the real deal. These templates can be either standard, those which are already present in MS Excel for their ready-made use or you can create your own template and utilize them later.

How to Select Standard Excel Template?

Open an Excel workbook and Go to File >- New >-

You will see a similar screen like the below screenshot.

Create Template Example 1

It gives you the option to select from some pre-defined options divided by various categories. You can choose the one relevant to your requirement and double click on it to use that template. Suppose I choose the Academic Calendar (any year) and double-click on it.

Create Template Example 1-1

You will see a screenshot like the below one. You can see here that you can simply change the year, and you can start using it for a different year than the one on which it was built, giving you the flexibility to use whenever you want.

Create Template Example 1-2

Here, you will need to make one other change, which is “select the week start day” for a month. The screenshot is below.

Create Template Example 1-3

However, if you want to see a preview first and then decide whether you want to select that template or not, do a single click.

Create Template Example 1-4

If you are satisfied with it, click on the create button to proceed with it. Else close it by clicking on the “X” symbol in the dialog box and proceed again. You can also move forward and backward to select the required one among the standard templates by clicking on the two symbols on the side of the dialog box (marked above in black).

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Create Template Example 1-5

Like this, in any template that we choose, there will be some things that we need to tweak to reuse it for our purposes.

How to Create a Custom Excel Template?

This will be like the way copies are done from the original piece. We take the utmost care in implementing the changes in the original piece, making sure all the formatting, headers, placeholders, etc. are perfectly done. Please devote a sufficient amount of time here, as anything and everything (example, style, formatting, graphics, etc.) related to this workbook will be replicated in its entirety.

What changes can be saved when you create a custom template?

Basically, anything can you can do in a workbook can be saved in a template. Some of these include:

  • Data Validation in Excel (Selecting values from a list)
  • Having a macro button
  • Some cells formatted in the way we like (for e.g., cells highlighted in yellow)
  • Protect a range of cells from making any changes to it
  • Hide certain rows and columns, charts, images, excel hyperlinks, etc.
  • Merge certain cells in excel or wrap excel text in some
  • Change the cell type to a specific format (e.g., Date format in a cell)
  • Freeze excel cells so that header columns are always visible.

In the below screenshot, I have shown some of these examples in an excel template I have created.

Excel Template

  • The cell B3 will only take inputs from a pre-specified list. (Go to cell B3, then go to the “Data” tab. Select “Data Validation.”

Create Template Example 2

In the drop-down, select the first one, “Data Validation.”

Create Template Example 2-1

  • Then make the following changes as shown in the below screenshot.

Create Template Example 2-2

A Drop-Down list is created.

Create Template Example 2-3

  • Cells A3 to A6 will be highlighted in yellow. (Select Cell “A3:A6”, go to “Home Tab,” then click on “Fill Color”)

Create Template Example 2-4

  • The image will be there at the place shown (For this, go to the cell where you want to insert the picture, then go to the ‘Insert’ tab, click on ‘Pictures’ and then upload the image where it is stored on your computer)

Create Template Example 2-5

A Picture is uploaded in that Cell.

Create Template Example 2-6

  • A button is present, which on clicking will run the required macro. (Go to the cell where you want to place the button, then go to the “Insert” Tab, go to “Shapes.”

Create Template Example 2-7

  • Choose the shape; Right-click on it to select “Edit Text” if you want to write anything in it.

Create Template Example 2-8

Write, “Click me to run the Macro.”

Create Template Example 2-9

  • Once done, right-click again and choose “Assign Macro.”

Create Template Example 2-10

It will open a dialogue box wherein you can assign the relevant macro which you have written in VBA.

Create Template Example 2-11

  • Rows 8 to 12 are hidden. (For this, select rows 8 to 12).

Example 2-12

Go to the ‘Home’ tab, then go to ‘Format,’ then to “Visibility,” then to “Hide & Unhide,” then “Hide Rows.”

Example 2-13

Similarly, in the above screenshot, apart from the ones mentioned above, you can see:

  • Cell F2 will always be in currency format. (For this, go to cell J2, do “ctrl+1” then change the type to “Currency”)

Example 2-14

  • Cells C15: D16 are merged into one. (For this, select cell C15: D16 and then click on “Merge & Center in Excel“)

Example 2-15

  • A worksheet is a freeze at cell C19 so that despite scrolling down, contents on row 19 will always be visible. You can use this when you want to always see the headers in your worksheet. (For this, go to cell N20, then go to the “View” tab & click on “Freeze Panes.”

Example 2-16

The output is shown below.

Example 2-17

Now, assuming we have done everything that needs to be done in our workbook, I will show you the process that needs to be followed to save this created template and utilize it the next time.

How to use Created Excel Template?

Once you have created your own templates and are made the changes to the workbook or worksheet, click “Save As Type” & you can save it in the format ‘Excel Template (*.xltx)’ if there is no macro. (Using Office 2016). If a macro is present, use the format Excel Macro-Enabled Template (*.xltm)

The screenshot is shown below:

Save as

We saved it as ‘xltm’ since there was a macro present.

  • Now that we have saved the created excel template, we can utilize it like the way I showed, when selecting a standard template. Open an excel workbook.
  • Go to File >- New >-

You will see a similar screen like the below screenshot.

Personal

You can see a “PERSONAL” category adjacent to the “FEATURED” one. All your personal templates will be present here.

Go to the Personal one, and double click the template “personal template” (the name that you gave to your template). On clicking it, you will see a workbook, with all the initial settings already present, which you don’t need to do again.

See the screenshot below for your reference.

Personal template

Things to Remember

  • Using an excel template is about creating a perfect first copy, and then duplicate it many times. So, invest an awful lot of time in designing the first copy.
  • Depending on whether a macro is present or not in your template, save it accordingly. If the macro is present, save it as the “.xltm” file. Otherwise, you can save it in the “xlt” excel extension.
  • The created Excel templates will be present in a different section called “PERSONAL” apart from the already present ones in the “FEATURED” section.

Recommended Articles

This has been a guide to Create Templates in Excel. Here we discuss how to create a Custom Excel Template along with practical examples and a downloadable template. You may learn more about excel from the following articles –

  • Calendar Template in Excel
  • Excel Inventory Template
  • Creating a Dashboard in Excel
  • Combination Charts in Excel
8 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

Coursera IPO Financial Model & Valuation Free Download