One of my biggest pet PDF peeves is converting large Excel spreadsheets to PDF. None of the settings I’d expect to work, such as printing just the selected cells or adjusting the print margins do a thing.
The problem appears when converting spreadsheets containing a large amount of rows, columns or both. Excel decides on the margins based on a standard paper size, but to make spreadsheets really usable once in PDF or paper form, often you need to shrink, expand, crop or adjust to get each sheet looking right.
Below I’ll look at a few ways to adjust things yourself, including a trick to manually set pages to break up pretty neatly. There’s a certain amount of trial and error in all this, but hopefully it’ll help you get out of those tough situations when trying to convert big spreadsheets to PDF. I’ve been tearing out a lot less hair lately!
These methods should work with any good PDF creator.
Print Area setting
In Microsoft Excel 2007 you might be tempted to play around with the Print Area settings (it is also included in previous versions of Excel). I wouldn’t don’t bother with it. It won’t help.
Adjust Page Breaks
Adjusting the page breaks is a nice quick solution when you find that a few columns or rows are being cut off and included on an extra page in your PDF. For me it works pretty well in Microsoft Excel 2007 but I’ve had less success with older versions of Excel.
Click the screenshot to see a page break being adjusted.
The basic idea here is for you to show Excel where the width and height of pages should be.
- Open the file in Excel.
- In the View ribbon tab, in the Workbook Views group, click Page Break Preview. (The page breaks should now be visible.)
- Click on and drag the page break to include more or less columns or rows.
- Once adjusted, use your PDF creator to make your PDF.
Scaling and page fitting content
The page scaling and fitting options have saved my hair on a lot occasions so I’m surprised that such useful functionality is buried away so deeply. There is more trial and error involved here, but with some patience you’ll get your content on the perfect number of pages.
Click on the screenshot to see the scale and fit-pages settings.
- Scale. This setting allows you to shrink or expand the contents of a sheet based on a percentage. If your content is a few too many columns and rows to fit on the normal page, then scaling down here should get your content on the page.
- Fit to pages. Ahhh … this is like an old friend to me — one who’s helped me through some very painful moments! It gives you extra control by allowing you to set the exact number of pages to use, with all content automatically scaling to fit into the pages you specify. For example, if you have a long spreadsheet that will take up 2 and a bit pages, just specify 2 pages and the content will be forced to fit.
You get to these features by heading to the Print Preview dialog. There are several ways to do this, but here’s the most obvious:
- Open the Print dialog
- Click Preview
- Click Page Setup
- In the Scaling group, update your settings
- Click OK
- Click Print
- Choose your PDF printer to convert with
- Click OK.
Hopefully these tips come in use. If you want to read about full Nitro Pro creation functionality, check out the create PDF files feature overview page on the Nitro site.
One Comment
Thanks,