How to create a dynamic print area in Excel?
Normally, the print area is constant after setting in your worksheet. In some cases, you would like the print area adjusting with the print content you deleted or added any time. How to achieve it? In this article, we are going to show you a method of creating a dynamic print area in Excel.
As below screenshot shown, assuming your normal print area is A1:E5, but the range data may grow until row 10 and column G. You can create a dynamic print area with the following steps.
1. You need to create a dynamic named range at first. Click Formulas > Name Manager. See screenshot:
2. In the Name Manager dialog box, click the New button to open the New Name dialog box. And in the New Name dialog box, enter Print_Area_Formula into the Name box, and enter formula =OFFSET($A$1,0,0,COUNTA($A$1:$A$10),COUNTA($A$1:$G$1)) into the Refers to box, and finally click the OK button. See screenshot:
Note: in the formula, $A$10 and $G$1 mean the print area will not grow bigger than row 10 and column G. You can change them as you need.
3. When it returns to the Name Manager dialog box, please close it.
4. Select the range with data you will set as print area (in this case, we select A1:E5), then click Page Layout > Print Area > Set Print Area. See screenshot:
5. Click Formulas > Name Manager to open the Name Manager dialog box.
6. In the Name Manager dialog box, select the Print_Area in the Name box, then replace the original formula with = Print_Area_Formula (the name of the dynamic range you created above) in the Refers to box, and then click the button to save the change. Finally close the Name Manager dialog box.
Now the dynamic print area is created. You can see the print area is adjusting with the data you added or deleted until it reaches the specified row and column. See screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agothis is definitely super usefull. However, each time I close and reopen the file, the Print_Area name configuration is gone. So each time I have to go to Define Names, and say Print_Area = Print_Area_Formula.Someone else with the same issue?? Thx!
- To post as a guest, your comment is unpublished.· 1 years agoHi, just one thing, is your formula dynamic?. Would this be better =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))?
Then when the pivot changes or cells or columns are added the formula does not need to change.
- To post as a guest, your comment is unpublished.· 2 years agoFirst of all, thanks a lot!
Secondly: you don't need to define two "names" in the Mame Manager. As a simpler alternative, just select some random cells and set the print area (on those random cells), then open the Name Manager: it will have the print_area already in: here you can replace the content with the formula you want.
- To post as a guest, your comment is unpublished.· 3 years agohi
thank you, it is great.
is there any way to have a conditional reference for the offset? I mean use the offset from the first cell in column A that contains a letter A, or equal to number 3 or filled by the color yellow.
I tried the following formula but it doesn't work.
- To post as a guest, your comment is unpublished.· 4 years agoHello,Its useful &exciting.