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:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!