Learn how to make sure that printing multiple sheets to a pdf gives one pdf and not many
If you have ever tried to print a large Excel workbook to a PDF file, you’ve probably run into this issue. You press print, Excel asks you to name the PDF, and then it begins to print. Everything seems fine, but then Excel asks you to name another PDF, then another, then another, ad infinitum. When the operation finally finishes, Excel has properly printed the workbook to a PDF format, but your worksheets have been split into several different PDF files. Some PDFs contain multiple worksheets, others only a single one, but all you really wanted was one PDF file with the entire workbook.
It turns out this issue is caused by having different Page Setup options on each worksheet. For example, Excel can’t print two pages with different paper sizes to the same “piece of paper” (actually a PDF in this case). Instead, it insists on having two different PDFs to print to, one for each paper size. So, to resolve this issue, you must make sure each worksheet’s page setup agrees with the others.
Fortunately, doing this is very simple. To begin, in your Excel workbook, right click one of your worksheet tabs at the bottom of the window and choose Select All Sheets. Any changes to the Page Setup options will now be applied to every worksheet.
This means we don’t have to check each worksheet to make sure it has the same settings as the others; we simply choose which settings we want and all the worksheets will automatically match. To do this, go to the Page Layout tab in the ribbon. In the Page Setup section, click the small arrow in the bottom right corner to open the Page Setup dialog.
The Page tab of the dialog contains the critical options that can lead to this issue, namely the paper size and print quality settings. Change these to whatever you wish, typically something like letter paper at 300 dpi. Other settings, such as orientation and scaling, do not cause the multiple PDFs issue so if you wish you can change them for individual worksheets. Still, it’s best to have all worksheets print with the same settings. Once you have chosen your desired settings click OK and they will be applied to every worksheet.
You can now print your workbook to a single PDF file.
Hi,
Your solution was very easy to understand and worked first time – saved me a few more grey hairs!!! Thanks Alan
Thank you thank you thank you!
This is a great solution and probably best if you want to ensure consistency and uniformity from page to page. However, if your workbook has pages of varying sizes (say, letter and legal), another solution is to select the worksheets you want to print into one PDF, then select File | Save as … and choose PDF from the drop down list. This will create one PDF with all of your selected pages without having to have the same paper size and quality.
Please make one more write-up about pdf book
THANK YOU!
Thank you Cathy Dobbins your suggestion is what I was looking for. My Excel charts have various page sizes and setups. I needed a solution that would allow me to save the entire workbook as one PDF with all the various sized pages. Thanks.
thank you thank you thank you
Thank you very much. Saved me a lot of time. This is the first time in years that I was stumped. Very best wishes.
Bill
Thank you so much for posting this solution. Such an easy fix to a vexing problem!
Thanks so much- was wondering what happened to make it behave this way all of a sudden
Thank you very much Helen and…
Cathy Dobbins thank you very much too! This is the best answer i found after looking for this solution in so many sites.
Users wanted to keep the format of all the workbooks
You da MVP!
David,
This is useful to know, but I am looking for the opposite solution. I want to print the lot of worksheets but not as a single pdf. For one or two sheets it is ok to print pdf one by one, but even this becomes annoying when you realise there was slight error and you adjust and reprint – each one again, never mind to do this for hundred worksheets.
I would like to be able to print out several worksheets as pdf in one go but separate files. Additionally I do not want to have to name each worksheet pdf being saved, as Excel is using the same name for each sheet, which is the ‘filename’ itself – Also annoying. ‘Filename_worsheet’ would be good. Is there a way to do this. Additionally to take this a step further I would like to be able to set a default print directory for each worksheet. This would save me a MASSIVE amount of effort and time.
You’re awesome! Way to go.
Thanks a lot, works perfectly
Thanks Helen, this worked wonders!
Thank you! This is exactly the solution I was looking for! Print quality was different. Worked perfectly.
THANK YOU!!!! Oh my goodness. I was struggling to fix this, and was going along the same line of thought as you set out, but it’s 20:43 my time and I’m tired, and I was trying to set up each page individually. Your solution worked so fast! Thank you 🙂
Thank you. As with others, grey hairs saved and a clearly described solution.
Found my solution!! thank you so much for writing this!
Really appreciate you sharing this info. Easy and solved the issue I was having.
Chris
Brillant, thanks so much for the easy to understand explanation and fix!
Thank you, thank you, thank you 🙂
This was really doing my head in!
This worked perfectly! You have no idea how much ache and pain has been released.
Great solution!!