How do I create a dynamic print range in Excel?

This Excel tutorial explains how to set dynamic print area.

Table of Contents Show

Create Excel dynamic Print Area – without blank data
Create Excel dynamic Print Area – with blank data
Create Excel dynamic Print Area – with unequal row
Outbound References
How to create a dynamic defined range in an Excel worksheet
In this article
How to use the OFFSET formula with a defined name
Additional resources
Additional resources
In this article
How do you set a dynamic print area in Excel?
How do I create a dynamic table range in Excel?

You may also want to read:

Excel dynamic Data Validation list

Excel dynamic data range

Excel graph dynamic data range

Print Area is a function to define the worksheet Range you want to print.

To set a Print Area, select a Range, say A1:B6, then click on Set Print Area

Click on FORMULA tab > Name Manager

You will find Range A1:B6 is named as Print_Area, which is the reserved name for setting Print Area.

Unfortunately, the Print Area Range has been specified as A1:B6, it will not change dynamically if we input data in row 7.

Create Excel dynamic Print Area – without blank data

Assume that column A does not contain blank row. Replace the formula in Refers to with

=OFFSET(compensation!$A$1,0,0,COUNTA(compensation!$A:$A),2)

If you don’t understand the OFFSET function, click here to view my previous post.

But simply speaking, the syntax of OFFSET function is as below

OFFSET( reference, rows, columns, [height], [width])

Create Excel dynamic Print Area – with blank data

With blank data in column A, we cannot use COUNTA anymore, because COUNTA is to determine how many non-empty cells in column A.

If column A contains blank data AND the last data is a Text, then use the below formula

=OFFSET(compensation!$A$1,0,0,MATCH(REPT("z",255),$A:$A),2)

If column A contains blank data AND the last data is a Number, use the below formula

=OFFSET(compensation!$A$1,0,0,MATCH(9.99999999999999E+307,compensation!$A:$A),2)

If you are unsure what data type is in the last cell, use

=OFFSET(compensation!$A$1,0,0,SUMPRODUCT(MAX((compensation!$A:$A<>"")*ROW(compensation!$A:$A))),2)

Create Excel dynamic Print Area – with unequal row

Suppose you have the data below. Currently you have data from January to October but it will grow to December in the future, you want to set Excel dynamic Print Area for the growing row.

Replace the formula in Refers to with

=OFFSET(Sheet4!$B$1,0,0,COUNTA(Sheet4!$B:$B),-2)

What the formula means it that, I refer to Range B1 as a starting point of OFFSET, and the height is expanded to the last row of column B, width is expanded one column to the left (-2). I use COUNTA because I assume all cells in Value should be non-empty.

If some Cells between January and the last month are empty, type

=OFFSET(Sheet4!$B$1,0,0,SUMPRODUCT(MAX(($B:$B<>"")*ROW($B:$B))),-2)

Now whenever you type something in the Value, you see the Print Area (the border) is instantly resized.

Outbound References

https://support.microsoft.com/en-us/kb/830287

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

How to create a dynamic defined range in an Excel worksheet

Article
05/05/2022
2 minutes to read
Applies to:Excel 2013, Excel 2010, Excel 2007, Excel 2003

Summary

In Microsoft Excel, you may have a named range that must be extended to include new information. This article describes a method to create a dynamic defined name.

Note

The method in this article assumes that there are no more than 200 rows of data. You can revise the defined names so that they use the appropriate number and reflect the maximum number of rows.

How to use the OFFSET formula with a defined name

To do this, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Office Excel 2007, Microsoft Excel 2010 and Microsoft Excel 2013

In a new worksheet, enter the following data.

Number A B
1 Month Sales
2 Jan 10
3 Feb 20
4 Mar 30
Click the Formulas tab.
In the Defined Names group, click Name Manager.
Click New.
In the Name box, type Date.
In the Refers to box, type the following text, and then click OK:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)
Click New.
In the Name box, type Sales.
In the Refers to box, type the following text, and then click OK:
=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)
Click Close.
Clear cell B2, and then type the following formula:
=RAND()*0+10
Note
In this formula, COUNT is used for a column of numbers. COUNTA is used for a column of text values.
This formula uses the volatile RAND function. This formula automatically updates the OFFSET formula that is used in the defined name "Sales" when you enter new data in column B. The value 10 is used in this formula because 10 is the original value of cell B2.

Microsoft Office Excel 2003

In a new worksheet, enter the following data:

Number A B
1 Month Sales
2 Jan 10
3 Feb 20
4 Mar 30
On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, type Date.
In the Refers to box, type the following text, and then click OK:
=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1).
Click Add.
In the Names in workbook box, type Sales.
In the Refers to box, type the following text, and then click Add:
=OFFSET($B$2,0,0,COUNT($B$2:$B$200),1)
Click OK.
Clear cell B2, and then type the following formula:
=RAND()*0+10
Note
In this formula, COUNT is used for a column of numbers. COUNTA is used for a column of text values.
This formula uses the volatile RAND function. This formula automatically updates the OFFSET formula that is used in the defined name "Sales" when you enter new data in column B. The value 10 is used in this formula because 10 is the original value of cell B2.

Additional resources

How do you set a dynamic print area in Excel?

More informative way to define print area in Excel.

On the Page Layout tab, in the Page Setup group, click the dialog launcher. . ... .

On the Sheet tab, put the cursor in the Print area field, and select one or more ranges in your worksheet. To select multiple ranges, please remember to hold the Ctrl key..

Click OK..

How do I create a dynamic table range in Excel?

The Pivot Table option can create dynamic Tables in Excel. For this, select the complete data to be included in Dynamic Table and then click on the Pivot Table option under the Insert menu tab or else press short cut key ALT + N + V simultaneously to apply it.