You can create template with Excel , saving a lot of time creating custom tables or invoice schemas that I usually recreate each time I use it or even worse , you risk losing because you use as a base a simple Excel file created previously (admit it, how many times have you happened to overwrite it ?!).
The answer is yes! Also in Excel you can easily create templates to use as a basis for creating new documents with styles other than standard spreadsheets, and today I am here to explain how to do it step by step. Up, rolled up your sleeves and discover with me how to make a template with Excel, it takes very little!
For the tutorial I used the 2019 version of Office that, at the time of writing, is the most recent, but the indications in the article are to be considered valid for the editions of the most dated suite, from 2007 onward (all those equipped with the “Ribbon” card interface). In addition, I will mention to some features of Excel Online and Excel for Android and iOS, always concerning the templates to be used to create new workbooks. Ready to start? Yup? Perfect! Then roll up your sleeves and follow the directions that you find below. Good reading and good work!
Make a template with Excel on Windows
If you want to learn how to make a template with Excel and use a PC equipped with Windows, the first step you need to do is start the program through its link in the Start menu (the one accessible by clicking on the flag icon located in the lower corner to the left of the screen) and start editing the spreadsheet with all the items you want to use as a basis for creating your next documents.
For example, you can create a fencing with borders, selecting the affected cells with the mouse and clicking on the button to add borders on the Home tab, add formulas for the automatic creation of sums or the automatic execution of calculations based on the values that you will enter in the cells of the sheet etc.
To change the type and style of cells, you must select the cells you want to change with the mouse, right-click in any of them and click on the Format cells in the menu that appears. In the window that opens, use the options in the Number tab to define the type of content of the cells (e.g. number, date, variable, etc.), the items in the Character tab to change the writing character of the cells, Edge to add them an edge, and so on.
When you have finished setting your preferences, save everything by clicking OK. If you’re having trouble setting up automatic calculations or other cell properties.
Now you are ready to save your template starting from the spreadsheet you just made. Click, then, on the File button located at the top left, select the item Save As from the menu that appears on the side and presses the Browse button.
In the window that opens, select the Excel template option or Excel 97-2003 template (if you want to create a template that is perfectly compatible with versions of Office prior to 2007) from the Save As pull-down menu and click the Save button to save your template in the folder with the Office templates.
If your document contains macros that you want to have automatically activated, set the Template option with macro activation of Excel instead of “Excel template” or “Excel 97-2003 template” from the Save As menu.
In case Excel does not automatically open the folder with in templates when you select the Excel template as the file type for saving, select it manually by going to C: \ Users \ Your Name \ App \ Data \ Roaming \ Microsoft \ Templates or C: \ Users \ Your Name \ Documents \ Office Custom Templates.
If you want to customize the folder in which Excel custom templates are saved, click on the program’s File button (top left) and select the Options item from the side menu. In the window that opens, click on the Save item (in the left sidebar), type the path where you want to save the templates in the Default personal templates path field and click on the OK button to save the changes.
Finished! At this point, you can recall the newly created Excel template at any time by opening the program and selecting it from the Personal tab of its home screen.
Alternatively, if you are already using Excel, click on the File button located at the top left and select the item New from the menu that appears. Then click on the Personal tab and select the name of the template you created to use it as the basis for a new document.
If you cannot see the Personal tab in the Excel menu or if you did not see the template you just created, try following the instructions below.
- Press Win + R on the PC keyboard to bring up the Run Windows … panel and give the command %appdata%\Microsoft\Templates\;
- Copy the address of the newly opened folder;
- Open Excel, go to the File menu > Options> Save and paste the path to the previously copied folder in the Default personal templates path field.
Finally, save the spreadsheet template again and it should appear on the initial screen of the program.
Make a template with Excel on Mac
If you use a Mac, you can create an Excel template by starting the application and choosing to create an empty workbook. Then customize the spreadsheet with colors, borders and formulas of your interest.
To do this, click on the File item in the top bar and choose the Save as template option from the menu that opens. At this point, choose whether to save the file as an Excel template, template with Excel macro activation or Excel 97-2004 template by selecting one of the options available in the File format drop down menu, make sure that in the menu located in there the folder is selected Templates, type the name you want to assign to the template and click on the Save button to complete the operation.
If you do not select the Templates folder in the Set in menu, click the adjacent arrow and go to the / Users / yourname / Library / Group Containers / UBF8T346G9.Office / User Content.localized / Templates.localized path.
Once you have saved your template, you can use it by selecting the New item from the Excel home screen (in the left sidebar) and then double-clicking on its icon. If you are already using Excel, you can access the same menu by selecting the File> New from template item from the top menu bar.
Make a template with Excel Online
As mentioned in the opening of the post, there is also a version of Excel Web based that you can use for free from the browser, provided you have a Microsoft account. Unfortunately, it does not include all the features of Excel for Windows or MacOS and, among the missing functions, there is precisely that related to saving custom templates. However, many pre-set templates are available and no one is forbidding to load simple XLSX or XLS files for use as templates.
If you want to try Excel Online, connected to the main page of the service, log in with your Microsoft account (if necessary) and choose whether to create a new document starting from one of the available templates or to use an existing document as a template. In the latter case, you must first load the XLSX / XLS file to use as a template on your OneDrive space, the Microsoft cloud storage service.
Then connect to the OneDrive start page, click on the Go to My OneDrive button, log in with your Microsoft account (if necessary) and drag, in the browser window, the file to use as an Excel template. Once the upload is complete, double-click on the file you just uploaded and it will open in Excel Online.
After editing a file, the changes applied to it will be automatically saved to OneDrive, so the original document will be overwritten. In this regard, if you do not want to lose your original “template”, I recommend uploading it in duplicate or using the copy function in OneDrive (accessible by right clicking on the file to be duplicated) to create a copy, before modifying it.
To download the modified documents to Excel Online on the PC, click on the File button located at the top left, select the item Save as from the menu that opens and click on the button Download a copy. Easier than that?!
Make a template with Excel on smartphones and tablets
Microsoft Excel is also available as an app for Android, iOS and Windows 10 Mobile, it’s free for all devices of 10.1″ or less (otherwise it requires the subscription to an Office 365 subscription, starting from 7 dollar / month) but, like Excel Online, offers only the possibility to use preset templates and to load existing XLSX / XLS files to be used as templates.
That said, to use the app, you do not have to do is download to your device (unless it is already installed on it), start it and log in to your Microsoft account. At this point, choose whether to create a new workbook using one of the available templates or open an existing file using the Open tab.
You can upload files from the internal storage of the device and from different cloud storage services, such as OneDrive, Dropbox and Google Drive. To access a cloud service from Excel, select the Open tab and tap the Add a location item. Changes made to files are saved automatically. In the case of the creation of a new document, instead, you must press the back arrow (top left) and type the name to assign to the latter.