Dynamically Setting a Print Area

Travis has a cell that, through a series of formulas, contains the upper-left corner of a 5 column by 20 row area that needs to be printed. For instance, if the cell contains $F$15, then the print area would be F15:J34, but if it contained $D$45, then the print area would be D45:H64. Travis wonders if it is possible to dynamically set the print area based on the contents of the cell, without using a macro.

Yes, there is a way you can set a dynamic print area without using a macro. Let's assume for the purposes of this tip that the calculated cell address is in A1. In preparation, you need to set up a print area:

1. Select a random cell or range of cells; it doesn't really matter at this point.
2. Display the Page Layout tab of the ribbon.
3. Click the Print Area tool. Excel displays two options.
4. Choose the Set Print Area option.

When you set a print area in this manner, Excel creates a named range called Print_Area. Whatever range it refers to is what will be printed when you choose to print. Since it is a named range, you can modify it in the Name Manager. Follow these steps to make the print area dynamic:

1. Press Ctrl+F3. Excel displays the Name Manger.
2. If there is more than one named range, select the Print_Area name.
3. Click the Edit button. Excel displays the Edit Name dialog box. (See Figure 1.)



Figure 1. The Edit Name dialog box.

4. In the Refers To box, at the bottom of the dialog box, replace whatever is there with the following formula. (If the name of the worksheet is different from what is shown in this formula, make the necessary change to reflect reality.)

=OFFSET(INDIRECT(Sheet1!$A$1),,,20,5)

5. Click OK to close the Edit Name dialog box.
6. Click OK again to close the Name Manager.

That's it. Your print area is now dynamic, dependent on the address stored in cell A1.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13946) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.