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:
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!
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!
Enter your email address below to download the sample workbook.
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 🙂