• List of Excel Add-In
• List of Excel Templates
• List of Excel Training
• Back to Free Excel Training Index

EFFECTIVE PRINTING

As we have stated in previous lessons, we believe the content of a Worksheet is far more important than using a great deal of formatting to pretty it up.

However, when it comes to printing a Worksheet, it is essential that you know how to produce a finished product that a reader can easily relate to.  It is definitely worth spending a small amount of time on getting your printer settings correct, rather than waste time and paper trying to do it quickly. Let’s look at the printing function of Excel in more detail.

There are many different ways that you can print a Workbook.  The three most common are:

• By going to File>Print.

• By selecting Ctrl + P.

• By selecting the printer icon on your Standard Toolbar.

If you print by selecting the printer icon, you must be aware that this option will by-pass the Print dialog box and print straight to your printer using its current print settings.  Use this option only when you are sure of your print settings.

Let us now access the Print dialog box by using either the File>Print option or by selecting Ctrl + P and look at the essential functions of this dialog box in some more detail.

Printer

Name

In the white box next to Name, you will see the printer that your particular PC is connected to. If you can access more than one printer, selecting the downward pointing arrow to the right of the printer name will enable you to see the other printers you are connected to. If you wish to change printers to print (ie, if you are connected to both a black and white printer and a colour printer, you can decide which printer you wish to use by just clicking the desired printer with your left mouse button.

Status

This is telling you the status of the printer you have selected. The choices are either busy or idle.

Type

This is again telling you which type of printer you are connected to as your default

Where

This is identifying to you the location of your printer. If you are connected to a local printer it will display the port number of your computer. If you are connected to a network printer, it will display the location of the network printer.

Find Printer

Allows you to select a printer on your network that is not listed in your dialog box.  Sometimes network administrators will bar you from entering this area, but if you have access, you can select the printer you want to use, click OK to return to this menu and print the document.

Properties

By pressing this button, you will be taken to a sub-menu of Properties.  Let’s look at the sheet tabs within this sub-menu. After making a selection on any of these sheet tabs, click OK and your changes will be accepted.

Note:  Some of the options listed here may not be available to you, you may have extra options, or the option names may be slightly different.  This is entirely dependent on the printer you are connected to.

Paper

Paper Size

Under this heading you will see the various paper sizes available to your printer. Have a look at the paper sizes available to you by scrolling through with the horizontal scroll bar beneath the icon window. Clicking on the desired paper size will select it. You will note in this box that there is a custom icon. If you printer supports custom paper sizes, you can click this icon and specify the size you wish to use.

Paper Source

This box and its downward pointing arrow to the right specify where in your particular printer your paper resides. Different printers have different paper sources, such as upper tray, manual feed or lower tray and you may wish to change your source. For instance, in most business today you will find letterhead stored in an upper tray and followers (white) paper in a lower tray, or A3 paper stored in one tray, and legal in another.

Media Choice

This box and its downward pointing arrow to the right specify the type of mediums available to you. These could be in the range of standard, bond, special paper, or transparency. Note that if you select transparency, you must have a transparency in your paper tray, otherwise if you print on ordinary paper, the printer’s ink will smear and waste not only ink but paper as well.

About

This will tell you nothing more than the copyright information unique to your particular printer.

Restore Defaults

Pressing this button will restore the defaults on this page tab to their original settings.

Graphics

Resolution

This option will only apply if you are using a laser printer and true-type fonts. It enables you to change the resolution of your printing. Basically, the higher the resolution, the better quality printing you will get.

Dithering

Dithering is used for colour printing and black and white printing. It blends pure colours into patterns to simulate a wider range of colours (such as blending red and yellow together to make orange) when used with a colour printer, and will produce grey shading in graphics for black and white printers.

None 

Click None if you don’t want any dithering.

Course

Click this if your resolution setting is 300 dots per inch or higher.

Fine

Click this option if your resolution setting is 200 dots per inch or less.

Line Art

Click this option if your graphics include well-defined borders between black, white and grey settings.

Error Diffusion

For printing pictures or photographs without well-defined or sharp edges.

Intensity

This option has a slide bar that can be dragged by holding down your left mouse button on the arrow and dragging towards either darkest or lightest. It will affect how dark or light the graphics in your document are printed.

Device Options

Print Quality

The options under this heading will be unique to your printer. Basically, you can change the type of quality you want to use depending on if you want to print a draft, a presentation or whatever.

Back onto the Print Dialog Box now and we will look at Print Range.

Print Range

There are two options under print range. Select All you would use if you wish to print your entire Workbook, or Select Page(s) if you wish to only print some of your Workbook. You can either type in the From and To boxes, or you can use the spin button (the tiny upwards and downwards pointing arrows to the right of these boxes) to make a selection. Then click OK.

Print What

There are three options under this heading.

Selection

By selecting this option, Excel will only print the range you have selected.

Active Sheet

If you select this option, Excel will print the active sheet. This is defined as going from the first cell containing data to the furthest right most cell containing data, and the furthest bottom cell containing data in your Worksheet.

Entire Workbook

This obviously will print the entire Workbook if selected.

Copies

You can either type the number of copies that you require in this box, or use the spin button to make a selection, then select OK.

Preview

By pressing this button, you will be able to view your document in Print Preview mode. More about this now.

Different Views

Excel provides you with lots of different settings that let you adjust the final appearance of the data that you wish to print. There are three different types of views that are available to you to help you see and adjust how the Worksheet will look when you print it. It is entirely up to you which view you work in, you can switch between the different views to view your work in different modes and see the effects before you print it.

The three views available to you are:

• Normal View

• Page Break Preview

• Print Preview

Normal View

Normal view is the default of Excel. It is the best option for working with your document and for on-screen viewing. This view is available through the View menu on the Standard Worksheet Menu Bar.

Page Break Preview

This preview will show you the page breaks of your data and will easily allow you to adjust your print area and page breaks. This view is available through the View menu on the Standard Worksheet Menu Bar and is not covered in this lesson.

Print Preview

If you set your view to Print Preview, you can easily see your columns and margins and adjust them in this mode. All you need to do is hover your mouse over the column handles (located right at the top of your page) until your mouse changes to a cross and drag in whichever direction you require to widen the column, or place your mouse on the margin lines and drag in the same way. This view is available by pressing the Preview button on your Print dialog box, by selecting the Print Preview icon located to the right of your printer icon on the Standard Toolbar, or by selecting Print Preview from the File menu.

Let us now have a look at printing a typical document.

Call up the attached Workbook ExcLev1L07WB.xls (NOTE: you may have to extract it from the ZIP first !)

• Try out the different views available to you. Remember, Normal view (which you should already be in since it is the default) and Page Break Preview are available through the View menu on the Standard Worksheet Menu Bar, whilst Print Preview is available to you by selecting the icon next to the printer icon, or by selecting Preview from the Print dialog box or by accessing Print Preview from the File menu.

• Let us now highlight the range that we wish to print to perform a print by Selection. Select the range A3:J54 on the Automatic Outline worksheet.

• Now select Print Preview to have a look at your selected range in this mode by using one of the three methods described above.

• Notice down on the status bar at the bottom of your page you will see the text: Preview: Page 1 of 2. This is telling you that you are on page 1 of 2 pages.

At the top of your screen are 9 buttons. Let’s have a look at some of these now.

Next and Previous

Selecting these buttons will take you through the number of pages you have on your screen. Next will take you forwards and Previous will take you to the previous page.

Zoom

By selecting this button, you will switch between a full-page view of a sheet and a magnified view. The Zoom feature will not affect the size of your printing. It is just an aid to assist with readability. You can switch between a full-page view and a magnified view of a sheet by clicking any area of the sheet. Notice your mouse will change to a magnifying glass when you do this. Place your mouse over the part of the sheet that you wish to magnify and by clicking your mouse button it is magnified.

Margins

Select the button titled Margins (another toggle button), you will see lines pop up on your screen. Each line depicts a margin. You can change your margins by hovering your mouse over the lines until your mouse changes to a cross, then drag to either widen or shorten. Selecting the Margins button again will hide the margin lines.

Setup

If you select this button, your Page Setup dialog box will pop up in front of you.

This dialog box can also be accessed via the File menu. You will notice as we go through this box that a few options are greyed out. If you select Page Setup through the File menu, all options will be available to you, so it is a good idea to access it this way while you are learning. This is probably the most important box of the entire printing function, as it is here you make the necessary changes to print your document professionally.

Orientation

Orientation means the way in which your sheet of paper prints from the printer. Portrait means with the shorter edge at the top of your page and the longer edges at the sides, and the Landscape option means with the longer edges at the top of the page and the shorter edges down the sides.

Scaling

There are two options under this heading. The first option Adjust to __% of normal size will allow you to reduce or enlarge the print range you have selected. The range varies from shrinking your selection to 10% of normal size, to expanding your selection to 400% of normal size.

The second option under this heading is Fit to __ page(s) high by __ page(s) tall. This will make our selection to fit on however many pages we specify.

Let’s now select Portrait under Orientation and Fit to 1 page(s) wide by 1 page(s) tall under Scaling. Now hit OK and you should be returned to Print Preview mode. You will notice that all our selected data now appears on one page.

If you hit the Setup button again, you will be returned to the Page Setup dialog box.  Note here that under Adjust to __% of normal size you have 77%. This is telling you that Excel had to reduce your selected data to 77% of it’s normal size to fit it all onto a page that is 1 page wide by 1 page tall.

Now select the Landscape button under Orientation and then select the Adjust to __% of normal size option and either type or use the spin button arrows to reach 100%. Select OK again to accept your choice.

Again you will be returned to Print Preview mode and you will notice now that your selected data is again on two pages. If we wanted to, we could select the Fit to 1 page(s) wide by 1 page(s) tall option, to fit all of our data on a landscape page, but in this case we will print on 2 pages.

Paper Size

You can change your paper size here, as well as in your Print dialog box if you wish by selecting your downward pointing arrow to see the paper sizes available through your printer.

Print Quality

You can change your print quality here, as well as in your Print dialog box by also selecting the downward pointing arrow to see the ranges available to you.

First Page Number

The default here is Auto which means your printing will start at page 1. If you wish to print starting at another page number, you can type any number other than 1 in this box.

Margins

This sheet tab will show you the margins of your page and the margins of any headers or footers you may have. You can type your margins in if you wish, but it is far easier to change your margins using the Margins button in Print Preview mode, as discussed above.

Centre on Page

This option however, is a very handy feature not available in Print Preview mode. By selecting either horizontal or vertical you will center your selection on your page either horizontally or vertically.

Header/Footer

The definition of a Header or Footer is something that is required to appear on every page of your document. As the names suggest, a Header is something that will appear at the top of every page, and a Footer is something that will appear at the bottom of the page. In Excel, you can have only one Header and one Footer in each Worksheet. The Header/Footer Dialog box can be accessed through the Page Setup dialog box, or via the View menu on your Standard Worksheet menu bar.

Let us first create a Header for the data that we wish to print.

• Click on Custom Header.

• Click in the white box under Centre Section. As the title suggests, if you type in this box, your Header will be centered, Left Section will left align your text and Right Section will right align your text.

• Type in the words FOUR YEAR FORECAST in the Centre Section box.

• Now, highlight your text and click on the A icon above. You will now be familiar with the Format Cells dialog box, and what you are looking at now is a mini version of this.

• Select Bold under Font Style and 24 under Size. Click OK.

• You will now be taken back to your Header dialog box and you will notice that the words FOUR YEAR FORECAST are indeed bolded and size 24.

• Select OK.

Now you are back in your Page Setup dialog box, notice that your header is in the top white box, and also under the heading Header.

To the right of the second white box is a downward pointing drop down arrow. This contains some built-in headers. If you wanted to, you could select a build-in header, then go to Custom Header to use it for your Worksheet.

Footers work in exactly the same way so let’s put a footer in now

• Click on Custom Footer.

• Click in the white box under Left Section.

• Select the second icon from the left with a green cross in it. You will notice &[File] appear in the Left Section. This will automatically insert the file name of your Worksheet into the Left Section of your Worksheet.

• The other icons here (apart from the A which is the mini version of your Format Cells dialog box) will insert information as follows:

  • # Will automatically insert the page numbers for you.

  • ++ Will automatically insert the number of pages in the active Workbook.
    This means that if you had Page # of ++ in your footer, you would actually have Page 1 of 12 (or however many pages you had).

  • 8/7 This will automatically insert the current date.

  • Clock Will automatically insert the current time.

  • Green Cross Will automatically insert the current file name.

  • Blank Paper Will automatically insert the sheet name of the current Worksheet.

• Now click in Right Section of your Footer, type the words Printed on then select the Date icon.

• Now select OK to return you to your Page Setup Dialog Box.

The last section of the Page Setup dialog box that we need to look at is under the Sheet heading

Sheet

The top area of this box is greyed out. If something is greyed out within Excel, this is because the options are not available in the area in which you are in. The options at the top of this box are only available if you access the Page Setup dialog box via the File menu as mentioned above.

The only options we need to look at here are the ones under Print.

Print

If you select Gridlines the gridlines of your Excel Worksheet will print.

If you select Black and White, your data will be printed in Black and white only (if you have colour in it, it will appear as grey shading).

If you select Draft Quality your data will be printed in draft quality.

If you select Row and Column Headings then the row and column headings (A, B, C, 1, 2, 3 etc) will be printed.

Page Order

You have two options here; Down then Over and Over then Down. Selecting one of these options will control the way data is numbered and printed when it does not fit on one page.

Let us select OK now to return us to Print Preview mode to have a look at our nearly ready to print Worksheet.

You will notice that you can now see the Header and Footer that we placed in our Worksheet.

Now hit your Next button and you will notice that the row that appears at the top of page 1 (Jan, Feb, 1st Quarter etc) does not appear at the top of page 2. We can easily change this by doing the following.

• Press your Close button to close you out of your Print Preview mode and return you to your Worksheet.

• Now with your print range still selected, go to File>Page Setup>Sheet.  You will notice in here that the top half of the box is now NOT greyed out, so all options are accessible. The top option Print Area allows you to select the range that you wish to print if you have not already done so. We have already selected our print area, so we do not need to use this option in our printing.

• We need to go to Rows to Repeat at Top under Print Titles. Let us press the collapse box (red arrow) to the right of this box, to collapse the dialog box and enable us to select the row we wish to repeat.

• All we need to do now is to select somewhere in row 3. This will place the reference $3:$3 in the Rows to Repeat at Top box.

• Press your collapse box button again to expand the box.
  You will note the option here Columns to Repeat at Left. This works the same as for rows, except it will repeat the selected columns on each page. You can also note here that under the heading Print there is an option called Comments. This option is only available if you select your Page Setup dialog box via the File menu and is used to print what are known as Comments (discussed in Excel – Level 2)

• Select OK.

• Now we are happy with our Worksheet, we can print it by selecting the Print button in Print Preview mode.

Copyright (c) 2007-2010, ConnectCode Pte Ltd. All Rights Reserved.