To do this exercise, you will need at least 9 columns of data and 20 rows.  Make sure the data in column A is people’s Last Names, and column B is 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.  So, 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%:

So, 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. 

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.

Hope this gets you on the road to happier printing days in Excel!