Excel tutorials

How to Automatically Set the Print Area of Variable Range of Cells

If you use Excel in your everyday work and often print data on a printer, I believe you've been thinking about how to automatically set the Print Area that will only be applied to a certain cell range, so not the entire worksheet.
In the example below, notice that the data is in certain columns and you often add data to the rows below. Since you often add data to a table below your wish is to automatically expand the printing area and apply it to the added data. When you run the Print Preview you want to see only certain data.

Image 1

How to set a variable data output area in Excel

 

Automatically expand the printing area

The first automatic setup method to expand the print area, see the image below. So in this case, our Print Area is in the cell range of 'C1:H27'.

To set the auto-changing print area, you need to set Print_Area and the named formula in Name Manager.

Run Name Manager on Formulas Tab and create a new item in the Print Area dialog box.

  • Name: Print_Area
  • Scope: Sheet1
  • RefersTo: PrintFormula1

The next step is to name Formula1:

  • Name: PrintFormula1
  • Scope: Sheet1
  • RefersTo: =OFFSET(Sheet1!$C$1;0;0;COUNTA(Sheet1!$D$3:$D$100)+2;6)

See clarifying parts of the formula

  • $C$1 -> Excel cell where PrintArea starts
  • $D$3:$D$100 -> The cell range in which we check the filled cell
  • +2 -> Number of rows from beginning to first row of filled cells (D3-2=D1)
  • 6 -> Number of columns included in PrintArea start from C1

 

Image 2

Dynamic printing area in Excel

If you want a different Print Area then set another formula.

Run Name Manager on Formulas Tab and create a new item in the Print_Area dialog box.

  • Name: Print_Area
  • Scope: Sheet1
  • RefersTo: PrintFormula2

The next step is to name Formula2:

  • Name: PrintFormula2
  • Scope: Sheet1
  • RefersTo: =OFFSET(Sheet1!$D$3;0;0;COUNTA(Sheet1!$D$4:$D$100)+1;4)

See clarifying parts of the formula

  • $D$3 -> Excel cell where PrintArea starts
  • $D$4:$D$100 -> The cell range in which we check the filled cell
  • +1 -> Number of rows from beginning to first row of filled cells (D3-2=D1)
  • 4 -> Number of columns included in PrintArea start from C1

Image 3

Dynamic Print Area in Excel Worksheet

 

The result of the set Dynamic Print Area

And finally, after the set Print Area you can choose which area you want to print on the printer. In the image below, see what the print area (Print Preview) looks like to apply the named Formula2. Of course you do not have to use both of these formulas. This is just an example of how we can set a variable area or Dynamic Print Area in Excel.

result of dynamic print area

Summary, while working and adding data to 'D27:D100' cells, Excel automatically enlarges the printing area. Also attached to this tutorial, you have the Download ZIP file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.