Note: The other languages of the website are Google-translated. Back to English
English English

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:

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (6)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello,Its useful &exciting.
This comment was minimized by the moderator on the site
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.



This comment was minimized by the moderator on the site
First 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.
This comment was minimized by the moderator on the site
Hi, 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.
This comment was minimized by the moderator on the site
this 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!
This comment was minimized by the moderator on the site
Ca ne fonctionne pas. A chaque fois qu'on faire le gestionnaire de noms et qu'on rentre dedans, il a remplacé la formule par la zone area de départ
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations