|
Anyone who has ever done any Excel troubleshooting knows that one of the
areas that causes people the most problems is printing, because Excel
spreadsheets are often not only too long for one page, but also too wide
for one page; and, therefore, Excel inserts both horizontal and vertical
page breaks, which can make your data print in all kinds of strange ways
if you don't understand how it works. But, once you do understand it,
it's pretty straightforward and easier to tweak to get what you want.
- To do this exercise, you will need at
least 9 columns of data and 20 rows. Make sure the data in column
A are people’s Last Names, and column B are people’s First Names. It
does not matter if this data is repeated over and over since
this exercise is simply to show you how to set up your pages
for printing, and it will be easier for you to see how this works
if your data is the same as mine. Columns
C through I can have anything you want in them.
- Select all of your data and set both
the Row height and Column width to 50.
- Go to File > Save As and save this file. Do
not close the file. Be sure to save it now so you can revert
back to it if you mess up your page settings so badly that it's easier
to start over (and this CAN happen.)
- Now click on the Print Preview button
and you will see that this file is printing in Portrait orientation
and is 18 pages long. Use the Next button at the
top of the Print Preview screen to advance through the pages and you
will see that each page only shows one column and every other page is
the bottom half of that column.
- First thing we need to do is get two
columns on a page, so click on the Setup button at the
top of the Print Preview screen, then click on the Page tab
and change the orientation to Landscape. Click OK.
- Use the Next and Previous buttons to
see that now you only have 15 pages and you are now seeing two columns
per page, but you are still seeing the second and third parts of each
column before you see the next column.
- Click on the Setup button again and this
time click on the Sheet tab and change the Page
order to Over,
then down and click OK.
- Look through your pages and you will
see that now you see all the data for these people before you
start seeing the data for the other people towards the bottom of your spreadsheet.
But you notice that when you get to pages 2 and 3, you can no longer identify
which person this date refers to.
- Click the Close button at the top of
the Print Preview screen to close Print Preview and return you to your
spreadsheet.
- Now, highlight columns A and B (your
first and last names) and format the column widths for these two columns
to be 12.
- Then, go to File > Page Setup and click
on the Sheet tab. Click inside the box that says "Columns
to repeat at left", then drag to highlight columns A and
B on your spreadsheet and this box should now say $A:$B. Click
on the Print
Preview button.
- You now see, if you look through the
pages, that your first and last names appear on the left of each
page, with one column of data to the right of it. This is ALMOST what
we want.
- Close Print Preview and return to your
spreadsheet. Now, go to the View menu and select "Page
Break Preview" and you will be presented with this box:
- This box just tells you that you can
drag the blue lines you now see on your screen to move your page
breaks where you want them. Also, see that each page is labeled in
gray in this view, so you can see exactly what is included in each
page. Click
OK on this box. (You can also put a check in the
box that says "Do
not show this dialog again." if you don't want to have to deal with
this box every time you use Page Break Preview.)
- Notice you see some dotted blue lines
and some solid blue lines. Solid lines show Manual page breaks
and dotted lines signify Automatic page breaks (made by Excel
to conform with your cell sizes and the size of the paper you are
printing to). My
data now looks something like this (note in this view you do
not see the repeating columns on the left):
- Let's say I want this data to fit on
seven pages wide (which it is), by two pages tall (which it isn't
... it's printing three pages tall). There are two ways you can fix
this. Save your file now so you can get back to the way it
looks at this moment. We will try one way, then you can close
without saving, then reopen the spreadsheet and try the second way.
- The first way is to click on the dotted
blue line between row 18 and 19 and drag it down past row 20
so it disappears. Now you only have one horizontal page break after
row 9. Drag that one up so it's between rows 8 and 9. However, when
you did this, Excel had to resize the cells proportionately, so it
made them shorter AND more narrow, and now you only have four pages
across instead of the seven you want. So, you will have to now drag
the vertical dotted lines to the left for all the pages until you again
have only one column per page. You may end up with column I not even
being included in your pages. If so, you will have to go back to Normal
View and click in cell I1 and go to the Insert menu
and add a Page Break.
- When you are done fiddling with it, it
should look something like this, if you reduce your Zoom percentage to
25%:
- That eventually got us to where we
wanted to be, but it's a bit of a hassle. Usually I only use
this method when I simply want to move a single page break over one
or two columns or rows. In this case, it's more work than necessary,
but it does get you accustomed to playing with this feature. Now,
close the file but do not save the changes. Then reopen the file.
- You should be back to the setup where
you had seven pages wide and three pages tall. Go to the File menu and choose Page
Setup. On the Page tab, put a tick beside where
it says "Fit to" and select 7 pages wide by 2 tall.
NOTE: This
may not work if you have set some manual page breaks that interfere with
this. If you cannot get it to work, revert back to your original spreadsheet
and set the number of pages wide and tall in this box BEFORE you change
any other settings. Sometimes
this feature does not work when you want to INCREASE the number of
pages. If
Excel does not see a need for this many pages wide, it will try to
override your settings, so you may have to go into Normal View and click
in the first cell at the top of each column and MANUALLY insert the vertical
breaks BEFORE you set it to be 7 wide by 2 tall. Also note that when
you do this, Excel decides where to put the page breaks and not you,
so you may end up going into Page Break View and adjusting them manually
anyway.
Setting up your pages for printing is one
of the tougher jobs in Excel. As long as you remember the options
you have in Page Setup and Page Break Preview, you can usually get
it sorted out between the two places. The only way to learn it is to
play with it until you find what works best for your particular spreadsheet.
Also, remember that a lot of the page settings Excel applies for you
are based on the size of your fonts and cells, etc., so sometimes you have
to manually go into your spreadsheet and adjust them. Also, notice
on the Page tab in Page Setup, above the "Fit to" option, there
is also an "Adjust
to" option. If your spreadsheet is just a little too big to
fit on the number of pages you want, sometimes just dropping this
percentage by a couple numbers will work. Decreasing or increasing
this percentage tells Excel to proportionately change the size of
everything on the spreadsheet by this amount.
TIP # 1: If you
only want to print a portion of a sheet, you can just highlight those
cells, then go to File > Print and put a tick
in the radio button where it says “Selection” and only that area will be
printed. And, Excel has a great feature which allows you to set a print
area when you have spreadsheets where you only ever print part of
the sheet. If
you highlight a group of cells, then go to the File menu and select
Print Area > Set Print Area, when you print this sheet, only that area
will be printed. This is useful if this is something you print often and
you don’t want to have to specify the selection every time you print it. However,
if ever you want to print the entire sheet again, you will have to
go to File > Print Area and click on “Clear Print Area”.
TIP # 2: By default,
when you print in Excel, it only prints the active sheet, however you
can go to File > Print and put a tick in
the radio button that says “Entire Workbook” if you want to print all of
the sheets. And, if you want to print more than one sheet, but not all
sheets, you can group the sheets before you go to File > Print and all
sheets that are grouped will be printed. (To group sheets, just hold down
your control key while you click on the sheet tabs for the sheets
you want grouped.) Just remember to ungroup the sheets after you print
because when sheets are grouped, everything you do on one is done
on all of them.
Hope this gets you on the road to happier
printing days in Excel!
Linda
http://personal-computer-tutor.com
|
