Printing: Setting the edges of the page

One thing I neglected to mention in my post about common problems is what to do when your printout breaks your table at an inconvenient place. For example, it might be that you’re stuck with just one column or row on another page, and would rather it wasn’t all by its lonesone. It might be that it’s breaking an alphabetical list in the middle of one starting letter, and you’d rather everything starting with that letter was on the same page. Alternatively, if there was a significant gap between letters… say there were a bunch of A through N, and then nothing until S, you might want to have some of N on the same page as the start of the S’s, so readers don’t flip back and forth wondering if there’s a missing page with O through R.

When this happens you can insert a page break to tell the program to start a new page at that location, rather than after the maximum number of rows of columns that will fit on the page.

You might be familiar with page breaks already from word processors. If you’re not, and you’re just hitting enter a lot to get to the start of a new page: try ctrl-enter next time. It’ll insert what’s called a page break, which will make it a lot easier if you have to adjust something later.

However, page breaks in spreadsheets are somewhat different from page breaks in word processors, because word processors only have to worry about up and down. Spreadsheets have to worry both about up/down and about left/right. So when you create a page break in a spreadsheet, you have to worry about both directions too.

There are some general things that are true across all spreadsheets.

  • Getting started: To place a page break, start by selecting a cell. The break will be placed between the row you choose and the row above it, and/or between the column of the cell you select and the column to its left. In other words, if you do both row and column then the cell you select will end up in the upper-left corner of the new page.
  • Right/Left break only: If you want to only change which columns end/start a page, then select a cell in the top row.
  • Up/Down break only: If you want to only change which rows end/start a page, then select a cell in the leftmost column.
  • Multiple breaks: You can have multiple breaks in a single worksheet, but they will all span the entire height/width of it. If you had two tables on the same page, one above the other, you couldn’t put a break between columns D/E for one, and E/F for the other. If you tried, you’d end up with one page with columns A-D on page 1, E on page 2, and F-whatever on page 3.
  • Removing breaks: Unless otherwise specified, to remove a break, follow the instructions for inserting a break. The menu with “insert” should also have “remove”.

As always, every program has its own way of doing this.

In Excel 97:

  1. Click on the “Insert” header or type alt-i.
  2. Click on “Page Break” or type b.

In Excel 2007:

See Excel 2010.

In Excel 2010:

  1. Click on the “Page Layout” tab or type alt-p.
  2. In the “Page Setup” group, click “Breaks” or type b.
  3. In the drop-down menu that appears, clik on “Insert Page Break” or type i.

In Google Spreadsheet:

There’s instructions in the help file for how to insert/remove breaks in spreadsheets, but they don’t seem to be valid – they probably were for Google Docs, and haven’t been updated for Google Drive, or some such. Therefore for now, I have to say that manually setting page breaks isn’t possible in Google Spreadsheet.

In OpenOffice Calc:

  1. Click on the “Insert” header or type alt-i.
  2. Click on “Manual Break” or type m.
  3. Click on “Row Break” or type r, -OR- click on “Column Break” or type c.

Note: To remove a break in Open Office Calc, go to the “Edit” header, and click on “Delete Manual Break” or type b. Then select which type you want to remove.

In Microsoft Works Spreadsheet:

  1. Click on the “Insert” header or type alt-i.
  2. Click on “Insert Page Break” or type k.
  3. Choose whether to insert it above or to the left of the selected cell. If you want to do both, repeat these steps.
  4. Click on “OK”.

Printing: Repeating headers

If your table takes up more than one page, the biggest problem is going to be that the second page won’t have the row headers if it’s too wide, or the column headers if it’s too tll. If it’s both too wide and too tall then the 4th page won’t have any headers at all.

There’s a couple different ways you can deal with this (other than ignoring the problem or using scissors and tape on the printout). One option is to manually separate the table into multiple tables, and repeat the relevant column & row headers in each. This, however, is tedious, and will probably involve a lot of back-and-forth between the print preview and the worksheet itself, as you try to get the perfect fit. Fortunately the other option is much simpler: tell the program what the row and column headers are, and tell the program to print them on every page.

At this point I want to point out somethign that could lead to confusion. I’m talking about the row and column headers, not the “headers” or the “page headers”. (I’m reminded at this point of this Order of the Stick comic.)

  • Row and column headers are the cells at the top or the left of the table, where you’ve entered a brief description of what’s in that column or row. I’ve talked about the importance of column headers before. Some programs refer to these as “titles”.
  • Headers, which could also be called worksheet headers or spreadsheet headers, are the row numbers and column letters.
  • Page headers are something you may be familiar with from word processing. They’re often grouped with footers, and print at the top (or bottom) of every page, usually outside of the normal print area. It’s where you put the title of the report, and/or your name, and/or the page number, and/or the file location, etc.

In this post I’m specifically talking about row and column headers – the ones you define.

Generally, you can specify as many rows or columns as you want, as long as they’re all next to each other. That is to say you can tell the program to repeat row 1 on every page of the printout, or row 3, or rows 1, 2, and 3, but you can’t tell it to print just rows 1 and 3.

Don’t get too carried away, though! It is possible to add so many rows and columns of repeating headers that there’s only 1 cell of actual content per printed page.

As always, every program has its own way of doing this.

In Excel 97:

  1. Click on the header for the File menu or type alt-f.
  2. In the menu that opens, click on “Page setup” or type u.
  3. In the pop-up window, click on the “Sheet” tab.
  4. In the “Print titles” section (just below the “Print Area” section), enter the rows and columns you want repeated.

In Excel 2007:

See Excel 2010

In Excel 2010:

  1. Click on the “Page Layout” tab or type alt-p.
  2. Locate the “Page Setup” group
  3. Click on “Print titles” OR click on the arrow in the bottom right corner of the group OR type “sp”. Any of these will open the “Page Setup” window.
  4. In the window that pops up, click on the “Sheet” tab.
  5. In the “Page titles” section (immediately below the “Page Area” section), choose the “rows to repeat at the top” and the “columns to repeat at the left”.
  6. Click “OK”

Note: You will also see a checkbox to print “row and column headings” but that’s not the same thing — that will print the row numbers and the column letters.

In Google Spreadsheet:

  1. Click on the “View” header.
  2. Click on the “Freeze rows”.
  3. Click on the number of rows you want to freeze. Note: these are always at the top of the sheet.
  4. Click on the “File” header, then on “Print” OR click on the printer icon OR type ctrl-p.
  5. In the window that appears, near the top on the right there’s a checkbox for “repeat row headers on each page”. Make sure it’s marked.
  6. Click “OK”

In OpenOffice Calc:

  1. Click on the “Format” header or type alt-o.
  2. Click on “Print ranges” or type n.
  3. Click on “Edit…” or type e.
  4. Under “Rows to repeat” change the dropdown menu from “- none -” to “- user defined – “, then enter the rows in the box next to it.
  5. Under “Columns to repeat” change the dropdown menu from “- none -” to “- user defined – “, then enter the column in the box next to it.
  6. Click “OK”.

Printing: Seeing what will print

One of the first things you should do before trying to print anything from a spreadsheet is figure out what is actually going to print, and how it will appear on the printed page. In Open Office this isn’t so hard: it measures everything in inches, so if you have an 8.5″x11″ piece of paper, and have 1 inch margins, then any table that’s less that 6.5″x9″ will fit. Unfortunatley other programs have different units of measurement, and those units aren’t so useful when comparing table and paper sizes.

Fortunately most spreadsheet programs will let you see how it’s going to print, before you actually waste paper on it. Continue reading Printing: Seeing what will print