Daily Dose of Excel

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.

Some time ago I was asked whether it was possible to add a Page .. of .. pages in a cell on a worksheet, without use of VBA.

Interesting challenge of course.
But with some defined names this proved possible.

I will show just one situation, where the pages are all below each other on the worksheet.

These names need to be defined:

RowAfterpgbrk
=GET.DOCUMENT(64)

TotPageCount
=GET.DOCUMENT(50)

PageOfPages
=”Page “&IF(ISNA(MATCH(ROW(),RowAfterpgbrk,1)),1,MATCH(ROW(),RowAfterpgbrk,1)+1)&” of ” & totpagecount + 0*now()

ThisPage
=IF(ISNA(MATCH(ROW(),RowAfterpgbrk,1)),1,MATCH(ROW(),RowAfterpgbrk,1)+1)

So to get the Page 1 of 3 in a cell, simply use this formula:

=PageOfPages

and to get the current page:

=ThisPage

A warning is needed here:

Do not copy cells that contain any reference to these defined names in their formula to another worksheet, Excel versions prior to 2003 will crash. To copy a formula from such a cell, select the formula in the formula bar and then hit control-c. Now you can safely paste the formula on another sheet.

I have uploaded a demo file to my site

Final helpful hint: If you use defined names a lot, consider downloading the
Name manager (by Charles Williams, Matthew Henson and myself) from:

www.jkp-ads.com

or

www.oaltd.co.uk/mvp

or from:

www.decisionmodels.com/downloads.htm

There was an interesting discussion at the Xtreme Visual Basic forums that I learned about from Andy (Thanks). It seems that that with Excel XP and service pack 3 (SP-3), controls placed on a worksheet will move when printed or previewed. I’ve heard of this problem, but never experienced it myself; probably because I use Excel 2000 unless I have a compelling reason to use a different version.

The solution, believe it or not, is to group all the shapes on the sheet, then ungroup them. How that fixes it, I have no idea. If you have this problem or are just interested in it, be sure to read the thread:

Sheet Controls Moving After Print

Kudos to Helen for finding a solution.

When your Excel table spans more than one printed page, you can repeat the rows or columns (that serve as headings) on the subsequent printed pages. On this table

I’ve added some manual page breaks to simulate a large table. To show the months across the top of every page and the regions across the left of every page, use the Sheet tab under File > Page Setup.

Page 4 of this print out looks like this without any rows or columns repeating.

And the same page looks like this after the changes to File > Page Setup > Sheet

The ampersand is a special character in Excel’s headers and footers. For instance, if you enter the date or the time, Excel puts an ampersand before the entry.

To show a literal ampersand in the header or footer, use two ampersands together.

This results in a header that looks like this:

If there are certain cells on your worksheet that you don’t want to print, you can hide the rows or columns they’re in before you print. Sometimes, however, there is other information in those rows and columns that you DO want to print.

There is no facility to hide individual cells, but you can change the color of the text to match the background of the cell. I’ll show you how I do it, but it’s not the only way (or the best way necessarily).

First, I create a style (Format>Style) called PrintWhite. If the background of your cells isn’t white, you’ll have to adjust this example to suit your particulars. I base the style on the Normal style, that is, I select a cell with the Normal style before creating PrintWhite. My style box looks like this:

As you can see, I uncheck all the boxes except font. I didn’t need to do that for this example, but since I know that the only attribute I will be changing is the font, I did it anyway.

Next, change the style for each cell that you want to hide while printing. In this example, I changed the style of all the even numbers to PrintWhite and left the odd numbers Normal.

Finally, you’ll need some code in your WorkBook_BeforePrint event. (Alt-F11 to open the VBE, double click on ThisWorkbook under your project in the Project Explorer, and select Workbook and BeforePrint from the dropdown boxes).

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Application.EnableEvents = False
    Cancel = True
    Me.Styles(”PrintWhite”).Font.Color = vbWhite
    ActiveWindow.SelectedSheets.PrintOut
    Me.Styles(”PrintWhite”).Font.ColorIndex = xlColorIndexAutomatic
    Application.EnableEvents = True
End Sub

The above event procedure changes the font color of the style to white, prints, then changes the color back to automatic.