Printing Two Worksheets on a Single Page

Written by Allen Wyatt (last updated December 3, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


8

Brian has a workbook that contains two small worksheets. He would like to print the two worksheets on a single landscape page. Brian sets the printer's "pages per sheet" setting to 2, but the worksheets still print on two separate pages. He wonders why this happens and how he can get both worksheets on a single printed page.

The important thing to remember here is that you are dealing with two separate "paradigms" of the printed page—the one used by Excel and the one used by your printer driver. This can make it tricky to get exactly the output you want because it isn't always clear whether it is Excel controlling the output or whether it is your printer driver.

Brian indicates that he sets the printer's "pages per sheet" setting to 2, which means that he is, within Excel, displaying the print options (Ctrl+P) and then clicking on Printer Properties. The dialog box that is then displayed—where Brian can set how many pages to print per sheet—can differ wildly from one printer to another; it is entirely under the control of the printer driver.

On my printer setup, I can select multiple worksheets, press Ctrl+P, click on Printer Properties, and set the number of pages per sheet. When I then print, I do get the proper printout (just the selected worksheets), scaled to the right amount to print 2, 4, or however many pages per sheet I want. If Brian is not getting that, then the cause has to be with his printer driver, not with Excel, because it is the printer driver that is doing the scaling and printing on the sheet. Excel is doing its duty by sending individual pages to the printer driver, then the printer driver is supposed to take care of the rest.

So, in Brian's case the first thing to check out is whether the latest printer driver is installed on the system. If it is, then I would take the workbook to a different computer that uses a different printer entirely and see if the same results occur. (This is advised because the different computer/printer combination would use a different printer driver than what Brian is using.)

An important thing to remember is that it is entirely possible that your printer driver is resetting itself. For instance, let's say that you set up your printer driver to print multiple pages, then you go back to your worksheet to make a change or two or even to do something related, like selecting the worksheets you want to print. When you then again display the Back Office area to get ready to print (by pressing Ctrl+P), your printer driver may have reset itself to print single sheets. You can check this out by clicking the Printer Properties link every time you display the Back Office print settings and double-checking that it is set the way you want.

If updating the printer driver doesn't work, using a different computer isn't a viable option, and the printer driver isn't resetting itself, then you will need to look at how you are creating your output and come up with a different approach. You can find three or four different ideas you can use in this tip: Printing Multiple Worksheets on a Single Page.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12998) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Hiding Errors

If you find the green and red squiggly underlines that Word adds to your document distracting, you might want a quick way ...

Discover More

Editing the Custom Spelling Dictionaries

Excel provides spell-checking capabilities on the text you enter in a worksheet. It utilizes the same dictionaries and ...

Discover More

Detecting Types of Sheets in VBA

When processing workbook information in a macro, you may need to step through each worksheet to make some sort of ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Controlling the Printer in a Macro

Need to access the advanced capabilities of a printer from within an Excel macro? You may be out of luck, unless you ...

Discover More

Scaling Your Printing

If you want to cram more of your worksheet onto each page of a printout, one way to do it is by using scaling. Here's how ...

Discover More

Preventing Changes to Multiple Selected Worksheets

It is common to select a group of worksheets and then print them. When done, any edits you make may affect the entire ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 9?

2022-12-04 19:05:39

Tomek

"One more thing" (a quote from Lt. Columbo):

Alternatively, you can print your two page hardcopy to a pdf, then use your pdf reader to print that file to your printer using two pages per sheet.

Another complication I noticed was that if each page was set-up to landscape orientation, the two-pages per sheet output would be a portrait page with two landscape half pages one above the other, not exactly what Brian wanted. On the other hand, if each page was set-up to portrait the result would be a landscape page with two parts of the spreadsheet side by side, fulfilling what Brian originally wanted. However, when I tried it with the content of each page squeezed to fit the margins (set to narrow), the two-per-page printout cut off the right-hand side columns, unless I feed a legal page into my printer.(i did not needto set the printer paper size to legal). The reason seems to be that two side-by-side letter-size pages scaled to fit 8 1/2" on a single letter-size page extend beyond the 11 inches available. This may be specific to my printer EPSON WF-7510.

All this weirdness just indicates to me, that although you may get what you want from just your Excel with proper printer settings and a lot of tweaking, you don't have full control of what's going on. Hence using other tricks suggested in the comments or in the older tip (see the link at the end of Allen's tip) is probably better. I feel that creating a "printing worksheet", especially using Excel camera facility, gives the user most control and flexibility over the final result, other than maybe using a third party software.


2022-12-04 18:01:57

Tomek

As a follow-up to Ron S's comment, not only you can have the same printer installed with different drivers, but you can also have multiple virtual printers installed in Windows 10, all using the same printer driver and the same port, but each of them can have different settings, including Pages-per-sheet. See https://www.howtogeek.com/362957/how-to-install-the-same-printer-twice-with-different-settings-on-windows/

Once you have done this and set the desired setting for each printer (this has to be done in Windows, not in Excel) you can select the desired printer at the print time within Excel. When you do this, the default printer preferences are applied to all pages to be printed, even if the content originates from different worksheets. This is the way I mentioned in my yesterday's comment to overcome the idiosyncrasy of Excel.

It works dependably when you switch in Excel from one printer to another, and not always when you use the same printer as previously, so if in doubt switch to a different printer then back to the desired one.

Another convenient use for having the same physical printer set up as two different virtual printers is for single and double sided output.


2022-12-04 10:35:52

J. Woolley

The Camera tool (see Mike J's comment below) is described in these articles:
https://excelchamps.com/blog/camera-tool/
https://trumpexcel.com/excel-camera-tool/
https://excelribbon.tips.net/T008189_Multiple_Print_Areas_on_a_Single_Printed_Page.html
For an equivalent of the Camera tool, copy (Ctrl+C) a region of the active sheet, then select another cell on any sheet and pick Home > Paste > Linked Picture (Alt+H+V+I).
My Excel Toolbox's DynamicImage macro is similar to the Camera tool. It copies a range of cells and pastes it as a dynamic image in any sheet of any workbook. The image includes cell values plus visible portions of shapes or charts from the copied range. Any changes in the copied range will be reproduced in the dynamic image. The result is a simple dashboard.
See https://sites.google.com/view/MyExcelToolbox/


2022-12-04 04:37:23

Mike J

I am another huge fan of Fineprint which, as Willy says, does much more than just multiple pages reduced to one page. However, I'm amazed that the only mention of the Camera facility is a brief reference in the linked page at the bottom of the tip.

The advantage that this facility is that the separate pages don't even have to be the same size, so you could have one worksheet which uses say 2/3 of the sheet and the other, the remaining 1/3.

Another huge advantage is that you can rotate and scale camera images allowing, for example, automatic portrait and landscape being printed double-sided.


2022-12-03 19:28:49

Ron S

Printing in Windows is more complex than you think. As Allen indicated the application formats the document according to it's own rules. The rules that the application use include limitations and features offered by the specific "Printer Driver", aka "Printer" in Windows, that you select. You can have multiple printer drivers installed that all send to the same physical printer. A common Limitation applied by the printer driver is the minimum margins, a reflection of the physical printer hardware limits. A common feature enabled in the driver is "Duplex" / 2 sided printing. You could have a printer driver installed, that works with the printer hardware, but the printer driver is not aware that the hardware supports 2 sided printing, so the feature is not offered.

Updating the printer driver is not likely to work. The newer version would likely continue to work the same way.
.
You can have multiple "printer drivers" installed in Windows. Then when you are printing you can select which specific "Printer" you want to use
.
On the other hand, go to the printer maker website and see if they have any other printer drivers that will work on your printer. For example, my older HP printer had separate printer drivers to work using HP PCL (Printer Control Language) and PostScript (another PCL) that the printer hardware supported. As well they had a "Universal" printer driver that replaces hardware specific drivers with a single driver for most of their printers. So I could have all 3 printer drivers installed in Windows, showing up in the print dialog as different "Printers" that all work with the same printer hardware.
.
If that doesn't work, try VARIOUS "PDF" printer drivers, including the Windows PDF printer. There are several free ones you can get. You may be able to print to PDF (file) then print to paper to get the feature to work (but probably not in this case).


2022-12-03 11:36:22

Tomek

There is one more idiosyncrasy for Excel/printer properties that may explain Brian's inability to get two pages on one sheet, at least in my MS365/Win10.

Excel seems to set printer properties per worksheet, so if two print ranges come from two different sheets, pages per sheet may be set to 2 for one range and to 1 for another. To check this, go to page preview and check printer properties for each page to be printed. Use navigation buttons on the bottom to switch pages. On my system, switching it for one previewed page affected only pages originating from the same sheet and did not communicate to other sheets! It is really hidden from the user.

There is another workaround for this, but i am running out of time now, will post another comment later.


2022-12-03 06:49:34

Jim Foote

When all else fails, there is a nifty utility called FinePrint (https://fineprint.com) that does the job very nicely. I've used it for years with several different programs and printers and it's been a lifesaver for me (not to mention a paper saver).


2022-12-03 05:41:19

Willy Vanhaelen

If you have to do it often then an easy solution for such situations is to buy a printer application such as "Fine Print" (www.fineprint.com). You print to Fine Print and it gives you a preview of what you want to print and then gives you the opportunity to manipulate it, such as print 2, 4 or 8 pages/worksheets on one page, repeat pages, add a letterhead, make a "booklet" and much more...

I use this program for many many years now and coun't do without it.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.