Printing Fit to Page Width

Making printed pages format correctly from Excel can sometimes be difficult.
The most common printing-related request I receive is: “How do I get Excel to print all of my columns on the same page?”

Take Northwind’s Customers table for example.
I’ve just extracted about 6 columns into my worksheet.
If I Autofit the columns, then do a Print Preview, some of the columns end up on a page of their own.

Here is how to fit them all on the same page.

From the File menu, select Page Setup…
On the Page tab, make it so that it reads: Fit to 1 page(s) wide by __ tall.
(The __ is intentionally blank).
Excel will interpret the settings as: Fit to 1 page wide by ‘as many pages as necessary’ tall

As you increase the number of columns, Excel automatically fits them into a print out.
Though, once the text becomes too small to read, you’ll want to flip the Orientation to Landscape mode.

Posted in Uncategorized

20 thoughts on “Printing Fit to Page Width

  1. I knew about this but this is another of those tips that I always seem to take for granted and never share with anyone else.

  2. I bet the second most common printing-related request is: if there are several pages, how do I keep the column headers on each page?

    Page Setup – Sheet – Rows to repeat at top.

  3. I wrote some code to automatically choose the best page setup for a dynamically generated report awhile back. I believe this code assumes that there are no empty cells in your block of data. You may wish to use “UsedRange” if you are going to modify this for your use. Also, my numbers I use on my 3 constants might need tweaked a little. I have yet to determine the exact point (to the decimal) where Excel pushes you on to a new sheet

    ‘ Sum of Used Columns’ ColumnWidths (Max width for each category)
    Private Const m_sngcReportPageWidth As Single = 80
    Private Const m_sngcReportLandscapePageWidth As Single = 127.87
    Private Const m_sngcReportLandscapeLegalPageWidth As Single = 150

    ‘ Determine the last cell in column A
    lngLastUsedRow = LastUsedRowInColumnA(m_wbkReport.ActiveSheet.Name)

    ‘ Determine the last cell in row 1
    intLastUsedCol = LastUsedColInRowOne(m_wbkReport.ActiveSheet.Name)

    With m_wbkReport.ActiveSheet
    ‘ Determine total columns
    intTotalCols = .UsedRange.Columns.Count

    ‘ Determine page width
    sngPageWidth = 0
    For intColumnCounter = 1 To intTotalCols
    sngPageWidth = sngPageWidth + .Columns(intColumnCounter).ColumnWidth
    Next intColumnCounter

    ‘ Make the report fit the entire page width
    If sngPageWidth >= m_sngcReportLandscapeLegalPageWidth Then
    ‘ Make the report landscape and legal
    .PageSetup.Orientation = xlLandscape
    .PageSetup.PaperSize = xlPaperLegal

    ‘ Determine the number of pages tall
    lngPagesTall = CLng(lngLastUsedRow / 50)
    If lngPagesTall = m_sngcReportLandscapePageWidth Then
    ‘ Make the report landscape and legal
    .PageSetup.Orientation = xlLandscape
    .PageSetup.PaperSize = xlPaperLegal
    .PageSetup.Zoom = 100
    ElseIf sngPageWidth >= m_sngcReportPageWidth Then
    ‘ Make the report landscape
    .PageSetup.Orientation = xlLandscape
    .PageSetup.PaperSize = xlPaperLetter
    .PageSetup.Zoom = 100
    End If
    End With

  4. would you mind adding the code for LastUsedRowInColumnA() and LastUsedColInRowOne()

    Also you are missing and END IF before the last END WITH

    FWIW

    alex

  5. I’m on an Oracle training course this week..
    No Excel on the computer, so I’m air-coding.

    lngRow = Cells(Rows.Count, 1).End(xlUp).Row
    lngCol = Cells(1, Columns.Count).End(xlToLeft).Column

  6. I am using Excel 2003 SP2

    I need to print 18 columns, print to fit 1 page wide by ** long. 3 of the columns contain large amounts of text. Whichever way I format it, the columns with large amounts of text do not expand in height to fill all the text when printing and cut off some of the text.
    When viewing the sheet on the screen it the height is fully expanded to the height required to show all the text.

    I hear this is a bug in Excel. Is this true?

  7. A program that I use on a daily basis is Fineprint.
    Fineprint has been around for several year but is still remarkably unknown program. Actually I think this how they survive (no competition)
    However, when companies do find this program they buy it by the 1000’s.
    http:\fineprint.com

  8. this is a great way to reduce paper consumption and it’s always easier to read.

    I’ve got a problem though … we got a really long list of people and their phone number we would like to print out on landscape instead of portrait… instead of having 1 long list we would have the list cut in half on the page… Is there a way to do this automatically without copy paste or VBA?

  9. I have the page set-up to Landscape and fit to 1 page wide by __ tall as specified above.
    The print preview shows exactly what I want.
    When I print several columns on the right are missing.
    Is it maybe the case that if the printer can’t get it any smaller it’ll stop at its own reduction level and tuff?

  10. I have gone in and selected the rows I would like repeated at the top of the page, however I do not want this repeated or printed out on the last page. How do I get this to not print or show on my last page without removing it from all the rest?

  11. I have adjusted my columns to fit the longest line of text (by double-clicking the crosshair at the top of each column) and it looks fine on the screen, but in “print preview” and on the actual printed page, the columns have not been adjusted to the text…in other words there’s a lot of white space from the end of the line of text to the beginning of the next column. What am I doing wrong?

  12. Hi there. I use excel sometimes to create a one page table. For example, maybe I want to create a sign in sheet for an event. So I use the border function to get my lines. Then to make it fit the page width, I will manually change the width until I get it about right. Right means the widths are consistent and the table takes up the whole page from left to right. I do this from top to bottom as well. I’m wondering if there is an automatic way to “autofit” the width of columns and height of rows to take up the whole page. Otherwise my “sign in” sheet would look odd and wouldn’t really make full use of the paper. Hope that’s clear and thanks for any help.

  13. i love you too! i have spent an hour trying to get all my columns on one page til i found this. now my problem is its too small to read. but it’s gotta all be on one page going across or else it won’t make sense. grrrr….

  14. WOW! This has been driving me crazy. You are saint for solving this problem! Now why can’t Microsoft clean up after itself when you use it?

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *