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.

How Styles Work In Microsoft Excel 2007

In addition to using and modifying Excel’s built-in cell styles, you also have the facility of creating your own. A convenient method of doing this is to highlight a range of cells and apply all the formatting attributes that you would like your style to have and then to turn those attributes into a cell style. When you create your cell style, Excel will detect all the attributes from the highlighted cells and include them in the style. Let’s take an simple example; let’s say we want to create a cell style for headings with our text angled at 45 degrees.

To set the text angle to 45 degrees, choose “Angle Counter Clockwise” from the Alignment section of the Home Tab of the Excel Ribbon. We might also want to centre the text horizontally and vertically, change the colour and make the text bold and slightly larger. Having chosen the relevant options from the Home Tab of the Excel Ribbon, to create a style which has all these attributes, simply highlight a cell that contains these formats and choose “New Cell Style” from the Cell Styles drop-down menu in the Styles Tab of the Excel Ribbon.

The Style dialogue box will then appear. Here, we can specify a name for our new style: let’s say we call it “Angled Heading”. The Style dialogue box contains six categories of formatting information which can be activated or deactivated via a series of checkboxes. We can switch off any categories of formatting that don’t apply. Thus, for our heading style, we can deactivate the categories “Number”, “Border”, “Fill”, or “Protection”; so we can uncheck all of them. Next to each of the categories, Excel lists the relevant attributes of the highlighted cell(s); so it’s not necessary to click on each category to set any options; all we need to do is click OK.

When creating a style in this way, it’s important to realise that Excel does not apply the style to the cell or cells on which the style was based. If you want to apply the style to the cell, you need to do so explicitly by highlighting the cell and choosing the name of your new cell style from the Cell Styles drop-down menu in the Styles section of the Home Tab of the Excel Ribbon. Having created a style you’ll notice that Excel has an extra category called custom in the Cell Styles drop-down menu and it is here that you can choose the style that you just created. Although the highlighted cell may look the same, it will now be associated with the new style.

We would then go on to apply the style wherever it was needed throughout our workbook. Just doing this is, in itself, a big time-saver. In addition, however, if we were to change the format of our heading, all we need to do is to modify the attributes associated with our cell style and all of our headings will be automatically updated.

Author is a developer and trainer with Macresource Computer Solutions, an independent computer training company offering Microsoft Excel 2007 Classes in London and throughout the UK.

Customising Text Alignment Microsoft Excel 2007

The alignment section of the Home Tab of the Excel 2007 ribbon is home to a number of options relating to the way in which your data is position within the cell. The most familiar and most frequently used icons in this section are the three relating to the horizontal position your data: left, centre and right. However you’ll notice that when you activate the cells of an unformatted worksheet, none of the three alignment icons is highlighted. This indicates that none of them is the default. The reason for this is that Excel treats data alignment differently depending on the data type.

If you type text in a cell, your text is aligned on the left; if you type a number, the number is aligned on the right; if you type a date, it is also aligned on the right. To set the horizontal alignment, you can either select a range of cells or click on a column letter to highlight the entire column then click on one of the alignment icons.

Haven chosen one type of horizontal alignment, you can change it in two ways. You can either click on a different form of alignment or click again on the already selected alignment. For example, if your text is centred and you click on the Centre button a second time, this deactivates centre alignment and returns you to the default alignment which, for text, is left. Thus we have, effectively, four types of horizontal alignment: left, centre, right and unspecified (or default), which is the alignment that applies when none of the alignment buttons is highlighted.

Excel also allows you to specify vertical alignment. This setting normally only becomes apparent when you increase the height of the cell and this time there is a definite default which is that text is aligned at the bottom of the cell. This setting applies to text, dates and numbers alike.

To change vertical alignment, either make a selection or click on the row number to select the entire row then click on one of the buttons to make the change: align middle, align top and so forth.

The alignment option also includes the ability to change the orientation of text within the cell. This is particularly useful in those situations where the headings are wider than the data within the cells. To change the vertical orientation of your text, you simply select the cells in question and then choose the appropriate orientation in the Alignment dialogue.

Having changed the orientation of the headings, you can probably make the columns much narrower. Excel offers a very useful way of doing this: simply select all the columns that contain data then in the Cell group of the Home Tab of the Excel Ribbon, choose Format and then AutoFit Columns. This option makes each of the highlighted columns no wider than it needs to be to display all the data it contains.

Finding Effective Training On Microsoft Excel 2007

Upgrading to Excel 2007 may be something of a shock to you and your staff. The initial reaction of most people is: “where is everything?” Bearing this in mind, you may well find that a training course on Excel 2007 is a good investment. The training should first of all get you past the initial state of confusion caused by the fact that 2007 looks so different from previous versions. Then it should give you some guidance on the new features in Excel 2007 such as the enhancements to charting and graphics, functions and conditional formatting.

One of the first things you should look for in having training on Excel 2007 is a full explanation of how the new interface works. You should be shown the new way of working and learn useful tips and shortcuts which will enable you to become at least as productive in Excel 2007 as you were in 2003.

In addition to this, however, you will want to learn the new features that Excel 2007 has to offer: the stuff that either wasn’t available in previous versions or which has undergone considerable enhancement.

The Excel 2007 worksheet is much bigger than its 2003 counterpart; about a thousand times bigger in fact. Your Excel 2007 training should show you how you can exploit this huge area when designing your spreadsheets. Some decent navigation tips would also be helpful.

Your training should also include demonstrations and explanations of pivot tables, a feature which has been revamped and improved in Excel 2007. If you or your colleagues have not used pivot tables to any great extent before, the training should begin by reviewing basic pivot table concepts before moving on to look at what Excel 2007 has to offer.

Charts have been given a big overhaul in version 2007. So be sure your training includes coverage of the special effects available in Excel 2007 charting and graphics. Your training course should cover such features as the format, design and layout ribbons and how to use SmartArt graphics and shapes. If charting is particularly important in your organisation, then make sure your training covers things like trendlines, scatter charts and pivot charts.

Your Excel 2007 training course should also cover conditional formatting. This is a feature that has been much enhanced in Excel 2007 and your training should show you how to exploit the new features available. Make sure you will come away from the training knowing all about Data Bars and Color Scale.

The ability to enter formulas and functions into the cells of an Excel spreadsheet is the key feature that has made the program so essential to so many organisations. Excel 2007 has added several new features relating to functions and several new functions. You should insist that any training course you book on Excel 2007 demonstrates these new features and functions such as IfError, SumIfs and AverageIf.

Next Page »