If you want to get the most out of Excel, you’ll need learn to create your own template. We’ll show you how to add fields and formulas that perfectly suit what you need Excel to do with your data.
Why Create a Custom Excel Template?
Excel templates can be used for just about anything. A lot of people use them for finances (you can make a template for a simple Excel budget, for example), but Excel has many other potential uses.
You could whip up a template to help you keep track of books and games you’ve loaned out each month, for example. Or to manage an invite list for regular parties. Even a custom calendar that works especially well with your family’s schedule is a possibility.
You can choose from pre-made options, but if you know how to edit them or create your own from scratch, you’ll get even more out of Excel templates.
You’ll benefit most from creating a custom template, if you have to create the same spreadsheet over and over again, like a monthly report or a weekly calendar. But even if you only have to create a spreadsheet once a year, creating a template for it could save you some time.
How to Create an Excel Template from Scratch
For this example, we’ll create a custom Excel template from a blank worksheet. I’ll be making a monthly book lending spreadsheet to show you how the process works. Let’s get started!
Here’s the information I want represented in my spreadsheet: the name of the month, the name of the lent book, the person I lent it to, the date they took it, the date they promised it back, how many days they have left, and the number of books I currently have out:
The month, days left, and number of books that are currently out on loan have all been calculated using formulas. Just to make sure it’s working, let’s put in some data.
The month is brought in correctly, the days left are accurately calculated, and the number of books out reflect the value in the “Returned?” column. Looks good.
How to Save Your Custom Template
After clearing out the information that doesn’t need to be there (books, lendees, and dates), we can save it as a template. Hit File > Save As and select Excel Template (.xltx) in the Format drop-down. The Where dropdown will automatically change to the My Templates folder, which is where you want it to be.
How to Open a Template
Now, when you want to open up a blank copy of the template, just go to File > New from Template, and select My Templates from the left sidebar.
(Depending on your version of Excel, this process might differ slightly, but it should be pretty easy to figure out how to open a template.)
Now you’ll have a fresh copy of the worksheet that you created and saved, ready to be used, and without having to create all of your column headers and formulas again.
Turn a Worksheet Into a Template
Creating a spreadsheet from scratch for a template is easy, but you can just as easily turn an existing sheet into a template. Just delete any information that you don’t want included in the template, leave everything else, and save it as a new template in the same way as explained above.
For example, if you download a personal budget spreadsheet, and it includes a line for a mortgage, but you don’t have a mortgage, you could delete that or replace it with something else and then save it as a template, saving you the time of customizing it every month. (Just be sure that anything you delete isn’t going to change how the included formulas work.)
Download & Customize Off-the-Shelf Templates
If you don’t have a worksheet you can turn into a custom template, you can also use an off-the-shelf template and customize it to your needs. You can find free templates under the following resources:
Microsoft Office also features free templates. In Excel, you see a selection of templates whenever you go to open a new document. You can use the search for online templates search box to find what you need. Or you can browse all templates available for Excel under templates.office.com.
Your Best Excel Templates
As you can see, making an Excel template is an easy process that could save you a ton of time in the long run, especially if you find yourself recreating the same spreadsheet on a weekly or even daily basis.
What have you used custom Excel templates for? Share your best ideas so we can all save some time and be more efficient!
Image Credits:Hand holding a rubber stamp by RTimages via Shutterstock