Printing: Making it look like a spreadsheet

Up till now I’ve been talking about printing out tables, and usually most people don’t care what sort of program generated the table. It should look clean, it should not have a lot of extraneous information, and it should only have the borders you’ve deemed appropriate. Therefore, by default most spreadsheet program printouts don’t look the way they do on the screen, because they don’t include the gridlines and the headers. (I’m talking here about the sheet headers — the letters at the top of the columns, and the numbers at the left of the rows.)

Occassionally, however, you actually want it to look like a spreadsheet. Given my background the most obvious reasons I can think of to do this involve either showing someone how to do something in a spreadsheet, showing someone where something is located in a spreadsheet if there are lots of tables on a single sheet, or else using a printout to verify/check formulas. For the third option you’ll also need to be able to print formulas, and I’ll talk about how to do that next week.

Not all programs will give you the option to print out the headings and gridlines. For those that don’t, if you need to have those the best way is probably just to take a screenshot and print that out. However, there are some programs that make it very easy to include those things in the printout if you want them.

Here’s how to make various programs print a spreadsheet so it looks like a spreadsheet.

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 middle section, labeled “Print”, mark “Gridlines” and “Row and column headings”.
  5. Click “OK”.

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, and click on the arrow in the lower right corner or type sp.
  3. Click on the “Sheet” tab.
  4. In the middle section, labeled “Print”, mark “Gridlines” and “Row and column headings”.
  5. Click “OK”.

-OR-

  1. Click on the “Page Layout” tab or type alt-p.
  2. Locate the “Sheet Options” group.
  3. Mark the “Print” boxes under “Gridlines” and “Headings”.

-OR-

  • Type alt-p-pg to toggle gridlines printing.
  • Type alt-p-ph to toggle headings printing.

In Google Spreadsheet:

Google Spreadsheet does not seem to allow you print the sheet headers, and prints the grid by default. You can remove the grid by marking the box next to “No gridlines” on the print screen.

In OpenOffice Calc:

  1. Click on the “Format” header or type alt-o.
  2. Click on “Page” or type p.
  3. In the middle section, labeled “Print”, mark the box next to “Column and row headers” and the box next to “Grid”.
  4. Click “OK”.

In Microsoft Works Spreadsheet:

  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 “Other Options” tab.
  4. Mark the box next to “Print gridlines”.
  5. Mark the box next to “Print row and column headers”.
  6. Click “OK”.

Printing: Print order

The printing options and tricks I’ve discused over the last group of posts are definitely not all the options available — they’re just the ones I use on a regular basis. However, spreadsheets have a number of other printing quirks as well. Today I’m going to talk about selecting what order the pages are printed in, since it’s not as simple as in a word processor.

In a word processor, the end of a page is its bottom, and to continue reading you should go to the top of the next page. But in a spreadsheet a page might have two “ends” — one at the bottom, and one to the right. So some spreadsheets let you dictate whether it prints “down first” or “across first”. If it’s “down first” then page 2 starts at the bottom of page 1, and page 3 starts at the bottom of page 2. If the spreadsheet is only 3 pages long in that direction then page 4 will start immediately to the right of page 1, and page 5 will be below 4 and to the right of 2. Conversely, if it’s across first then page 2 starts to the right of page 1, and when it runs out of pages to the side it goes down to the page immediately below page 1. This might not seem important, but when you’re printing it out and giving it to people in a bound report of some sort, you want them to be able to find the “next” page quickly and easily.

What the “next” page is depends entirely on what information they’re following. For example, imagine that you’ve got a lot of census information about the 50 states in the US, and that the states are the rows and the different statistics are the columns.

If you expect people to be looking for all the statistics about a particular state, then you want it to be easy to follow a single row. Therefore it should be “across first”, so they can flip from the first part of the data about that state to the 2nd part without having to skip pages.

On the other hand, if you expect people to be comparing states to each other then you want it to be easy to follow a single column of statistics across all the states. That means page 2 should start immediately below page 1, which means it should be printed “down first”.

Most programs default to “down first”, and of course the ones that let you choose all do it differently.

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 last section, labeled “Print order”, click on “Down, then over” for “down first”, or “Over, then down” for “across first”
  5. Click “OK”.

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, and click on the arrow in the lower right corner or type sp.
  3. Click on the “Sheet” tab.
  4. In the last section, labeled “Print order”, click on “Down, then over” for “down first”, or “Over, then down” for “across first”
  5. Click “OK”.

In Google Spreadsheet:

Google Spreadsheet does not seem to have a way for you to dictate what order to print the pages in.

In OpenOffice Calc:

  1. Click on the “Format” header or type alt-o.
  2. Click on “Page” or type p.
  3. In the top section, labeled “Page order”, choose “Top to bottom, then right” for “down first”, or choose “Left to right, then down” for “across first”.
  4. Click “OK”.

In Microsoft Works Spreadsheet:

Microsoft Works does not seem to have an option for this.

Creating auto-updating tables

The last thing I mentioned on my post about printing was creating automatically updating duplicates of your tables.

Sometimes, when I’m working with a lot of data and doing a lot of synthesizing, I want to make a table on the same worksheet at the data I’m synthesizing from. It makes formulas easier to make and to read, for one thing, and those useful little borders around cells that you’ve used in your formula are only useful if you can see them when you’re editing it.

However, when it comes to printing, I might want to print that table on the same page as some other tables, all of which were made on the same worksheets as their relevant data.

I could just copy the tables and paste them as values and as formats in another sheet. (If I just pasted them normally and I had any references that weren’t named variables then the results would be guaranteed to be messed up because they’d no longer be pulling data from the correct cells.) But if I do that, then I’ll have to do it every single time I’m ready to print after the data’s been changed.

A better solution is to create tables that are automatically updated every time the data in the orignal table changes. Fortunately this is really straightforward and easy to do, once you think about it.

  1. Insert/create a new worksheet, if necessary. Instructions are here. I recommend naming the worksheet soemthing like “ToPrint”.
  2. Select and copy the original table.
  3. Go to the new worksheet.
  4. Select where you want the table to be. You don’t have to select all the rows and columns that will be in the table — just the one that will be in the upper left corner is sufficient.
  5. Paste special as formats. I haven’t explicitely discussed how to do this, but it’s in the same menu as paste special as values. You should now have a blank area with whatever borders and shading you had in the original table. You’ll also have the text formatting, but it won’t be visible since there’s no text.
  6. Go back to/select the upper left corner of the blank table. Actually this can be any cell in the table, but I recommend that it be one of the corners.
  7. Enter a formula to make it equal the corresponding cell in the original table. For example: if your worksheets are ToPrint and OriginalData, and both tables start in the first row & first column, then in A1 of ToPrint you’d enter the formula “=OriginalData!A1” (without quotes). You can do this by simply typing = in the cell and then (without hitting tab or enter) clicking on the “OriginalData” worksheet, then on the cell A1 in that worksheet. Then you can hit enter or tab to tell the program you’re done editing the formula in that cell. If the cell you’re referring to doesn’t have any data in it, then you might see a zero instead of a blank area. That’s okay — we’ll fix it later.
  8. Copy the cell with the formula. Just select that cell and hit ctrl-c, or however you prefer to copy. Do not go up to the formula bar and highlight/copy the formula there. If you copy the formula itself, rather than copying the cell with the formula, then after you do the next step you’d end up with a table wherein every entry is equal to the first cell of the original table.
  9. Select the entire area where you want the auto-updating table to be. This is why you start by pasting the formats, rather than finishing with that step — with the formats already there, you can easily see where the edges of the table are.
  10. Paste special as formulas. Again, this option will be in the same menu as paste special as values. If you use regular paste than you’ll be overwriting the formats of the other cells.
  11. Delete any formulas in cells that are supposed to be blank. Sometimes I’ll have a blank cell in the upper left, with column headers to its right and row headers below it. But if you make one cell equal to an empty cell (one with nothing in it, as opposed to one with =””) a lot of programs will give you a zero (0). So you need to get rid of cells like that. You could avoid this step by only doing step 10 into cells that are supposed to have data. However, I find it easier to paste once and remove the excess than to paste once over part of a table, then again over a different part.

That’s it! Now, every time your data changes, this table will change as well, and it’ll stay on a clean sheet that’s easy to print.

Printing: Printing only part of the sheet

One of the things I do in my regular job involves generating statistics to go in tables to go in reports. Quite frequently I end up doing multiple layers of synthesis: I’ll do one, then use the results of that to do another. However, I only need the final results for the report, not all the in-between steps. If all the steps are on the same page, though, if you don’t do anything in particular then they’ll all get printed together.

One obvious solution is to put the final results in a separate worksheet. Normally I would highly commend this method of dealing with the issue, but since my report spreadsheet already has over 20 worksheets adding more just makes it harder to navigate.

Another possible solution is to move all of the unwanted data down and/or to the right, so that only the parts you want to print would naturally fall on the first page. Then you can tell the program to just print page 1 of that worksheet. However, anything you do to the worksheet after that (adding/removing columns/rows, changing font size, adding notes or a new intermediary table) could potentially mess it up, so you have to be careful from then on.

There’s also a third solution: define the area to be printed. When you define the “print area” for a worksheet, only the cells in that area will be printed, with the exception of repeating headers if you’ve defined any.

There are a few things that are true across most programs.

  • Your “print area” can be any size from one cell to the entire worksheet.
  • You can only have one print area per worksheet.
  • Your print area must be contiguous – all the cells in it must touch each other.
  • Once you set your print area, it would be outlined by the same style of border that’s used to mark your page breaks, or the edges of your page after you’ve previewed how the printout will look. The cells that won’t be printed may be greyed out in some way.
  • Unless otherwise specified, you can remove the print area restriction in the same menu that you set it in.

Beyond those commonalities, every program has its own way of doing this.

In Excel 97:

  1. Select the cells that you want printed.
  2. Click on the header for the File menu or type alt-f.
  3. In the menu that opens, click on “Print Area” or type t.
  4. In the menu that opens, click on “Set Print Area” or type s.

In Excel 2007:

See Excel 2010.

In Excel 2010:

  1. Select the cells you want printed.
  2. Click on the “Page Layout” tab or type alt-p.
  3. Locate the “Page Setup” group, and click on “Print Area”. It doesn’t matter if you click on the picture or the arrow below it.
  4. In the menu that appears, click on “Set print area” or type s.
  5. Halfway down the “Page” tab, there’s a section for scaling. You can use the options there to shrink or expand the printout, either by changing the percentage or by specifying how many pages tall and how many pages wide the printout should be.

In Google Spreadsheet:

Google Spreadsheet does not seem to allow you to define print areas, but it does allow you to just print the cells you’ve selected. It’s more tedious because you have to do it every time you print, but if you’re only going to print it once it’s pretty much the same effort.

  1. Select the cells you want to print.
  2. Click on the “File” header, then on “Print” OR click on the printer icon OR type ctrl-p.
  3. In the window that appears, in the Options section near the top, on the left, change it from “Current sheet” to “selection”.
  4. Click “OK”.

In OpenOffice Calc:

  1. Select the cells you want printed.
  2. Click on the “Format” header or type alt-o.
  3. Click on “Print Ranges” or type n.
  4. Click on “Define” or type d.

In Microsoft Works Spreadsheet:

See Excel 97.