Other Excel Add-In, Templates and Training
Download Workbook for this lesson
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:
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
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
This is telling you the status
of the printer you have selected. The choices are either busy or idle.
This is again telling you
which type of printer you are connected to as your default
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.
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.
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.
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.
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
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.
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.
This will tell you nothing
more than the copyright information unique to your particular printer.
Pressing this button will
restore the defaults on this page tab to their original settings.
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 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.
Click None if you donít
want any dithering.
Click this if your resolution
setting is 300 dots per inch or higher.
Click this option if your
resolution setting is 200 dots per inch or less.
Click this option if your
graphics include well-defined borders between black, white and grey settings.
For printing pictures or
photographs without well-defined or sharp edges.
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.
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
Back onto the Print Dialog
Box now and we will look at 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.
There are three options under
By selecting this option,
Excel will only print the range you have selected.
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.
This obviously will print the
entire Workbook if selected.
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.
By pressing this button, you
will be able to view your document in Print Preview mode. More about this
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
Page Break Preview
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.
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.
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
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 !)
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
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.
select Print Preview to have a look at your selected range in this mode
by using one of the three methods described above.
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
At the top
of your screen are 9 buttons. Letís have a look at some of these now.
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.
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.
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.
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 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.
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
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.
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.
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.
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.
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
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.
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
Let us first create a
Header for the data that we wish to print.
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.
the words FOUR YEAR FORECAST in the Centre Section box.
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.
Bold under Font Style and 24 under Size. Click OK.
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.
are back in your Page Setup dialog box, notice that your header is in the top
white box, and also under the heading Header.
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
the white box under Left Section.
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.
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).
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
in Right Section of your Footer, type the words Printed on then select
the Date icon.
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
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.
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
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
Let us select OK now to
return us to Print Preview mode to have a look at our nearly ready to print
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.
your Close button to close you out of your Print Preview mode and
return you to your Worksheet.
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
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.
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.
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)
Now we are happy with our
Worksheet, we can print it by selecting the Print button in Print
Once a document has been saved
with itís print settings, you only need to press the Print icon on the Standard
Toolbar to print. This will by-pass Print Dialog box and send your Worksheet
straight to the printer.
As we stated at the
beginning of this lesson, printing is an essential part of Excel. Even in these
days of a paperless office, we need to be able to efficiently present data that
people can understand and use to itís fullest capacity. Our personal
preference is to use Print Preview first to set up how a worksheet would
look, although you need to be aware that not all options in the Page Setup
dialog box are available to you by doing it this way. You can access the Page
Setup dialog box via the File menu and have all options discussed here
available, but at the end of the day, it is up to you which way you use.
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
Get the Excel VBA Training for free when you purchase our Technical Indicators now!