Creating an Excel template to simplify updating a monthly file

- [Voiceover] On this worksheet we're seeing some sales total for February 2016. Let's imagine you've named this file something along those lines, like "February 2016 Sales". And you've been working with this file every month and what you do is you create a new file every month in the following way. Imagine, it's early March, you've opened the February file. What do you do first? You change the heading here, perhaps you just double-click, change that to March. And then you've worked with it enough to know that these cells here are not formulas, so you just take out the data.

This is the February data. And we highlight just this data here. Everything on the perimeter there to the right and below, those are formulas, they're gonna look kinda funny right now as I press "delete" to get rid of the data. So, it's March, you're putting in the numbers here. I'll just make a few numbers here. And you do some more here and there. You get a phone call, you get interrupted, maybe you've gotten roughly half of it done or maybe you're suddenly called to a meeting. And what might you do? You might click the "save" button. And what have you done? You've wiped out the February data.

In other words, you've saved this February file. You've got some of your March data in it, maybe even all of it, but you've wiped out February. It's easy to say, "Well I'd never do that." Let me backtrack with Ctrl Z here. Why don't, as a matter of course say what we've been doing is working just fine. If it's a new month, it's March, we open February's file, we change this, take out the data. We save it as March. Easy, right? We'll never have a problem with that. Well you could, and you could go go down the path of actually wiping out your old data possibly.

Let's approach this a different way. If we wanted to save this as a template, what we could do is have no data here, keep the formulas in the perimeter. We might also, as we delete the data here, maybe make it green, as if to say, "Let's only do data entry on the green cells". We'll make it a light green. We can also get rid of this as well too, but keep it green. Now, if we save this as a template, we'll never run into that problem of accidentally opening the file and saving it under the wrong name.

So, let's save this as a template. File, Save As. And we don't want to save it as an Excel Workbook, we want to actually save it as, go to the drop down here, an Excel Template. You'll see it in the list right here. As the file extension "sltx". Click it. We'll give it a name. Different name, how 'bout "Monthly Sales" instead of "February 2016 Sales"? So we're saving a file, and it's a template called "Monthly Sales".

Now, let's imagine for the moment we don't need it. We'll close this. Ctrl W can close it quickly, we don't need that. At a later time we want to get to that template. If you go the "File" tab in the ribbon and choose "New", right here, you will see built-in templates. But if you've save any templates, as I just did, go to "Personal", and there's the Monthly Sales template. That certainly doesn't look like it there, the image, but that's what we want. We'll click it.

Now, notice that the file name at the top of the screen says "Monthly Sales1". If I save this mistakenly or quickly after having put in some numbers, or maybe having done nothing, I am not overwriting the template. You have to kinda, as we say, jump through hoops to wipe out the template. You can't do it by accident. We will put in our numbers here as needed. Now, I really don't wanna type in all the numbers here, but I'll do something I wouldn't typically do for data here, I'll just type =randbetween, and most of those numbers were between 50 and 300 so I'll just put in those numbers.

Obviously these are not correct, but I just want to fill the data to give it the look of how it might be when it's finished. So there it is. And since these are random numbers I'll use that trick of, with the right mouse button, drag them there to the right then back, with the right mouse button. Copy as Values Only. So I'm simply simulating the idea that maybe we typed in the entries here. And here I would type in, for example, probably the month. Maybe it's March. And I don't necessarily have the data, although I possibly could. Maybe I'll put in the last day of the month if the March data's there.

In other words, we'll have this worked out ahead of time, possibly even with picklist here if we wanted to. And actually, since it says "For the month of", we just wanna do "March 2016". There we go. Okay, so that's fine. Now, as I said, if anywhere in the process we mistakenly hit the Save button here, what happens? We are not on the path to doing anything with that template. We are on the path to saving this data as an Excel Workbook. And we would give it a sensible name, like "March Sales", or "March 2016 Sales".

We're saving it as a regular Excel Workbook. The template is unchanged, it's unsaved. And so, in the future when it comes to be April, we won't be opening the March data and typing over the numbers and saving it under a different name. We don't have to worry about that, we'll just go to the File tab, choose New, and Personal. There's our template. We open it. Now, there could be a time when you want to change the template. Maybe we're going to add a new state here or put in some new totals. We could certainly do that, and at that point, since we want to change the template, we would go back to the File tab, choose Save As, and not as a Workbook, we will save this as a template.

Right there. And we'll wanna make sure we use the same name that we used before. It's our Monthly Sales template, or whatever it might be. In other words, we will save our template when needed. In other words, we'd be updating it. We don't really have to do that here but we certainly could do it. The strange thing in the process here, at least strange initially, is the idea that when you choose File, New, and pick Personal, and then find your template, when you open it you are opening a copy of the template. It takes whatever the template name is and follows it with an additional number.

Now, maybe you're on extended vacation and it's early June and you wanna do April, May, both pretty much at the same time. Well, let's open another one. File, New, Personal, Monthly Sales. What name do we see at the top? Monthly Sales 2. So we could imagine how easy this is going to be, we'll never run the risk of wiping out our previous month's data. It's all we did to set up the template. Simply was to take out the data that was pertinent to that specific month, left all of our formulas in place along the perimeter, and we went through the process of File, Save As.

We saved it as a template, remember the first time we did that, saved it as a template. It's stored in a different location, and that location is recognized when you choose File, New. So the template idea had real merit when you're working with a file like this that needs to be updated on a regular basis.