Monday, November 03, 2008

Dynamic Print Area in Excel

I was creating a new version of my amortization schedule spreadsheet today and wanted to set the print area so that it didn't print a bunch of blank pages. If a person chooses a 15-year loan with monthly payments, I wanted the print area to show only those payments - not the entire table.


The key was to use a dynamic print area - a print area that uses a formula to define the range of cells to print. To create a dynamic print area ...

1. Define some arbitrary print area on your worksheet (File > Print Area > Set Print Area). This will create a Named Range, called "Print_Area"
2. Now you need to edit the formula used to define the Named Range. Go to Insert > Name > Define and click on "Print_Area" in the list. In the "Refers to:" field, use the OFFSET() formula to define the range. For example:

=OFFSET($A$1,0,0,ROW($A$16)+$H$6+1,COLUMN($H$1))
where $H$6 is a reference to the number of rows in the amortization table.

10 comments :

Anonymous said...

Interesting - I plan on testing this but have 2 questions.
(1) How to do this in Excel 2007 ?
(2) Can there be more than one dynamic print areas in a worksheet ?

Thanks - Iziquiel

Anonymous said...

In Excel 2007 it's the same process. You can edit the named ranges by clicking on the Formula tab in the ribbon and finding the Name Manager button (it's roughly in the middle of the ribbon).

Each Print_Area range is local to the sheet it belongs to too.

Anonymous said...

thx a lot, a very useful tip for me.
Bahadir @istanbul

Anonymous said...

A slight modification can make this even easier. You can use the counta formula to count the # of rows down and # of columns across. If there are blanks then you will need to add x # of rows/columns to the formula below..

=OFFSET($A$1,0,0,counta($A:$A),counta($1:$1))

This will sent your dynamic range to start at A1 and go down however many rows are not blank and however many columns across are not blank.

Anonymous said...

I used the same formula but every time I preview the print area it is reset to the range of cells in the Offset answer, e.g. &A&9:&O&250, instead of the Offset formula.
How can I stop this?

Mark Duchesne said...

Yes, i have been having the same problem. I cannot work out why excel is replacing the print area formula with the actual value. I am using excel 2007 and cant find out any solutions to this as yet.

Unknown said...

Maybe i missed something, but according to this guide about excel print area, you just have to mark everything you want to print and then set print area. That guide is for excel 2010, though. Or is your point that you cant do it because the area is too big, so manually marking it would take longer?

RJ Roth said...

I'm also interested in whether an answer has been found for the automatic range value replacement. It seems like once Excel quantifies the number of rows to print, it fills in the dynamic range with hard values and does not update as new data is entered. Any help would be appreciated.

Jon Wittwer said...

@Markus ... Manually redefining the print area is certainly an option. This tip is for when you don't want to do that every time you print, and is applicable to only specific situations.

@Markus, RJ ... I'm not sure why Excel would be converting your formula to a range. I haven't been able to reproduce that problem.

Unknown said...

I found that Excel converts the formula to a range when the workbook is in Page Layout view. However, if you change the worksheet to Normal view and create the range formula again, then Excel behaves.