Excel doesn’t print all active sheets to PDF

microsoft excelpdfpivot tableprint to pdfprinting

Update: added issue on Feedback Hub: https://aka.ms/AA65nqn

When I select sheets (Shift+Click to multiselect) in a macro-free workbook that each may or may not span multiple printed pages, select File > Print (using Microsoft Print to PDF, selecting "Print Active Sheets"), all pages don't get included in the PDF.

This happens when using File > Save As (PDF) as well.

Specifically, the last couple of sheets/pages are omitted from the result, which is a huge issue, since it basically renders the file useless for its purpose (= PDF report generation).

File has mixed orientation (Landscape/Portrait), many of the sheets span multiple printed pages, and the last two sheets are static one-pagers that are – even though I selected them – not included in the PDF.

I can reproduce the issue consistently with this example file.
Example PDF output also available (note the two last sheets are missing). The last page looks like this:

Last page of example file which doesn't get included in PDF

Check the last page of the resulting PDF. It's not there!

Environment

  • Excel version 1906 (16.0.11727.20222 32-bit)
  • Windows 10 Pro 64-bit

Things I've checked for/tested:

  • All pages have same print quality (was noted as a potential issue elsewhere)
  • Using regular "Save As" and selecting PDF gives the same (bad) results.
  • CutePDF writer gives the same (bad) results.
  • Printing to a physical printer prints all pages (good!)
  • If I select only the last couple of sheets, they get included in the PDF as they should. As I expand my sheet selection and print to PDF, at some point it starts "losing" pages at the end of the document.

Observations/clues:

  • Total page count varies throughout the PDF document. See page 21 and 22 in the example PDF linked above, it says "Page 21 of 31", then "Page 22 of 30" (similar oddity can be observed with CutePDF)
  • Sheets with pivot tables that span multiple sheets seem to be the primary culprit: selecting these for printing seems to have higher likelihood of messing up the output.

I think this is mainly an issue with the print drivers (Microsoft Print to PDF or CutePDF) since printing to a professional printer works as intended, but I could be wrong.

What could be wrong, and how do you suggest I mitigate this? I need the PDF output; this is the primary purpose of the Excel file.

Best Answer

I have used your example file to successfully duplicate the problem and I did find a solution.

The problem with this spreadsheet is that you have mixed orientations : Some sheets have the Landscape orientation while others are in Portrait. While printing, Excel forces you to specify one orientation and this does not work, for two reasons:

  1. Printing in Landscape Orientation will print all the selected sheets, but everything will be in Landscape Orientation, even the sheets marked as Portrait.

  2. Printing in Portrait Orientation will stop upon reaching sheet "Sheet2 (6)" which is too wide to print in Portrait orientation. Reducing the column-widths in "Sheet2 (6)" can let it print in Portrait Orientation, except that some columns will be truncated.

Excel simply does not let you print in mixed orientation, which I believe is an oversight of its developers, not adding an option for this case.

The way to generate a PDF file with variable Orientation is not to use a PDF printer. Use instead the ribbon File > Save As and save the selected sheets in PDF format. This will save the sheets, each with its own correct orientation.

Related Question