Print area is simply the area which will be printed if you give a command either using Ctrl+P or going to File menu and Print.
Excel gives you the flexibility to set the print area. We have discussed this feature of Excel way back in time in this article: Excel Tutorial – Ctrl P is Power in Control (Printing). It has been one of the most read articles in our Excel repository. I totally recommend each and every reader to go through it as we learnt quite a bucket load of things we can do in Excel’s print options.
However, we are on a different mission. Have a look at this Invoice Template V1.0 . Among other key benefits, one of them is dynamic print area or simply put print area changes as the data size changes. So, instead of having a fixed print area, if your invoice is big, print area will expand to incorporate all of the items, and if its small the print area will contract automatically. And today we will learn how to do it.
The idea behind dynamic print area is actually having dynamic named ranges.
Not many know that when you specify a print area in Excel, its actually a named range. And if it is a named range you can use all sorts of formula in it and thus it makes expanding/contracting print area.
Consider the following situation:
Now you might be thinking that you can select the print area and get rid of that heading above. But its a fix that needs to be fixed every time the invoice is updated. For example if we set print area by selecting cell range: =B4:F24 then it will stay fixed and if we insert more items the bottom will be cut out.
So need a way to make the print area dynamic so that it not only get rid of the top heading but also expands/contracts with the data.
So here is the trick:
- Data is starting from Row 4
- Until row 14 you have static data i.e. this is not going to expand or contract
- From row 15 onwards you have the data that is dynamic and tends to change.
So from row 4 to 14 it makes 11 rows (including row 4) that are static and the rest are variable. So the print area is 11 rows + variable rows. As you can see that the part that varies is actually residing in column F. So if we can count the items in column F every time, we can work out the print area.
This is where our OFFSET function is going to help us.
First of all set the print area as normal. Just select whatever data you have and go to page layout tab > page setup group > print area drop down > set print area.
Now you have specified a print area. Go to Formula tab > defined names group > click names manager. This will open names manager box. Find the name “Print_area”. Left click on it and you can see the area to which it refers.
Now you have to make the adjustments as shown in the animation below:
The formula I entered is as follows:
=OFFSET(Invoice!$B$4,0,0,11+Invoice!$E$3,5)
OFFSET function has the following syntax:
OFFSET(reference, rows, cols, [height], [width])
And what it does? It helps you OFFSET i.e. jump! Let me explain how. You mention the reference, its like a starting point. Rows argument tells excel how many rows it should jump/skip and cols argument which is columns tells how many columns to jump or skip. Height argument tells height of data to be fetched and width tells the width of the data to be fetched.
Now lets look at our formula again:
=OFFSET(Invoice!$B$4,0,0,11+Invoice!$E$3,5)
We told Excel that on invoice worksheet to start from cell B4, skip no rows or columns and fetch the data that is 11 + (the number mentioned in cell E3) rows deep and 5 columns wide.
Now in cell E3 I have this formula:
=COUNT(F:F)
This formula simply calculates the cells that contain numbers. In my situation, the answer of this formula is 10. So basically Excel will fetch 11 + 10 = 21 rows starting and including cell B4.
Now even if you add or delete rows, the COUNT function will recalculate the number of cells in column F that has numbers therefore it will change the sum of rows in the OFFSET formula and thus making the print area dynamic.
Later in the template I hid the contents of cell E3 by turning the font white or you can do it using custom number formatting. Even if you don’t hide it it is outside print area and so won’t bother.
Don’t forget to checkout following tutorials as well:
Excel Tutorial – Ctrl P is Power in Control (Printing)
Create Data Validation lists from Unsorted data with duplicates in Excel [How To]
HI
Thanks for the solution !
Unfortunately for me the formula works only once when I use It. System always returning to the default print area. I have to retype to ma make work and so on.
Did I miss something?
=OFFSET(Sheet1!$A$1,0,0,13+Sheet1!$B$21;16)
BR
MRJ
hi! Great stuff, but unfortunately it’s not working for me. Could you perhaps elaborate what I’m doing wrong?
I’m testing this in my own sheet, using a set print area from A1 to D5. I have set the number of additional rows to count (another 5) in cell F1
So if I’m following this correctly the formula should be: =OFFSET(Sheet1!$A$1,0,0,4+Sheet1!$F$1,4).
Excel keeps giving me error messages about the first part of the formula, but I haven’t got a clue as to what I did wrong. Would you be able to point out my error?
Great post, i will follow you in future.
Hasaan,
How does one make a dynamic print range that counts only those cells where a formula returns a value other than null? That is, the cells that have the formula but a null result are not printed, but the range above the blank cells (those where the formula returns nothing) where the formulas have produced results: text or numbers, are printed. That is how do you select only those cells with readable values, but not the cells with only formulas that don’t return values?
This is useful for Single page or Limited pages…
But in case multi pages,,where range of pages also changes ,what should be done ???
because when select the print area for many pages,,…there will be blank pages will be printed
hi
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.
offset(indirect(CELL(“address”,INDEX(A:A,MATCH(“A”,A:A,0)))),0,1,COUNTA(Sheet1!$b+Sheet1!$b:$b),7)
Wow, a very useful technique.
I have one concern to put forward. I tried the formula in in my work sheet. When I applied rows to be repeat at the top the formula is not working.
Can you please check ?
The solution works well, until you change the Page Layout -> Orientation from Portrait to Landscape or back. This replaces the OFFSET formula that the Print_Area refers to with the actual cell range that is referenced. Now that the formula is gone, the Print_Area is no longer dynamic.
Do you have any ideas of how to overcome this?
Thank you.
Hmmmmm interesting. If your data is in one table then probably selecting that table in print options will help and keep it dynamic.
Would it be possible to please elaborate on this as I am facing the same issue?
The change in page orientation overwrites the named print range.
This was most helpful. I applied this to an invoice generator by having one column on the left which either has 1 in all cells in each page with data and pages without data are blank and are not printed or included in a pdf. thank you for your detailed explanation!! Cheers!!!
=OFFSET(‘Proposal Tool’!$AI$2,0,0,0+’Proposal Tool’!$CZ$165,69)
My print area starts at AI2, is 69 cells wide, and is varied based on the product item count, which is indicated in 3 “IF” formulas in CZ169.
To avoid indicating which rows have data, as some rows are blank for page formatting/spacing, I did the following:
In column AI, I entered 1 in each cell in AI for page 1. For page 2, each cell in AI has an IF formula that if the total # of items is greater than 11 [11 items per page] then result is also 1. This is repeated for page 3.
Very useful workaround if you are avoiding using macros.
Hey!
So happy it worked for you and you adopted it to your requirements! Keep Excelling!