For business users of Microsoft Excel Free guides and templates
Home >  Excel Reports > 

Report Distribution

How to Save Multiple Excel Reports to One PDF File

You can save any number of Excel reports to one PDF file using only Excel. Here's how to do it.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

A great way to distribute your Excel reports is to save them in a PDF file and then distribute the file.

People who receive your PDF file can read it on their desktops and mobile devices, and they can print it easily.

The only limitation is that without using a third-party add-in, all reports must be in the same workbook.

How to Prepare Your Reports for PDF

To prepare your reports for PDF, you take the same steps that you would to print your reports to a printer. That is, you assign print areas to all areas of your workbook that you want to print or save as PDF.

To assign the first print area in a worksheet, select the area and then choose Page Layout, Page Setup, Print Area, Set Print Area.

To assign another print area in the same worksheet, select the area and then choose Page Layout, Page Setup, Print Area, Add to Print Area.

Next, it’s usually a good idea to assign the same tab color to each worksheet that you want to print or save to PDF. Let’s suppose you want to assign purple tabs as your “print” tabs.

To do so, right-click on a tab, choose Tab Color, and then, in the Standard Colors area, choose the purple square. Then assign the same purple color to the tabs of your other worksheets to which you’ve assigned a Print Area.

If you have more than one purple tab, they’ll be easier to work with if you arrange them side-by-side. So if your purple tabs are sprinkled throughout the other tabs of your workbook, click and drag the purple tabs so they’re all side-by-side.

At this point, before you print your reports, or save them as a PDF file, it’s a great idea to save your workbook. You’ll understand why in a moment.

How to Print-Preview Your Reports

Before you print or save your reports as a PDF file, it’s a good idea to print-preview them. To do so, first select all worksheets with purple tabs.

To do so, click the left-most purple tab, hold down your Shift key, and then click on the right-most purple tab. After you do so, notice that the title of your document window has changed from something like MyReport.xlsx – Microsoft Excel, to MyReport.xlsx  [Group]– Microsoft Excel.

To print-preview your reports, choose File, Print or press Ctrl+p, which will take you to the Print area of Excel’s Backstage view. The right side of your screen will show the print-preview of the first page of your report.

You can use the scroll bar at the right to scroll through the other pages of your report images. You also can click on the print-preview image and then use your Page Down and Page Up keys to scroll down or up through your report images.

If you see a problem, press the Esc key to return to the Ready mode. Then—and this is important—immediately ungroup your selection of worksheets. To do so, select any non-purple tab. Or, if all tabs are purple, select any tab other than the active one.

The reason it’s important to ungroup your worksheets is that when you make a change to any worksheet in a group of worksheets, you make the same change to all worksheets in the group. If that’s what you WANT to do, it can save you a lot of time. But if that’s what you DON’T want to do, it can really mess up your reports.

If you forget to ungroup your worksheets, and then make changes to one of them, you have two ways to recover. First, if you haven’t made many changes, you can press Ctrl+z as many times as necessary to undo the changes you’ve made. Or, you can simply close your workbook without saving it, and then open it again.

(This is why I asked you to save your workbook before you print-previewed it.)

How to Save Your Reports as PDF or Print Them

After you’ve print-previewed your reports, and they look okay, it’s time to print them or save them as PDF.

To print them, choose File, Print or press Ctrl+p again, make sure the printer settings are correct, and then choose Print.

To save them as PDF, first choose File, Save As, and then, in the Save As dialog, choose PDF (*.pdf) as the Save as Type. After you do so, the Save As dialog expands to include three additional choices.

First, you can choose whether to make your file the standard size or the minimum size for publishing online. You might want to experiment with these options to see whether the smaller size will work for you as a general rule.

Save as PDF Options dialogSecond, you can choose the Options button, which launches the Options dialog shown here, with its default settings shown.

The most important choice in this dialog is to make sure that Active sheets is selected. Otherwise, if Entire workbook is selected, you’ll include all non-report worksheets in your PDF.

You have three additional choices:

Document properties. When you select this check box, Excel includes title, subject, author, and similar information in the file.

Document structure tags for accessibility. This check box is selected by default so that your PDF file is more accessible to disabled users. Clear this check box if you want your file to be as small as possible.

ISO 19005-1 compliant (PDF/A). According to Wikipedia, “PDF/A is an ISO-standardized version of the Portable Document Format (PDF) specialized for the digital preservation of electronic documents.

“PDF/A differs from PDF by prohibiting features ill-suited to long-term archiving, such as font linking (as opposed to font embedding).

“The ISO requirements for PDF/A file viewers include color management guidelines, support for embedded fonts, and a user interface for reading embedded annotations.”

The PDF/A setting also disables transparency and certain other effects. When transparency is disabled, the objects that formerly were transparent can become solid black in your PDF.

You would choose this setting only if the file is for a government agency that requires it, or if you need the file to be suitable for long-term archiving. But you’ll need to double-check the saved PDF file to make sure that it’s readable.

After you’ve chosen the settings you want to use, choose Save. When you do so, Excel saves a copy of your file as PDF, but leaves your original worksheet open in Excel.

Finally—and this is important—be sure to ungroup your worksheets if necessary. That is, if more than one worksheet is selected, click on the tab of a sheet that’s not selected. Or, if they’re all selected, click on the tab of a sheet that’s not active.




Tags: #excel, #PDF, #Print, #Print-preview, #save as PDF, #multiple Excel reports, #Excel reporting

Will Tablets and Readers Transform Management Reporting with Excel?

Free Excel Dashboards

Click to see customer testimonials

Charley's SwipeFile charts