How to create sheet template and apply it in Excel?
Worksheet templates offer a practical solution for Excel users who frequently need to recreate similar worksheet formatting, such as standardized reports, recurring schedules, or organizational layouts. By designing a template, you can streamline your workflow and avoid repetitive formatting tasks, saving time and ensuring consistency across multiple workbooks. This guide explains how to create a worksheet template, how to insert it into other books, and introduces advanced approaches—such as automation with VBA and dynamic customization using formulas—to further enhance efficiency and adaptability within different scenarios.
Create a sheet template and apply it in Excel
VBA Code - Batch insert sheet templates
Create a sheet template and apply it in Excel
Follow these easy steps to design and reuse a worksheet template:
1. Start with a new Excel workbook and keep only one worksheet, deleting any extra sheets.
2. Format and edit the remaining worksheet exactly as desired for your template layout. This can include setting column widths, entering example data, applying styles, and setting up formulas as needed. For example:
3. Save your formatted worksheet as a template, choose File > Save As > Browse. In the Save As dialog, enter a descriptive name for your worksheet template in the File name field. Next, open the Save as type dropdown and choose Excel Template (*.xltx).
Tip: You don't need to change the default location; Excel automatically saves templates to the Templates folder. This ensures easy access whenever inserting new sheets.
4. Click Save to finish creating your worksheet template.
5. To use your new template, open any Excel workbook, right-click a worksheet tab, and select Insert.
6. In the Insert dialog box that appears, choose your template sheet from the General tab. If there are multiple templates, select the one matching your needs:
7. Click OK and the worksheet template will be instantly inserted into your workbook.
Note: To remove a worksheet template from your available options, open the Insert dialog, locate the template, right-click it, and select Delete as shown below:
When creating templates, it's helpful to include frequently used titles, labels, formulas, conditional formatting, and even sample data as a demonstration. This supports quick adaptation for future use. If your organization uses shared network drives, clarify with IT where templates should be stored for easy team access.
If the template fails to appear in the Insert dialog, verify that it is saved as an .xltx file and placed in the correct Templates directory. Occasionally, Excel may require a restart for the latest templates to display correctly.
VBA Code - Batch insert sheet templates
In cases where you need to insert a sheet template repeatedly across multiple workbooks, or automate addition to several positions within a workbook, using VBA macros can streamline the process, eliminate manual steps, and increase consistency. This is especially useful in large projects or routine monthly reporting.
Advantages include automation of repetitive insertions and error reduction, while potential users should be careful to back up data before large batch operations.
1. Access the VBA editor by clicking Developer Tools > Visual Basic. In the window that appears, click Insert > Module, then copy and paste the code below into the module:
Sub BatchInsertSheetTemplate()
Dim templatePath As String
Dim targetWB As Workbook
Dim i As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Specify the full path to your .xltx template file
templatePath = Application.GetOpenFilename("Excel Template Files (*.xltx), *.xltx", , "Select Template", , False)
If templatePath = "False" Then Exit Sub
' Loop to insert template into active workbook multiple times
Set targetWB = ActiveWorkbook
For i = 1 To 3
targetWB.Sheets.Add Type:=templatePath
Next i
End Sub2. Click the 
run button to execute the macro. A dialog box will prompt you to select the template file. After selection, the code will automatically insert three copies of your sheet template into the current workbook. If needed, change the number 3 in the code to any value for custom batch quantity.
When running this macro, make sure the workbook is saved and not protected by password; protected workbooks can block macro actions. Inserted sheets will be placed before the active sheet. If you wish to automate insertion into several workbooks, open each workbook one by one before running the macro, or adjust the code accordingly for batch processing.
If the macro does not insert sheets as expected, check that the template path is correctly chosen and that macro settings (such as macro security) allow code to run.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in