Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Create Templates in Excel (Table of Contents)
Create Excel Templates
Ever wondered to utilize your previous work to get an advantage on your next piece? If you have spent your time in creating similar works multiple times or need to follow the exact formats in multiple workbooks, then Excel Templates is your answer to save your precious time. The examples include your dashboards, event management reports, inventory planning in a warehouse, etc. You need to do this time and again, so why not put your best foot forward once and then just do some tweaks to utilize it again for a different purpose.
This is where you will find the concept of “creating excel templates” useful. It 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.
You will be given a short introduction on using the standard templates present in the MS Excel file, and then we can move on creating our own templates, configured to our requirements.
#1 – 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.
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-clicked on it.
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 for, giving you the flexibility to use whenever you want.
For this template, you will need to make one other change which is “select the week start day” for a month. The screenshot is below. You will see this once you open the template.
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. You will see a screenshot as below.
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 2 symbols on the side of the dialog box (marked above in black).
Like this, in any template that we choose, there will be some things which we need to tweak to reuse it for our purposes.
#2 -How to Create a Custom Excel Template?
This will be like the way copies are done from the original piece. We take utmost care in implementing the changes in the original piece, making sure all the formatting, headers, placeholders etc. are at 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 custom template in Excel?
Basically, anything can you can do in a workbook can be saved in a template. Some of these include:
- Data Validation (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 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
- The cell B3 will only take inputs from a pre-specified list. (Go to cell B3, then go to “Data” tab. Select “Data Validation”.
In the drop-down, select the first one “Data Validation”.
- Then make the following changes as shown in below screenshot.
A Drop-Down list is created.
- Cells A3 to A6 will be highlighted in yellow. (Select Cell “A3:A6”, go to “Home Tab”, then click on “Fill Color”)
- 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)
A Picture is uploaded in that Cell.
- 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”.
- Choose the shape. Right click on it to select “Edit Text”, if you want to write anything in it.
Write “Click me to run the Macro”.
- Once done, right click again and choose “Assign Macro”.
It will open a dialogue box wherein you can assign the relevant macro which you have written in VBA.
- Rows 8 to 12 are hidden. (For this, select rows 8 to 12).
Go to the ‘Home’ tab, then go to ‘Format’, then to “Visibility”, then to “Hide & Unhide”, then “Hide Rows”.
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”)
- Cells C15: D16 are merged into one. (For this, select cell C15: D16 and then click on “Merge & Center”)
- 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 “View” tab & click on “Freeze Panes”.
Output is shown below.
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 excel template and utilize it the next time.
#3 – How to Use Created Excel Template
Once you have created your excel 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:
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.
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.
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 according. If the macro is present, save it as “.xltm” file. Otherwise, you can save it in “xlt” file extension.
- The created excel templates will be present in a different section called “PERSONAL” apart from the already present ones in the “FEATURED” section.
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 –