Customizing Print Area and Print Titles in Excel 2007

This article offers a description two features which are very useful when printing worksheets in Microsoft Excel: Print Titles and Set Print Area.

Print Titles

As well as printing headers and footers on every page, Excel also allows you to specify Print Titles. Whereas headers and footers give an overall description of the report, print titles are taken from the worksheet itself. They are normally row and column headings and they are very often an essential part of the printed report.

For example, if you have a worksheet with only five average-sized columns but several hundred rows, although the header and footer appear on every page, only page 1 will have column headings. This means that it becomes difficult to know what the figures on the other pages relate to. We can use Excel’s Print Titles feature to print the column headings on every page. Print Titles are in the Page Setup section of the Page Layout tab; simply click on the button and Excel displays the Page Setup dialog box with the Sheet tab activated. In our five column worksheet example, we would only need to specify the number of rows that we would like to repeat at the top of every page. In our example, it would be only the first row. Having clicked to specify row one, we can click Print Preview to see the result and we would see that the headings are repeated at the top of each page.

Set Print Area

Print Titles

In the Page Layout tab of the Excel 2007 ribbon, you will find the Set Print Area drop-down menu. When you click on it, it offers two options: Set Print Area and Clear Print Area. The Set Print Area command allows you to highlight a range of data and specify that this is the only part of the worksheet that will be printed whenever you use the print command. Having chosen the Set Print Area command, Excel will now display a dotted lined border around the area that has been set, in much the same way as it normally displays the page boundaries after the print command has been used.

For example, let’s say you have a worksheet with only five average-sized columns but several hundred rows, although the header and footer appear on every page, only page 1 will have column headings. This means that it becomes difficult to know what the figures on the other pages relate to. We can make use of Excel’s Print Titles feature to print the column headings on every page. Print Titles are in the Page Setup section of the Page Layout tab; simply click on the button and Excel displays the Page Setup dialog box with the Sheet tab activated. In our five column worksheet example, we would only need to specify the number of rows that we want to repeat at the top of every page. In our example, it would be only the first row. Having clicked to specify row one, we can click Print Preview to see the result and we would see that the headings are repeated at the top of each page.

Author is a developer and trainer with On-SiteTrainingCourses.Com, an independent computer training company offering XML and XSLT training courses in London and throughout the UK.

Understanding Microsoft Excel 2007′s Three Page View Modes

Although Excel’s Print Preview feature is fairly useful, it is purely a preview mode. You are not able to work on your data while in Print Preview mode. However, in addition to Print Preview, Excel 2007 has two new modes of working which offer similar benefits to Print Preview while allowing you full access to your data. To switch to these modes, use the buttons on the right of the Excel status bar. There are buttons which can take you from Normal mode to Page Layout or to Page Break Preview.

Normal mode is the default mode in Excel. Here, the focus is on modifying and entering your data without thinking too much about pagination. If you print or preview your data, Excel paginates the worksheet and inserts dotted lines to show you the page breaks. This is normally the only feedback which relates to the printed version of your document.

By contrast, Page Layout view gives you a permanent preview of where page breaks will occur and which data will be printed on which pages. When in Page Layout view, zoom out so that you can see more of the worksheet and you’ll notice that Excel displays margins on the left, right, top and bottom, as well as headers and footers. However, the great thing is that Page Layout view not simply a preview mode. You still have access to all the data within your worksheet and you can edit each of the cells it contains. If the printed version of your worksheet is particularly important, you may find it convenient to stay in this mode permanently; particularly if you have the benefit of a large monitor.

Excel’s third page view is called Page Break Preview. You will notice that, when you click on the Page Break Preview button, Excel automatically zooms out so that you can see more of your worksheet. Page Break Preview mode is a lot like Normal mode. It isn’t WYSIWYG (What you see is what you get) and neither headers and footers nor margins are displayed. The chief difference between Normal mode and Page Break Preview mode is that, in Page Break Preview mode, the margins can be dragged.

This can be an extremely useful facility. For example, if you want to force a given column of data onto a new page, you just drag the blue dotted line representing the page break to the left of that column. In fact, it can be a deceptively simple facility. Many experienced Excel 2003 users may even mistake the dotted lines representing page breaks for a variation of those which are displayed in Normal mode and may not even realise that they can in fact be dragged.

Author is a developer and trainer with TrainingCompany.Com, a UK IT training company offering Microsoft Excel 2007training courses in London and throughout the UK.

Report Preparation In Microsoft Excel 2007

Excel’s page formatting features are available by clicking on the page layout tab of the Excel ribbon. If you are working with page formatting, you may also find it useful to enter page layout mode. This is done by clicking on the page layout button in the status bar. Adjust your page zoom as required and you will have a constantly updated preview of how your document will look when it prints out.

Excel also shows you the number of pages required to print a document on the status bar. Some documents are easier to print by changing the orientation to landscape. This often enables you to fit all the columns in your worksheet onto a single page. To change the page orientation, choose Orientation and then Landscape.

Excel offers three ways of changing the margins. The first is to click on the Margins button and choose one of the presets. Here, you’ll find four options: the last settings used, normal, wide and narrow. One of these settings may well be ideal for your data. If not, the second method of modifying margins is to enter custom settings. This is done by choosing Custom Margins in the Margins drop down menu.

When entering margin settings in this window, it is important to realise that there’s a difference between left and right margins and also top and bottom margins. The figure you enter in the left and top boxes will be faithfully reproduced by Excel. So, for example, if we set the left margin to 3 cm, you will have precisely 3 cm on the left-hand margin. However, because Excel never prints a fragment of a row or a fragment of a column and only prints complete rows and columns, the figure you enter on the right will be the minimum margin rather than a figure which Excel can faithfully reproduce each time. And the same applies to the bottom margin setting.

The third method of modifying margins is perhaps the best of all. It’s also the most interactive. Simply position the cursor on the left of the ruler and drag to the left or right to change the margins. Excel immediately updates the preview of your page and shows you the actual margin setting. You can continue dragging until you are happy with the margins.

Another simple way of changing the way in which your data will print is to change the paper size. In many cases, you can reduce the number of pages required by using A3 paper instead of A4. Naturally, it’s only possible to change the paper size in this way if you have a printer capable of handling that paper size. If you output most of your documents to PDF, paper size will not be a problem and changing the paper size in this way is often a good solution.

The writer of this article is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel VBA 2007 Classes at their central London training centre.

How To Use Print Preview And Quick Print In Microsoft Excel

Excel’s Quick Print facility allows you to send a document to the default printer without the need of entering values in a dialogue box. If the Quick Print button is not already displayed on your Quick Access Toolbar, simply choose it from the Customise Quick Access Toolbar drop-down menu. You will notice that the tooltip which appears when you position the mouse over the Quick Print button has the name of the default printer in brackets. If the printer that is displayed is not the one you anticipated you can simply use the regular Print command instead.

If, like a growing number of Microsoft Excel users, most of your documents are transmitted electronically, you may have Adobe PDF set up as the default printer. In this case, when you click the Quick Print button, you will be prompted to save the file since printing to Adobe PDF means creating a disc file.

Whatever your default printer, you will find that Excel prints the document using its default settings: moderate margins, no header or footer, no column or row headings and no gridlines. If the document cannot fit on a single page, Excel will produce multiple pages moving down first and then across. Having printed the document, Excel paginates your worksheet and subsequently displays dotted lines representing the page boundaries.

Whereas Quick Print will send a document to the printer straight away, Print Preview offers a method of previewing documents prior to sending it to the printer and is often a useful precaution. To access the Print Preview feature, click on the Office button in the top left of your screen, choose Print and then Print Preview.

If the preview of the document looks OK to you, simply click on the print button to send the document of the printer. If the document needs to be modified before it can be printed correctly, one option is to click on Page Setup. This gives you access to settings such as orientation, margins, header and footer, as well as several other advanced features.

You also have the option of zooming in on your spreadsheet data by clicking on the zoom button. When you click on the zoom button a second time, the whole page is displayed again. Excel also allows you to preview all of your pages by clicking on the Next and Previous buttons.

You can also make margins visible or hidden. Margins consist of dotted lines with dragable handles at the end of each line. The margins displayed in Print Preview are pretty comprehensive. Firstly, there are the page margins: top, bottom, left and right. Next, there are margins to control the area available to headers and footers. Finally, there are dragable handles allowing us to change the column widths. You will often find that you can reduce the number of pages needed to print a document simply by altering the various margins.

The author is a trainer and developer with TrainingCompany.Com, an independent computer training company offering Microsoft Excel 2007training courses in London and throughout the UK.

Benefits Of The Split Command In Excel 2007

The Split command can be found in the View Tab of the Excel Ribbon. It allows you to split an Excel spreadsheet window into either two or four individual panes and is particularly useful for comparing data in different parts of a large worksheet.

To use the Split command, you must first activate the cell where you want the split to occur then click on the split button in the Window section of the View Tab of the Excel Ribbon. The horizontal and vertical bars which mark the split can be repositioned simply by dragging them. Separate scrollbars are displayed for each section; so, in the case of a vertical and horizontal split, you will have two horizontal scrollbars and two vertical scrollbars. To remove the split at any time, simply click on the split button once more.

A second and more intuitive method of using the split command is to use the split boxes. The split boxes are tiny icons located above the vertical scroll bar and to the right of the horizontal scroll bar. Let’s say that we have a spreadsheet containing rows of sales figures. Suppose we want to be able to compare the sales figures of one person with the sales figures of other individuals. We can create two vertical panes by dragging the vertical split box. We can then scroll to make the first person’s sales figures visible and in the top pane then do the same to position the sales figures of any other sales person in the bottom pane.

Excel offers us a delightfully simple way of removing the split: simply double-click on the split line.

Suppose we then want to focus on the total for each individual and that the totals are shown in column J. We can create vertical and horizontal splits by dragging each of the two split boxes. We can then display the salesperson’s name displayed in the left pane while in the pane on the right we can scroll across to reveal the total.

As we scroll up and down, we would then see the totals for each sales person next to the sales person’s name. If we would also like the heading to remain in place, we simply resize the top pane to make it one row deep, displaying only the headings, while we scroll the bottom pane up and down.

When we have finished reviewing our totals, we can remove the vertical split and leave the horizontal split in place. To do this, we just double-click on the vertical split line. To return to a completely normal Excel window, we would also double-click on the horizontal split line.

The The writer of this article is a trainer and developer with TrainingCompany.Com, a UK IT training company offering Microsoft Excel 2007training courses in London and throughout the UK.

Moving Excel Worksheets Between Workbooks

Excel permits you to change the order of worksheets within a workbook at any time. There are two ways of doing this, the first of which is simply to drag the tabs that represent each worksheet. As well dragging a single tab, it is also possible to highlight several tabs and drag them all at the same time.

Not only can we move worksheets around within the same workbook, it is also possible to move worksheets from one workbook to another. For example, suppose you have a workbook containing a worksheet for each month of the year (“Jan”, “Feb”, etc.) and that we now want to split this into four smaller workbooks, one for each quarter: the first containing “Jan”, “Feb” and “Mar”; the second containing “Apr”, “May” and “Jun”; and so forth.

To keep the number of sheets in each workbook to a minimum, we could begin by changing the default number of worksheets Excel will give us in each new workbook. This is done by clicking on the Office Button and choosing Excel Options. In the section headed “When creating new workbooks Include This Many Sheets”, we alter the number to one. We can then generate our four worksheets by clicking four times on the new sheet icon on the Quick Access Toolbar.

Each of the new workbooks will have one sheet, which is the minimum that Excel will allow. We can access the new workbooks by clicking on the View Tab of the Ribbon and using the Switch Windows drop-down menu. To move worksheets between workbooks using drag and drop, we will need to see all the workbooks simultaneously. Excel has a special command for achieving this. In the View Tab, we click on the Arrange All button and choose “Tiled”. Excel will then display each of the workbooks in a smaller window, allowing us to see all of the open workbooks simultaneously.

The next step is to highlight the three worksheets relating to the first quarter: we click on “Jan” (the first), hold down the Shift key and click on “Mar” (the last). We can then drag the selected sheets across to the window of one of our new workbooks. We can the simply repeat this procedure for the three remaining quarters.

As we saw earlier, the minimum number of sheets which you can have in a workbook is one. Therefore, when we have moved the last three sheets from the original workbook, the window will simply disappear. Naturally, however, the last saved version of the file will still exist.

The final step would be to delete the unwanted sheet from each of the four new workbooks. Having done this, to leave the split screen view and return to normal mode, we simply maximise any of the windows.

Just for reference, the second way of copying sheets from one workbook to another is to use the Move or Copy Sheets command. This can be found in the Format drop-down menu in the Cells section of the Home Tab or by right-clicking on the selected sheet tabs. As well as moving sheets, this method also allows you to create a copy at another location.

Click here if your people need in-company Microsoft Excel training anywhere in the UK.

Mastering Worksheets in Excel 2007

Excel offers a number of different ways of inserting new sheets into your workbooks. One of the simplest is to use the Insert Worksheet button. This is located on the right-hand side of the worksheet tabs. Regardless of which worksheet is active, clicking this button will always insert a worksheet as the last tab in your workbook.

Excel also allows you to insert a worksheet at a given position within the workbook. For example, let’s say that we have a workbook containing all of the invoices issued by our company for each month of the year. There would be twelve worksheets named “January”, “February”, and so forth. Suppose we now want to insert quarterly analysis sheets at the end of each quarter.

As with columns, Excel will always insert new worksheets to the left of the currently selected tab. Therefore in order to insert the first quarterly analysis sheet, we need to select April. However, as we have already seen, we can’t use the Insert Worksheet button to insert the new sheet or it will always go at the end. So, instead of this, we use the Insert command in the Cells section of the Home Tab of the Excel Ribbon.

Another way of doing the same thing is to right-click on the “April” tab and choose Insert. Excel then asks us to specify the type of sheet we want to insert. We can insert a worksheet, we can insert a chart sheet, a backwardly compatible macro sheet or dialog sheet. The backwardly compatible options persist because they are sometimes useful for Excel developers. In addition to these presets, we can insert one of Excel’s pre-created spreadsheet solutions such as the billing statement or sales report. To add a worksheet, in the General tab of the Insert dialogue, highlight the worksheet button then click OK.

We would then rename the sheet; let’s say “Qtr1 Analysis” and repeat the same procedure to insert analysis sheets to the left of the “July” and “October” tabs. The analysis sheet of the fourth quarter will be the last sheet in the workbook. We can only insert a worksheet in this position by using the Insert Worksheet button.

You can delete the selected worksheet by using the Delete Sheets command in the Cells group of the Home Tab of the Excel Ribbon. It’s also possible to delete a sheet by right-clicking on the sheet tab and choosing Delete. To delete multiple sheets, simply highlight the relevant tabs and then using the Delete Sheet command.

Selecting multiple sheets requires the use of the classic Windows techniques of Shift-click and Control-click. To select a contiguous range of sheet tabs, click on the first then, while holding down the Shift key, click on the last. To select a non-contiguous range of tabs, click on the first then, while holding down the Control key, click on each of the others.

To deselect a selected range of tabs, click on a tab which isn’t selected. If all tabs are selected, just click on the name of any tab to select only that tab.

Author is a developer and trainer with OnSiteTrainingCourses.Com, a UK IT training company offering Microsoft Excel Classes in London and throughout the UK.