Often you may have a data range that you add or remove rows, and you need to keep redefining the print area manually. In this tutorial we are going to discuss a process using the OFFSET function to automatically adjust your print area as you add or remove rows of data.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a data range that has a header plus 50 rows for data:
If I choose to Print this page, even though I only want to print the 17 rows with data, you can see that Excel wants to print all 51 rows:
If I highlight the range I want to print, go to the Page Layout tab and choose Set Print Area, Excel will then define the print range that I want:
And notice that Excel has defined that range in the Name Manger:
But if I add or remove rows of data, Excel will still print $A$1:$I$17.
How do we create a process that will automatically adjust the height of the range based on the number of rows that contain data? Easy! We’ll set up another item in the Name Manager and use the OFFSET function to dynamically define the height.
First we are going to create another item in the Name Manager, call it “DynPrint” and insert the following formula in the “Refers to” area:
The formula we used is:
Now we have two items in the Name Manger:
Next, let’s redefine the Print Area item to reference the DynPrint item we just entered:
Now, whenever we add or remove items from our data range, the height of the print area will automatically adjust based on the number of items within column “I”.
Let’s take a quick refresher on the OFFSET function. The OFFSET function “Returns the reference to a range that is a given number of rows and columns from a given reference”. The syntax is:
=OFFSET(reference, rows, cols, [height], [width])
So, in our example, the reference was cell A1, we went down zero rows and over zero columns, which kept cell A1 as our starting point. The last two arguments define the size of the range. The height is defined by the COUNTA function, and we used 9 to define the width, since that is the number of columns in our range. You can make that dynamic also by using a similar process for the width.
Now, whenever you change the number of items in your data range, Excel will dynamically adjust the height!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!