Printing from Excel - Part 2

In the last post I showed how to format an Excel sheet so that it fitted to the page and added page numbers. The end result as displayed in Print Preview is as shown here.

As we can see there are no gridlines on the print. We can either remedy this by selecting border types on the table or, more easily, we can set a print option to show simple gridlines. To do this go to Print Preview, click the 'Setup...' button and select the 'Sheet' tab. Here, I select the Print Gridlines option, as shown.

After I click 'OK', the Print Preview looks like this. I have zoomed in on the preview so that you can see the gridlines more clearly.

The above is a preview of page 2, where we can see that there are no column headers as there are on page 1. We can tell Excel to print the column headers on each page to make the print of the table more user-friendly. To do this I need to close the Print Preview, and select 'Page Setup' from the 'File' menu, which enables some extra options. On the 'Sheet' tab I click the circled button.

This opens the following option box. I now click the row that I want to repeat. In this case it is row 1, so I click the appropriate button as circled in red, and close the option box by clicking the button circled in green. This will enter the appropriate code into the 'Rows to repeat at top' option. After, I click 'OK' I have a well formatted spreadsheet ready to be printed.

As you can see there are a number of other options that I have not explored here. If you have a document that prints on many pages you may want to click the 'Margins' button and adjust the size of the print area by clicking and dragging the various dotted lines, but be careful to leave enough of a margin around the table so that the print is not ruined - at least a centimetre (or about half an inch) should be enough. If you have a particularly wide document you may not be able to fit it onto one page width, in which case you can also set the print to repeat columns in much the same way as I did for rows. Printing Excel spreadsheets may be a fiddly business, but with a little effort you can achieve some very good results.

Printing from Excel - Part 1

Printing from Microsoft Excel is markedly different to printing from Word. Whereas in Word we can usually just tell it to print without worrying that much about formatting the page, in Excel we will usually have to specifically tell it how we want to print our speadsheets, and this can sometimes be a little fiddly. Because there is quite a lot of fiddling that can be done I have split these instructions over two posts.

Pictured above is an example of a spreadsheet - in this case a dataset of U.S. population information. Exciting, I know. When I press the Print Preview button [pictured right] I see the following:

As we can see, not all the columns are shown on the first page. If I go down to page 6 we can see the missing column.

To remedy this situation I click the 'Setup..' button in the Print Preview. This opens up the following option box.

I could change the orientation to 'Landscape' as this would enable the missing column ; however, for the purposes of this tutorial I will keep it set to 'Portrait'. Instead, I reduce the scaling until the final column appears on the first page. In this case I set it to 80% of normal size.

I could also have achieved the same result by selecting all the data and reducing the font size, the column width and row height; although I find adjusting the scaling to be easier. The Print Preview now looks like this:

As yet there are no page numbers shown. In order to have page numbers displayed I click 'Setup..' again and open the 'Header/Footer' tab. Here I can select whether I want the page numbers to be shown at the top or the bottom of the page by choosing an option from the appropriate drop-down list, two of which are shown below.

As you can see from the drop-down lists, there are other options available for what will be displayed in the header or footer, including file name and date. I now have a serviceable format for my document that I can print out. At the same time I have reduced the number of pages from ten to four. There are a number of other print formatting options available that I will explore in the next post.