How to Make a Wide Report Fit to One Page Wide by Many Pages Tall
October 15, 2021 - by Bill Jelen
Problem: After I create a wide report, it prints four pages wide. How do I make it print one page wide?
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
