Practical issue: Save time and effort setting up individual worksheets with Excel templates [1]
Solutions: Set up a template [2]
Making sure a new workbook contains the sheets you want [3]
Working with sheets [4]
Sheet navigation [5]
Further reading [6]

Practical issue: Save time and effort setting up individual worksheets with Excel templates

Understanding Excel's options and how to work with Excel templates can save considerable time and effort when setting up the individual worksheets in an Excel workbook. It's also worthwhile learning the quickest ways to manage and manipulate worksheets.

Solutions: Set up a template

Making sure a new workbook contains the sheets you want

By default, a new workbook will contain three new sheets named sheet1, sheet2 and sheet3 and these sheets will be created from Excel's default sheet template. You can impose your own requirements on Excel in a small way by just choosing how many sheets a new workbook should contain. Go to Tools, Options and you will find the 'Sheets in new workbook' option on the General tab. (Excel 2007: Office button, Excel Options, Popular, 'When creating new workbooks' section, 'Include this many sheets'). The option can be set from 1 to 255, but the number of sheets a workbook can contain is 'limited by available memory'. Excel 2007 contains an additional option to set the 'Default view for new sheets' to Normal, Page Break Preview or Page Layout View.

You can go a lot further than this through the use of templates. You can create a new Excel template by using the File, Save As option and setting the 'Save as type:' to Excel Template, this will change the 'Save in' location to the Templates folder and also set the file extension to 'XLT'. In Excel 2007, as an added security feature, there are two types of Excel 2007 file format template – the normal XLTX and the macro-enabled XLTM. New workbooks created from a template will contain whatever sheets are in the template. This means you can include any 'standard' sheets and content that all spreadsheets based on that template should contain, and also set up the formatting and page set-up options such as headers and footers that you require.

The use of templates is not limited to creating a whole new workbook, you can also create templates for use in adding individual sheets or groups of sheets to an existing workbook. When you right-click on an existing sheet tab and choose 'Insert' you will be presented with your list of templates. Choosing a template will insert all the sheets in that template into your workbook. Obviously, if you want to create a template for a new sheet that includes page headers and footers and whatever other formatting or content you require, you would just save a workbook with a single sheet as a template.

We're not quite done with templates yet, it is possible to create new 'default' Excel templates that will be used when you use the Control-N keyboard shortcut or the New' button for a new workbook, or you use the Shift-F11 keyboard shortcut or choose the Insert, Worksheet option (Excel 2007: click on new worksheet tab) to insert a new sheet. For a new default workbook you need to save a template named 'Book.xlt' in the XLSTART folder (Excel 2007: Book.xltx or Book.xltm). It's the same for a new worksheet except 'Book' is replaced with 'Sheet'.

Working with sheets

Right-clicking on an existing sheet tab brings up the shortcut menu containing all the sheet commands. However, many of the commands are more easily carried out in other ways:

Insert: Control-F11 or Insert, Worksheet. Excel 2007: click Insert Worksheet tab:

(Note that as described above, these options insert a default sheet whereas the right-click insert allows you to choose from your templates).

Delete: does what it says on the menu.

Rename: double-click on the sheet tab.

Move or copy: drag to move or control-drag to copy. The right click option can make copying between different open workbooks easier, but you do have to be very careful to click on the 'Create a copy' option if you want to copy rather than move.

Select All Sheets: this can be very useful if you want to set up all the sheets in the same way, but must be used with great care as it is all too easy to inadvertently overwrite settings or content on the sheets that aren't visible. Multiple sheets can also be selected by clicking the tabs while holding down the control key, or the shift key for contiguous sheets. When more than one sheet is selected, right-clicking on any selected sheet will display the right-click menu with an additional 'Ungroup Sheets' option.

Tab Colour: from Excel XP onwards it is possible to set the colour of each tab.

(View code: opens the Visual Basic window for the sheet.)

The Excel 2007 right-click menu also includes the Protect Sheet and Hide/Unhide Sheet options.

Sheet navigation

Control+PageUp and Control+PageDown will move to the next and previous sheets respectively.

To display two sheets of the same workbook side by side choose Window, New Window followed by Window Arrange. If you have multiple workbooks open, use the 'Windows of active workbook' option to avoid showing windows for all the workbooks.

In Excel 2007 the Window options are found in the Window section of the View Ribbon.

In Excel 2003 and Excel 2007 there are specific 'View Side by Side' options that include the ability to set 'Synchronous Scrolling' to ensure that the two windows scroll together.

Further reading

Gail Perry's articles on manipulating sheets in Excel: and

Using the worksheet code event to when a cell value changes.

Detailed compendium articles on

including the use of '3D' formulae, Pivot Tables and the Indirect() function.

Subscribe to the ExcelZone newswire
[7]To keep up with spreadsheet issues, click the graphic to subscribe to the free fortnightly ExcelZone newswire. The subscribe function [8] will take you back to the AccountingWEB home page after it adds your name to the subscription list.