• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Factor 5 Dynamic Print Area

You are here: Home / Excel Formulas / Excel Factor 5 Dynamic Print Area
Excel Factor 5 Dynamic Print Area
July 13, 2012 by Mynda Treacy
The following Excel Factor entry was sent in by Daniel Ferry, founder of Excel enthusiast blog, Excel Hero.

Often an Excel model needs a dynamic print area, as data that we wish to print may change over time.

Watch how the print area for the table below (depicted by the dashed line around the data), dynamically updates as new rows are added to the bottom.

Excel Dynamic Print Area

You can do this using a formula that produces a dynamic range and the result is the dynamic print area you see above.

How to Create a Dynamic Print Area

First create a dynamic named range using the OFFSET function and COUNTA (or any other combination of functions that creates a dynamic range).

For this example we’ll assume our table will not grow bigger than row 200 and column Z, therefore our formula will be:

=OFFSET($A$1,0,0,COUNTA($A$1:$A$200),COUNTA($A$1:$Z$1))

For more on how the OFFSET formula works.

To create a named range open the Name Manager on the Formulas tab of the ribbon > click New.

The following dialog box will open and you can give your range a name (e.g. Print_Area_Formula), then enter your dynamic range formula into the ‘Refers to:’ field (in this example we’re using the OFFSET formula above).

Excel Dynamic Print Area

Note: the OFFSET formula example above will only work if there are no blank columns or rows in the data range.

Next create a normal print area by selecting a range on your worksheet > go to the Page Layout tab of the Ribbon > click on Print Area > Set Print Area.

This will automatically create a named range in the Name Manager called Print_Area.

Excel Dynamic Print Area

Now simply go into the Name Manager and edit the Print_Area name so that its ‘Refers To’ field houses the dynamic range formula that we created earlier.

To do this open the Name Manager > click on the name ‘Print_Area’ >  type the name of your print area dynamic formula in the Refers to: field at the bottom:

Excel Dynamic Print Area

Now sit back and relax as your print area dynamically updates when new data is added to your table!

Easier Alternative

While the Named Range solution works, Microsoft MVP, Beth Melton shared this alternative with Daniel:

Simply format your data in an Excel Table > select the entire table and set your print area the normal way (from the Page Layout tab). And that’s it.

As the table changes shape (grows or shrinks, both vertically and horizontally) the print area will adjust dynamically!

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook.

Thanks for sharing this cool tip, Daniel.

Besides being a Microsoft MVP for Excel, Daniel Ferry runs the Excel enthusiast blog, Excel Hero and the Excel Hero LinkedIn Group.  Daniel is currently authoring a book for Microsoft Press on Excel 2013 due to ship next year.

Daniel truly is a veteran of all versions of Excel, since the beginning of the product 26 years ago.  If you would like to learn from him you can. He shares his expertise at the widely praised and truly remarkable Excel Hero Academy* .

*Disclosure - if you join Daniel's Excel Hero Academy through the link above we receive a small commission.

Vote for Daniel

If you’d like to vote for Daniel’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂

Excel Factor 5 Dynamic Print Area

More Excel_Factor Posts

Excel Factor Voting Roundup

Excel Factor Voting Roundup

Excel Factor 21 Hyperlink Triptych

Excel Factor 21 Hyperlink Triptych

Tips and tricks with hyperlinks to make them dynamically update as the selection in the sheet changes. Sample workbook available.
Excel Factor 20 Custom Number Format Disguise

Excel Factor 20 Custom Number Format Disguise

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 19 Dynamic Dependent Data Validation

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

Excel Factor 18 Dynamic Hyperlinks and the Magic Hash

The Excel HYPERLINK function creates links to places inside or outside of your Excel file. I'll show you a shortcut for creating them that few people know.
Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 17 Lookup and Return Multiple Matches

Excel Factor 16 Dynamic Lookup

Excel Factor 16 Dynamic Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 15 The Lazy Lookup

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 14 Interleave Data from Two Columns into One

Excel Factor 13 Handy Tips & Tricks

Excel Factor 13 Handy Tips & Tricks

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel FormulasTag: Excel_Factor
Previous Post:Excel Factor Entry 4 INDEX and MATCH Two CriteriaExcel Factor Entry 4 INDEX and MATCH Two Criteria
Next Post:Excel Factor 6 Auto Refresh PivotTablesExcel Factor 6 Auto Refresh PivotTables

Reader Interactions

Comments

  1. jim

    March 26, 2021 at 10:50 pm

    Hi Mynda,

    I tried creating a dynamic print area by editing Print_Area name in Name Manager
    It works (I can see the outlined area changing) but this resets to a fixed range as soon as I Print Preview, use Page Setup or even revisit Name Manager

    also, it seems, if I save and reopen the spreadsheet

    not very functional, Microsoft

    jim

    Reply
    • Mynda Treacy

      March 28, 2021 at 10:55 am

      Hi Jim,

      I can’t reproduce this bug. If you download the example file for this tutorial can you reproduce the problem in that file too? I suspect there is something wrong with your file specifically or the formula in your file.

      Mynda

      Reply
      • jim

        March 29, 2021 at 9:46 pm

        No worries Mynda, fixed with some (very simple) VB – although that always feels like cheating, code was needed anyway to facilitate creating a pdf output (which is what the print area was needed for anyway)
        I’ve seen this problem alluded to elsewhere with similar replies of non-reproducibility (a new test file also does this, not just my working file – maybe it’s something in my set up)

        jim

        Reply
        • Mynda Treacy

          March 30, 2021 at 8:44 am

          Ah, glad you found a workaround, Jim.

          Reply
  2. David

    November 30, 2020 at 10:00 am

    I have two sheets in a workbook, both with dynamic print areas. However, I need to be able to switch to the ‘active’ dynamic print area either based on a macro (ie, Print_Area now=’sheet 1 dynamic print area’, or ‘Print_Area=’sheet 2 dynamic print area’). I’ve tried setting the print area to a ‘choose’ formula and an ‘indirect’ formula, but neither works.
    Any tips would be welcome.

    Reply
    • Catalin Bombea

      December 1, 2020 at 3:32 pm

      Hi David,
      Both worksheets need a separate Print Area, you should not use a single area for multiple sheets.

      Reply
  3. Ian

    February 20, 2018 at 7:21 am

    Does this work in 2013?

    If I set the PrintArea namespace to =Print_Area_Formula, it will just then turns that formula into a static value based off of whatever is currently in the worksheet as soon as I leave the name manager(i.e. “=Sheet1!$A$1:$I$142”).

    Instead of dynamically changing based on my data, the PrintArea just calculates what the dynamic formula equals at the moment of entry, not dynamic with new data being inserted.

    Reply
    • Mynda Treacy

      February 20, 2018 at 10:30 am

      Hi Ian,

      What if you set the print range and then edit the Print_Area name in the name manager and put the dynamic named range formula in the ‘Refers to’ field. i.e. skip creating the Print_Area_Formula and just put the formula in the Print_Area name.

      Mynda

      Reply
  4. Paul de Barros

    September 9, 2014 at 4:06 am

    I really like using this system, but there is a problem. The “refers to” for the Print_Area named range shifts from the named range I defined to a static range every time I print, or even print preview. How do I stop that from happening? Is there some sort of “keep this dynamic” code? Any advice you can offer would be greatly appreciated.

    Reply
    • Mynda Treacy

      September 9, 2014 at 8:56 pm

      Hi Paul,

      That’s strange. I tested it on my PC and didn’t have any problems. Are you able to send me your file via the help desk so I can see what the problem might be?

      Mynda

      Reply
      • Paul de Barros

        September 9, 2014 at 9:17 pm

        Actually, I think I solved the problem right after I posted my question. Following advice from this website, I made the Print_Area range be dynamic instead of setting it to refer to a separate named range. So far, I haven’t had the problem with it converting to being static. Thanks for getting back to me, though.

        Reply
        • Mynda Treacy

          September 10, 2014 at 6:21 am

          Thanks, Paul. It seems you may not have followed the tutorial above correctly since that link explains how to do the same thing.

          Not to worry, the main thing is you have it working.

          Kind regards,

          Mynda

          Reply
          • Paul de Barros

            September 10, 2014 at 6:40 am

            Upon rereading the instructions, I see that you are right. I was thrown off by the image that has Print Area set equal to Print_Area_Formula. Thanks.

          • Mynda Treacy

            September 10, 2014 at 6:43 am

            Ah, thanks for clarifying. I wonder how I can make that clearer.

            Mynda

  5. SIMBARASHE SILENDENI

    August 5, 2013 at 11:11 pm

    Hi Treacy,

    I have a worksheet with a 5 year, monthly income statement projection covering column A-AM (Sept 2013 – Aug 2016). Column A-B contains the income statement headings, eg Turnover, cost of sales, etc upto profit. I have 3 more columns for totals; AN,AO & AP (ie Yr 1 total, Yr2 total and Y3 total). I want each year’s monthly income statement to be printed with its total on one page. I also want the headings to be repeated on each page on the print out.

    Please help.

    Regards

    Simba

    Reply
    • Mynda Treacy

      August 7, 2013 at 4:54 pm

      Hi Simbarashe,

      You can print non-contiguous areas i.e. your yearly totals, and set columns and rows to be repeated on each page.

      To set multiple print areas select the first range > Page Layout tab > Print Area > Set print area.

      To set the next print area select the next range > Page Layout tab > Print Area > Add to print area.

      To set rows/columns to repeat:

      Page Layout tab > Print Titles – enter the rows/columns in the relevant fields on the sheet tab of the page setup dialog box.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply
  6. Mynda Treacy

    July 13, 2012 at 12:34 pm

    Bryon emailed me with this cool tip in response to the ‘Easier Alternative’ method above:

    “There is no need to even set the print area on any table. Simply place your cursor anywhere in the table and then go to File –> Print. From there, click the dropdown directly under Settings and choose ‘Print Selected Table’.

    This is nice because if you have multiple data tables, you don’t have to clear and reset the print area for different tables; just click on the table in question and print it. The feature understands when a table grows and shinks.”

    Thanks for sharing, Bryon 🙂

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.