Version 4.2.6.2

The last few tutorials have looked at the techniques you need to master to use Styles and Templates effectively, but putting these into practice is essential to understanding them, I believe. So it is time for us to actually built a Template that incorporates a few styles and put the whole package together. For my example, I am going to create something useful for a consultant who needs to keep track of time for billing customers.

The first step is always to create the Template. This must be first because the Template must contain the Styles you create. If you started with the Styles, where would you put them? You could just open a spreadsheet and do all of your work and save it as a Template when you are done, but I actually prefer to create the Template first, then edit it as I go. I am less likely to make a mistake that way or lose my work. So I will open Calc to a blank spreadsheet, then go to File–>Templates–>Save As Template. In the window that opens, I click the Save button, then give it a name. I called mine “Billable Time”. Then I go to File–>Templates–>Manage, which opens the Template Manager. I select the Spreadsheets tab, then the Billable Time Template, and click the Edit button. Now when I look up the Title Bar in Calc, I see that I am in Billable Time.ots, which is exactly what I want to see. Now anything I create is in my Template.

In creating my Template, I will add contents to various cells and create Styles as needed. But the first step is to check my Page styles. As we discussed when looking at Page Styles and Page Settings, I have modified my Default Page style in a few ways from what you might see out-of-the-box. My Default style is set for Landscape, and has the Grid and the Column and Row headers turned on by default, for instance. (See the above link for a full discussion). I don’t foresee any need to change that right now, so I will leave that.

Next, I will put in my Headers and Footers. I go to Edit–>Headers & Footers, and I add a Header in the Center area that says Billable Hours By Month, and set it to Arial, Bold, 14 point. For Footer I already have what I want from customizing my Default Page style: Left contains the file and path, center the page count, and right has the date. These are fields that pick up their values automatically so I don’t need to think about it, which is why I put them into my Default Page style. The thing to keep in mind about these Header and Footer areas is that they show up when the document is printed, but not when it is viewed on a screen, so don’t forget to put in some info on the spreadsheet body as needed.

Now we need to start on the sheet itself. We want to have the appropriate text labels and such in the Template so that when we create a new spreadsheet from the Template it will already have the standard text in place and we just need to fill in the data. So I will start by merging and centering Cells A1 through E1, entering the text “Billable Hours for”, and selecting the Heading style for it. The Heading style is set to make it Bold, Italic, and 16-point, but I think I want to customize the just a little, so I click on the style, then right-click, select Modify, go to the Background tab, and give it a pale blue background. Note that this customization only applies to the Heading style when it is used in this Template and documents based on this Template. If I open Calc at some other time and get usual default page, this style will not have that background.

Then I will go to Row 2, select Cells B2 to E2, merge them, and type “Client”. In this case I want it to be a lower level heading, a Heading 2, and I don’t see one here. So I will create one. Right-click in the Styles and Formatting window, select New, and start filling it in. On the Organizer tab, name it Heading 2, link it to Default, and place it in All Styles. On the Numbers tab, format it as Text. On the Font tab set it to 14-point Bold, and for Background I used Yellow 4.

On Row 3 I want a third-level header, so this will be called Heading 3, formatted as Text, and the font to Bold-Italic 12-point, and for a background Pale Green. Then I use this for cells A3 through E3, and in Cell A3 I type Date. I then type a number “1” in cell A4, and then fill to get numbers up through 31. This gives me a month worth of numbers on this sheet. When I create a document from this Template, I will fill in the names of my clients in cells B1, C1, D1, and E1 (and if you have more clients than that, extend your headers through additional columns).

So, when I put in the names of my clients at the beginning of the month, or as needed. I can then open my sheet every day to enter my billable hours. But of course at the end of the month I will want to total my hours and bill them. So I selected Cell A36, applied Heading 2, then did the same for Cell A38. And in Cell A40 I applied the style Heading 3. Then for Cell A36 I entered Total Hours, in Cell A38 I entered Total Billed, and in cell A40 I entered Invoice #. Now, I go to cell B36, and put in the formula “=SUM(B4:B34)”. Right now, since there is not data, the cell will display a zero, but as you add hours each day the total will mount up. Then, I fill this formula by clicking-and-dragging through cells C36, D36, and E36.

Then in Cell B38 I need to multiply the hours by the billing rate, so I enter the formula “=B36*50”, since I will assume my billing rate is 50 per hour (dollars for me, but perhaps something different for you.) And again I will click-and-drag to fill across the other columns.

This covers one month. But what if you want to cover an entire year? Simple, just copy the sheet. Click on the tab on the bottom that says Sheet 1, hold down the Control key to force a copy, and drag to the right. You will now have a sheet that says Sheet 1_2. Repeat this ten more times and you should have twelve sheets. At this point, I would go back and name each sheet as January, February, etc. To do this, just right-click on the sheet tab, choose Rename Sheet, and type in the month. And when you have done this for all twelve months, go back to the header on row 1 that currently reads “Billable Hours for” and fill in the month name there as well.

If you followed my procedure and saved this as a Template and then opened the Template for editing, all you need to do now is click the Save icon. If you want to double-check, look at the Title bar up on the very top of the window. If it has the name of a template with the *.ots extension, you are saving a Template. If you don’t see this, be careful as you are in an ordinary document. In that case go to File–>Templates–>Save As Template to make sure you save it properly. (See our discussion above for how to do this.)

Now that you have the Template, you can put it to use. in Calc, go to File–>New–>Templates, select your Template, and click Open. You will have a an untitled document based on the Template, but it is already set up with your headers, your formulas, and your labels. All you need to do is start adding data.

The Template I created can be downloaded here.

Listen to the audio version of this post on Hacker Public Radio!