
Left click on it and you can see the area to which it refers. Go to Formula tab > defined names group > click names manager. Just select whatever data you have and go to page layout tab > page setup group > print area drop down > set print area. This is where our OFFSET function is going to help us.įirst of all set the print area as normal. So if we can count the items in column F every time, we can work out the print area. As you can see that the part that varies is actually residing in column F.

So the print area is 11 rows + variable rows. So from row 4 to 14 it makes 11 rows (including row 4) that are static and the rest are variable. From row 15 onwards you have the data that is dynamic and tends to change.Until row 14 you have static data i.e.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. 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. But its a fix that needs to be fixed every time the invoice is updated. Now you might be thinking that you can select the print area and get rid of that heading above. Now when I print it the problem is that ugly heading gets in the way. And if it is a named range you can use all sorts of formula in it and thus it makes expanding/contracting print area.Īs you can see it has big ugly blue heading “Easy Invoice Template” and after that the real deal starts. Not many know that when you specify a print area in Excel, its actually a named range. The idea behind dynamic print area is actually having dynamic named ranges. 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. Among other key benefits, one of them is dynamic print area or simply put print area changes as the data size changes. Have a look at this Invoice Template V1.0 . 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. It has been one of the most read articles in our Excel repository. We have discussed this feature of Excel way back in time in this article: Excel Tutorial – Ctrl P is Power in Control (Printing). 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.Įxcel gives you the flexibility to set the print area.
