The following Excel Factor entry was sent in by Daniel Ferry, founder of Excel enthusiast blog, Excel Hero.
Often an Excel model needs a dynamic print area, as data that we wish to print may change over time.
Watch how the print area for the table below (depicted by the dashed line around the data), dynamically updates as new rows are added to the bottom.
You can do this using a formula that produces a dynamic range and the result is the dynamic print area you see above.
How to Create a Dynamic Print Area
First create a dynamic named range using the OFFSET function and COUNTA (or any other combination of functions that creates a dynamic range).
For this example we’ll assume our table will not grow bigger than row 200 and column AA, therefore our formula will be:
=OFFSET($A$1,0,0,COUNTA($A$1:$A$200),COUNTA($A$1:$AA$200))
For more on how the OFFSET formula works.
To create a named range open the Name Manager on the Formulas tab of the ribbon > click New.
The following dialog box will open and you can give your range a name (e.g. Print_Area_Formula), then enter your dynamic range formula into the ‘Refers to:’ field (in this example we’re using the OFFSET formula above).
Note: the OFFSET formula example above will only work if there are no blank columns or rows in the data range.
Next create a normal print area by selecting a range on your worksheet > go to the Page Layout tab of the Ribbon > click on Print Area > Set Print Area.
This will automatically create a named range in the Name Manager called Print_Area.
Now simply go into the Name Manager and edit the Print_Area name so that its ‘Refers To’ field houses the dynamic range formula that we created earlier.
To do this open the Name Manager > click on the name ‘Print_Area’ > type the name of your print area dynamic formula in the Refers to: field at the bottom:
Now sit back and relax as your print area dynamically updates when new data is added to your table!
Easier Alternative
While the Named Range solution works, Microsoft MVP, Beth Melton shared this alternative with Daniel:
Simply format your data in an Excel Table > select the entire table and set your print area the normal way (from the Page Layout tab). And that’s it.
As the table changes shape (grows or shrinks, both vertically and horizontally) the print area will adjust dynamically!
Thanks for sharing this cool tip, Daniel.
Besides being a Microsoft MVP for Excel, Daniel Ferry runs the Excel enthusiast blog, Excel Hero and the Excel Hero LinkedIn Group. Daniel is currently authoring a book for Microsoft Press on Excel 2013 due to ship next year.
Daniel truly is a veteran of all versions of Excel, since the beginning of the product 26 years ago. If you would like to learn from him you can. He shares his expertise at the widely praised and truly remarkable Excel Hero Academy* .
*Disclosure - if you join Daniel's Excel Hero Academy through the link above we receive a small commission.
Vote for Daniel
If you’d like to vote for Daniel’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂
Paul de Barros says
I really like using this system, but there is a problem. The “refers to” for the Print_Area named range shifts from the named range I defined to a static range every time I print, or even print preview. How do I stop that from happening? Is there some sort of “keep this dynamic” code? Any advice you can offer would be greatly appreciated.
Mynda Treacy says
Hi Paul,
That’s strange. I tested it on my PC and didn’t have any problems. Are you able to send me your file via the help desk so I can see what the problem might be?
Mynda
Paul de Barros says
Actually, I think I solved the problem right after I posted my question. Following advice from this website, I made the Print_Area range be dynamic instead of setting it to refer to a separate named range. So far, I haven’t had the problem with it converting to being static. Thanks for getting back to me, though.
Mynda Treacy says
Thanks, Paul. It seems you may not have followed the tutorial above correctly since that link explains how to do the same thing.
Not to worry, the main thing is you have it working.
Kind regards,
Mynda
Paul de Barros says
Upon rereading the instructions, I see that you are right. I was thrown off by the image that has Print Area set equal to Print_Area_Formula. Thanks.
Mynda Treacy says
Ah, thanks for clarifying. I wonder how I can make that clearer.
Mynda
SIMBARASHE SILENDENI says
Hi Treacy,
I have a worksheet with a 5 year, monthly income statement projection covering column A-AM (Sept 2013 – Aug 2016). Column A-B contains the income statement headings, eg Turnover, cost of sales, etc upto profit. I have 3 more columns for totals; AN,AO & AP (ie Yr 1 total, Yr2 total and Y3 total). I want each year’s monthly income statement to be printed with its total on one page. I also want the headings to be repeated on each page on the print out.
Please help.
Regards
Simba
Mynda Treacy says
Hi Simbarashe,
You can print non-contiguous areas i.e. your yearly totals, and set columns and rows to be repeated on each page.
To set multiple print areas select the first range > Page Layout tab > Print Area > Set print area.
To set the next print area select the next range > Page Layout tab > Print Area > Add to print area.
To set rows/columns to repeat:
Page Layout tab > Print Titles – enter the rows/columns in the relevant fields on the sheet tab of the page setup dialog box.
I hope that helps.
Kind regards,
Mynda.
Mynda Treacy says
Bryon emailed me with this cool tip in response to the ‘Easier Alternative’ method above:
“There is no need to even set the print area on any table. Simply place your cursor anywhere in the table and then go to File –> Print. From there, click the dropdown directly under Settings and choose ‘Print Selected Table’.
This is nice because if you have multiple data tables, you don’t have to clear and reset the print area for different tables; just click on the table in question and print it. The feature understands when a table grows and shinks.”
Thanks for sharing, Bryon 🙂