Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

Creating a dynamic print area in Excel


arrow blue right bubble 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:



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mike · 11 months ago
    hi
    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.

    offset(indirect(CELL("address",INDEX(A:A,MATCH("A",A:A,0)))),0,1,COUNTA(Sheet1!$b+Sheet1!$b:$b),7)

    thanks

    Mike
  • To post as a guest, your comment is unpublished.
    mahmoud · 2 years ago
    Hello,Its useful &exciting.