How to Make a Wide Report Fit to One Page Wide by Many Pages Tall

October 15, 2021 - by Bill Jelen

How to Make a Wide Report Fit to One Page Wide by Many Pages Tall

Problem: After I create a wide report, it prints four pages wide. How do I make it print one page wide?

Initially, this very wide report will span four pages if you print in in portrait mode
Figure 121. Taping four pages together leads to copier jams.

Strategy: Ultimately, you will set the Scale to Fit settings to print to one page wide by any number of pages tall. Before you can do that, you should follow these steps:

  • 1. Eliminate extra columns from the print range. Because this worksheet has some lookup tables beyond column X that you do not want to print, highlight columns A through X and select Page Layout, Print Area, Set Print Area.

  • 2. Set long headings on two lines rather than one. For example, Sales Rep in cell D5 could be on two lines to save width in the column. In cell C5, type Sales, press Alt+Enter, and type Rep. Do the same thing for Prior Year in X5.

  • 3. Make the columns narrower. Select the data in A5:X130 and then select Home, Format, AutoFit Column Width. Gotcha: The AutoFit command does not deal well with cells in which Alt+Enter was used, as in step 2. You therefore have to manually adjust the column width of columns D and X.

  • 4. Change the orientation to Landscape by selecting Page Layout, Orientation, Landscape.

  • 5. Adjust the margins by selecting Page Layout, Margins, Custom Margins. On the Margins tab of the Page Setup dialog, set the top, left, and right margins at 0.25 inch. Adjust the bottom to 0.5 inch and the footer margin to 0.25 inch. Alternatively, use Print Preview and click the Show Margins icon in the lower right. You can now drag the margins to a new location.

  • 6. On the Page Layout tab, open the Width dropdown in the Scale to Fit group. Choose 1 page. (This is much easier than using the Page Setup dialog, as discussed in the following alternate strategy.)

Results: The report will fit on one page wide and three pages tall.

Alternate Strategy: You can use the Page Layout dialog to indicate that the report should fit to one page wide by <blank> pages tall. On the Page Layout tab of the ribbon, click the dialog launcher in the lower-right corner of the Page Setup group. Choose the Page tab of the Page Setup dialog. Choose Fit To. Leave the first spin button at 1 Page(s) Wide. Using your mouse, highlight the 1 in the spin button for Tall. After the 1 is highlighted, press Delete to leave this entry completely blank. Before Excel 2007, you followed this rather convoluted process to create a setting equivalent to step 6 above.

See Also: "How to Fit a Multiline Heading into One Cell" on page 269.

This article is an excerpt from Power Excel With MrExcel

Title photo by Redd on Unsplash

Bill Jelen is the author / co-author of:
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.