In this video, learn how to design your own custom template from an existing spreadsheet file, including how to lock content on a sheet that is not to be disturbed.
- [Instructor] When you put into a lot of work into the design, the look and feel of your sheets here in Numbers, and you think it's a file you might want to keep using going forward with different data, for example, an annual budget is something that we'll use next year, and the year after, and so on. You might consider taking all of the work you've done in your file, and saving it as a template, so going forward, users will only have to fill in the blanks all of the design, the look and feel, will have been completed already.
That's what we're going to do in this chapter, beginning with starting off the design process, and we're going to do it with our Leaf and Mortar Budget file, 0901, if you're catching up. So we will end up saving this as a template, for now we're going to make the modifications to the actual file before we save it as a template. So let's look at some of the things that will need to be updated. Leaf and Mortar Annual Budget, you can see the year is 2018. Well that's something that will be adjusted, so we want people to go in here and change the year. We want them to be able to change the exchange rate.
Down below in this table, all of these figures actually come from formulas that contain data in these other sheets at the top. So we don't want people manipulating these cells, we want those locked, and that's the first thing we're going to do, and that is lock this table. Now before we lock it, I'm going to take out the year up here, where it says 2018, because once it's locked, people won't be able to go in here and change the contents. So, just click inside that cell after 2018, backspace with your delete key to take it out.
Next, because we're already in the table, we can go to the arrange tab over here in the inspector down at the bottom, click lock. As soon as you do, you can see the little x's around the outside instead of handles, that tells us this table's locked. And if we tried to go into one of these cells to make an adjustment, we can't. Of course, we can unlock it if we need to, but for now it's locked, and we're going to keep it that way. Down below we want people to add to the checklist, so we're going to go in here and take out anything that's already there from this year.
And up here, Leaf and Mortar Annual Budget, we could change the year 2018 to the word year, so people know they need to enter that, or they could just go in. This is not locked, we can go in here and change this anytime, it's a text box. All right, down below, we'll keep all of that information the way it is. Now it's time to go to some of the other sheets. Now the expense report, maybe going forward, that's not going to be part of this file. So we can actually remove it, click the arrow next to expense report, and choose delete.
We'll delete it all together, now we're down to three tabs. We do need these different tables because this data is actually being used in our profit loss summary table, that's where all the figures are coming from. So let's go to income details. We want people entering the month order line items, the estimated and actuals, the variance is a calculation that will be determined by the estimated and actual figures that are entered, prior year is input by the user, variance, of course, is a formula as well, as is the actual prior overview that's coming from this same cell.
So, we're going to take out anything that users would have to input. And we do that by simply clicking and dragging over the cells we want to delete. So we'll start at A2, January, go all the way across to the actual column in E and scroll all the way down to the bottom of our table, even the empty cells, just hit your delete key to remove that. And now you can see the only other thing is the prior year values that are entered, we'll select all of those, and all you have to do is click and drag from G2 all the way down to the bottom of our table, and hit your delete key to delete that.
The other figures we see are formulas, that's excellent. Now, there are some tricks we're going to talk about in this chapter for making it easier for people to enter data, giving them options for the month, the quarter, and the line items for example. For now, though, let's go to expense details because we need to do the exact same thing here. We need to click and drag from, in this case you can see A2 is where January starts in this table. We don't want the quarters, the categories, the line items, again, we'll make it easy for people to enter that data, but the estimated and actual figures are also something that people enter.
The variance is a calculation, so we're going to leave that column alone. I'll scroll all the way down to the bottom here in row 75, hit our delete keys. Now you can see we're left with some data, like prior year. The variance is a formula, as is the actual, so it's just this data here in column H. So, we'll drag from H2 all the way down to the bottom to remove that data, and we're now ready to make this a template. Notice all the figures showing up in our summary, all based on data that's been removed in these other sheets.
So, we'll continue working with our data and making it easier for people to enter data when they use this template once it's been saved as a template, we'll do that next.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Recall how to customize the user interface.
- Explain how to save a spreadsheet.
- Describe how to customize table cells and borders.
- Cite the steps for adding an image to a cell.
- Distinguish between different functions and explain how to use them.
- Explain how to format general chart attributes.
- Describe how to use interactive charts.
- Cite how to insert hyperlinks into a document.
- Recall how to add and arrange images.
- Summarize how to collaborate with iCloud.