Create/use custom or default sheet templates in Mac Excel
If you want to insert a new specific worksheet to your workbook with a nice layout and maybe a few formulas on a regular basis then you have two options.
Make a copy each time of a clean worksheet with your layout
- Select the sheet that you want to copy
- Right click on the sheet tab and choose "Move or Copy..."
- Select the sheet
- Check the "Create a copy" checkbox
- Be sure that the “To book” dropdown show the name of your file
- Press OK
Or much easier hold the Option key down and drag the sheet tab to the right or left with your mouse.
When you release the mouse button you will see the copy of the sheet.
Use a Sheet template(xlt, xltx or xltm)
How do I create one:
1: Open a new workbook and delete all worksheets except the first one.
2: Change all the things you want in this worksheet
3: Then use File>Save As Template to save the file with the name you want as a Template (xlt, xltx or xltm). When you save as a template it automatically want to save in the default template folder.
How do I find the template folder in 2016 and higher :
With code you can find the correct path in Excel with this code line
MsgBox Application.TemplatesPath
“You see the string .localized appears twice in the msgbox (/User Content.localized/Templates.localized/). You don’t see these in Finder because by default the Finder suppresses extensions; while VBA shows the raw folder names, in English.”
On my English Mac the folder location in Finder is : /Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/User Content/Templates
On my Dutch Mac in Finder User Content/Templates show up as Gebruikersinhoud/Sjablonen
Note : The library folder is a hidden folder, press the Option key when you click on Go in the Finder menu to see/open this folder.
1: Open a new workbook and delete all worksheets except the first one.
2: Change all the things you want in this worksheet
3: Then use File>Save As Template to save the file with the name you want as a Template (xlt, xltx or xltm). When you save as a template it automatically want to save in the default template folder.
How do I find the template folder in 2016 and higher :
With code you can find the correct path in Excel with this code line
MsgBox Application.TemplatesPath
“You see the string .localized appears twice in the msgbox (/User Content.localized/Templates.localized/). You don’t see these in Finder because by default the Finder suppresses extensions; while VBA shows the raw folder names, in English.”
On my English Mac the folder location in Finder is : /Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/User Content/Templates
On my Dutch Mac in Finder User Content/Templates show up as Gebruikersinhoud/Sjablonen
Note : The library folder is a hidden folder, press the Option key when you click on Go in the Finder menu to see/open this folder.
Add-in to give you a option in the UserInterface to insert a sheet template
Note : As far as I know there is no option in the UserInterface like in Excel for Windows to insert custom sheet templates in Excel for the Mac. Maybe you like this small Add-in that I created. This add-in add a menu item called "Insert Sheet Template" to the bottom of the menu that you see when you Right-click on a sheet tab on the bottom of your Excel screen.
Add-in that give you the option in the UserInterface to insert a sheet template
- Open Excel
- Update Excel (See button in the Help menu)
- Use Tools>Excel Add-ins... in the menu to open the Add-ins dialog
- Use the Browse button to select the add-in and choose Open
- Press OK
- Done
Note: If you store the Add-in in the Add-ins folder (the best place) it will be automatic in your Add-ins dialog list, read the information on this page if you want to know how to do this : Install an Excel Add-In in Excel for Mac
VBA code examples
But you can do it with VBA code if you want. Test the two basic examples below in Mac Excel:
Example to select the template you want to insert in your workbook :
With the macro example below you can select the template that you want to insert, you do not have to change anything in the macro before you try it.
Example to select the template you want to insert in your workbook :
With the macro example below you can select the template that you want to insert, you do not have to change anything in the macro before you try it.
The example below will insert the template MySheetTemplate.xltx after the last sheet in your workbook. You can use this macro for example if you want to insert a new worksheet based on this template every day. Be sure you have saved a sheet template with the name MySheetTemplate.xltx in the default template folder before you test the code.
Change the Default Excel sheet template
In Mac Excel you can insert a new default worksheet like this:
- Right click on a sheet tab and click on "Insert Sheet"
- Or you can click on the + sign on the right of the last sheet tab
- Or you can use "Insert>Sheet>Blank Sheet" in the Excel menu bar.
- Or you can use Shift F11
- Or you can use Home>Insert>Insert Sheet in the Ribbon
If you want to change the settings of this worksheet you can create your own default sheet template:
- Open a new workbook and delete all worksheets except the first one.
- Change the things you want in this worksheet (Font/background color, font and font size and ?)
- Then use File>Save As to save the file as a Template (xltx or xltm)
- As template file name use Sheet(or local name) and copy the file in your Excel Startup folder.
Every worksheet you insert now is based on this worksheet template.
Important : You must remove the extension of the file name so only Sheet is left as template name. Note: If you not see file extensions use Finder>Preferences..Advanced to show them(first checkbox)
Where is the Excel Startup folder ?
With code you can find the correct path with this code line
MsgBox Application.StartupPath
“You see the string .localized appears twice in the msgbox (/User Content.localized/Startup.localized/). You don’t see these in the Finder because by default the Finder suppresses extensions; while VBA shows the raw folder names, in English.”
On my English Mac the folder location in Finder is : /Users/rondebruin/Library/Group Containers/UBF8T346G9.Office/User Content/Startup/Excel
On my Dutch Mac in Finder User Content/Startup show up as Gebruikersinhoud/Opstarten
Note : The library folder is a hidden folder, press the Option key when you click on Go in the Finder menu to see/open this folder.
If you want it easy to find this folder follow the steps on this page
Setup your Mac for Mac Office
Change the default Workbook template
Tip: you can also make a custom default workbook if you want and add it in your Startup folder, you must name the template file Book(or local name) (remove the extension).